Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Unanswered: Set "Save As" file path

    All,
    It is driving me nuts! I have the following code -

    Private Sub CommandButton2_Click()

    Const sLOG As String = "Shift Hand Off Notes - "
    Const sFILEFILTER As String = "Excel files (*.xls),*.xls"

    Dim sInitialFileName As String
    Dim v

    ActiveSheet.Unprotect
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    With Setup
    sInitialFileName = sLOG & Worksheets("Hand-off Notes").Range("K1").Value & " - " & Worksheets("Hand-off Notes").Range("F2").Value & " - " & FormatDateTime(Date, vbLongDate)
    End With
    v = Application.GetSaveAsFilename _
    (InitialFileName:=sInitialFileName, _
    filefilter:=sFILEFILTER)
    Me.Application.DefaultFilePath = "S:\share\dc\LP\LP Hand-off\DC 840"
    If v = False Then
    MsgBox "You have cancelled sending the Shift Hand Off Notes"
    Else
    ActiveWorkbook.SaveAs Filename:=sInitialFileName
    ThisWorkbook.SendMail Recipients:=("DC-BLLP@kohls.com, bob.carter@kohls.com")
    Subject = ("Shift Hand-off Notes For") & Format(Date, "dd/mm/yyyy")
    End If

    End Sub

    Everything is working except I cannot get it to point to the file path I have set up in the Me.Application.DefaultFilePath line.

    Any suggestions?

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Bob,

    I don't really understand the purpose of calling Application.GetSaveAsFilename() since you are going to override the filepath they choose? If it's to give them an opportunity to cancel the process then you could use a message box?

    Not tested, but I think you might be looking for something along the lines of...
    Code:
    Private Sub CommandButton2_Click()
    
        Const sLOG As String = "Shift Hand Off Notes - "
        Const sFILEFILTER As String = "Excel files (*.xls),*.xls"
        
        Dim sInitialFileName As String
    
        
        ActiveSheet.Unprotect
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        
        With setup
            sInitialFileName = sLOG & Worksheets("Hand-off Notes").Range("K1").Value & " - " & Worksheets("Hand-off Notes").Range("F2").Value & " - " & FormatDateTime(Date, vbLongDate)
        End With
        
        If MsgBox("Do you want to save and e-mail the Shift Hand Off Notes?", vbOKCancel, "Continue?") Then
            ActiveWorkbook.SaveAs Filename:="S:\share\dc\LP\LP Hand-off\DC 840\" & sInitialFileName
            ThisWorkbook.SendMail Recipients:=("xxx,xxx")
            MsgBox "Email sent"
        Else
            MsgBox "Process cancelled"
        End If
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •