Which Directory?

[Martin Harris]

Is it possible to report/record if a user has changed folder when offered the GetSaveAsFilename screen and then saves the file. (The saved file is then not in the expected place.)

 

[JoeMo]

Is it possible to report/record if a user has changed folder when offered the GetSaveAsFilename screen and then saves the file. (The saved file is then not in the expected place.)

Something like this will detect a change in directory for a previously saved workbook, but where to record it is another issue. If you record it in the workbook for which the change has been made, and the workbook is not in the expected place ... then you haven't accomplished much.

Sub ChangedDirectory()
mypath = ActiveWorkbook.Path
fname = Application.GetSaveAsFilename("myFile")
If Not InStr(fname, mypath) > 0 Then
    MsgBox "Changed Directory"
End If

End Sub

 

[Martin Harris]

thanks JoeMo - it is not the workbook that is being saved. I am generating a PDF document with a time stamped name. It is this document that the GetSaveAsFilename is for and I need to know if the folder offered is changed by the user before the save happens.

 

[JoeMo]

thanks JoeMo - it is not the workbook that is being saved. I am generating a PDF document with a time stamped name. It is this document that the GetSaveAsFilename is for and I need to know if the folder offered is changed by the user before the save happens.

If the folder the user selects with GetSaveAsFilename differs from the folder the workbook containing the macro is in, you can enter a note to that effect in the workbook (w/ date & time stamp), and exit the sub before the pdf save.

 

[Martin Harris]

If the folder the user selects with GetSaveAsFilename differs from the folder the workbook containing the macro is in, you can enter a note to that effect in the workbook (w/ date & time stamp), and exit the sub before the pdf save.

Sorry but not sure how to do that?

 

[JoeMo]

Sorry but not sure how to do that?

If you post the code you are using to save the pdf someone can help you.

 

[Martin Harris]

Joe Here is the code I am using. Thank you for this - most appreciated.

Sub CommandButton1_Click()
 Dim ws As Worksheet
 Dim strPath As String
 Dim myFile As Variant
 Dim strFile As String
 Dim NewDir As String
 Dim JobNumber As String
 On Error GoTo errHandler
 Dim wb As Workbook
 Dim ct As String
 strFile = ""
 Set ws = ActiveSheet
 Set wb = ThisWorkbook
 JobNumber = Range("C9").Text


 strFile = JobNumber & "-Register-" & (Now) & ".pdf"
 strFile = Replace(strFile, "/", "-")
 strFile = Replace(strFile, ":", "-")
 strFile = ThisWorkbook.Path & "\" & strFile


 
 myFile = Application.GetSaveAsFilename _
 (InitialFileName:=strFile, _
 FileFilter:="PDF Files (*.pdf), *.pdf", _
 Title:="Select Folder and FileName to save")

    If myFile <> "False" Then
    ws.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=myFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
            If MsgBox("PDF file has been created" & vbNewLine & "Do you wish to view?", _
            vbYesNo + vbDefaultButton1, "View The PDF?") = vbYes Then
                 ActiveWorkbook.FollowHyperlink ThisWorkbook.Path & "\" & strFile & ".pdf"
            End If
     End If

exitHandler:
 Exit Sub
errHandler:
 MsgBox "Could not create PDF file"
 Resume exitHandler
End Sub

 

[JoeMo]

Joe Here is the code I am using. Thank you for this - most appreciated.

Martin,
I'm a bit unsure about what exactly you want to happen if a folder other than the one the workbook containing the code is located in is chosen by the user on the GetOpenFilename command. If you want to force the user to save the file to a specific folder, why not just remove the GetOpenFilename line and use strFile as the path/filename to do the export to pdf?

In any case, here's a modification which simply informs a user who chooses a different folder that the default folder must be used and then exits before the save. The modification is highlighted in red font.

Sub CommandButton1_Click()
 Dim ws As Worksheet
 Dim strPath As String
 Dim myFile As Variant
 Dim strFile As String
 Dim NewDir As String
 Dim JobNumber As String
 On Error GoTo errHandler
 Dim wb As Workbook
 Dim ct As String
 strFile = ""
 Set ws = ActiveSheet
 Set wb = ThisWorkbook
 JobNumber = Range("C9").Text


 strFile = JobNumber & "-Register-" & (Now) & ".pdf"
 strFile = Replace(strFile, "/", "-")
 strFile = Replace(strFile, ":", "-")
 strFile = ThisWorkbook.Path & "\" & strFile


 
 myFile = Application.GetSaveAsFilename _
 (InitialFileName:=strFile, _
 FileFilter:="PDF Files (*.pdf), *.pdf", _
 Title:="Select Folder and FileName to save")
    If myFile <> "False" Then
        If myFile <> strFile Then
            MsgBox "Cannot save file to a folder other than the default folder - start again"
            Exit Sub
        End If
        ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
                If MsgBox("PDF file has been created" & vbNewLine & "Do you wish to view?", _
                vbYesNo + vbDefaultButton1, "View The PDF?") = vbYes Then
                     ActiveWorkbook.FollowHyperlink ThisWorkbook.Path & "\" & strFile & ".pdf"
                End If
     End If

exitHandler:
 Exit Sub
errHandler:
 MsgBox "Could not create PDF file"
 Resume exitHandler
End Sub

 

[Rick Rothstein]

Is it possible to report/record if a user has changed folder when offered the GetSaveAsFilename screen and then saves the file. (The saved file is then not in the expected place.)

I am not sure I understand why you are having a problem with this... the GetSaveAsFilename returns the entire path to the selected file... can't you check it against path you expect it to be and react to any differences? What am I missing here?

 

[Martin Harris]

Thank you Joe and Rick. I perhaps have not explained my reasoning very well, But between you though I have gained what I needed. Many Thanks