Which Directory?

Martin Harris

Board Regular
Joined
Jul 24, 2012
Messages
74
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.)
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
Code:
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

Board Regular
Joined
Jul 24, 2012
Messages
74
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jul 24, 2012
Messages
74

ADVERTISEMENT

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?
 

Martin Harris

Board Regular
Joined
Jul 24, 2012
Messages
74

ADVERTISEMENT

Joe Here is the code I am using. Thank you for this - most appreciated.
Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
Rich (BB code):
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jul 24, 2012
Messages
74
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,917
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top