Sep 23, 2011. 'My issue is after I send a email from Excel I have to close Excel and reopen to see my files in the default file location set in the Excel options. I was going to create a macro that would send my Excel attachment then navigate to my default file location, but I can't seem to find the right commands. Setting 'Application.DefaultFilePath' before calling 'Application.GetOpenFileName' does not set the opening (default) directory.
I have googled and found answers to part of my question but not the complete question. I want to use Application.GetOpenFilename in Excel VBA to open a file and I want it to open in the same directory as ThisWorkbook.Path. I have found that beforehand I can do OpenPath = ThisWorkbook.Path ChDrive OpenPath ChDir OpenPath But, after that runs, if I run any other Application.GetOpenFilename it will still access that same directory (until perhaps I close Excel???). But, I want it to revert back to the default directory (no matter what that was). On my computer, which is Windows XP, it happens to be MyDocuments.
But, some of the people using this may have XP and some may have Windows 7. I can't find anywhere how to figure out what the original default directory was so that I can store this so that I can later reset back to the default. Any help would be much appreciated.
From a previous question I know how to go about letting the user click on a 'browser' button and navigate to a specific file that they might want to open. Code: Private Sub CommandButton2_Click() Dim vaFiles As Variant vaFiles = Application.GetOpenFilename() ActiveSheet.Range('B9') = vaFiles End Sub I want to create a second browser button that will let the user navigate to a folder. This folder is going to be where they save the.pdf file that my program creates. Here's the problem: The GetOpenFilename requires the user to click on a file.
If there's no file in the folder then there's nothing the user can do. I hope that was clear enough. Have added ErrorHandler to this in case the user hits the cancel button instead of selecting a folder. So instead of getting a horrible error message you get a message that a folder must be selected and then the routine ends.
Below code also stores the folder path in a range name (Which is just linked to cell A1 on a sheet). Sub SelectFolder() Dim diaFolder As FileDialog 'Open the file dialog On Error GoTo ErrorHandler Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker) diaFolder.AllowMultiSelect = False diaFolder.Title = 'Select a folder then hit OK' diaFolder.Show Range('IC_Files_Path').Value = diaFolder.SelectedItems(1) Set diaFolder = Nothing Exit Sub ErrorHandler: Msg = 'No folder selected, you must select a folder for program to run' Style = vbError Title = 'Need to Select Folder' Response = MsgBox(Msg, Style, Title) End Sub. In the VBA Editor's Tools menu, click References. Scroll down to 'Microsoft Shell Controls And Automation' and choose it. Sub FolderSelection() Dim MyPath As String MyPath = SelectFolder('Select Folder', ') If Len(MyPath) Then MsgBox MyPath Else MsgBox 'Cancel was pressed' End If End Sub 'Both arguements are optional. The first is the dialog caption and 'the second is is to specify the top-most visible folder in the 'hierarchy. The default is 'My Computer.'
Function SelectFolder(Optional Title As String, Optional TopFolder _ As String) As String Dim objShell As New Shell32.Shell Dim objFolder As Shell32.Folder 'If you use 16384 instead of 1 on the next line, 'files are also displayed Set objFolder = objShell.BrowseForFolder _ (0, Title, 1, TopFolder) If Not objFolder Is Nothing Then SelectFolder = objFolder.Items.Item.Path End If End Function. Hopefully my explanation of what happens is clear. If I choose an empty folder as the targetted save path and then press 'cancel' the text in my specified cell becomes 'FALSE'. When I run the program it does work, but it doesn't tell the user what folder the pdf file is being saved to (though they chose it) and when it gets saved the word 'FALSE' appears at the beginning such that 'FALSEOutput_report. Nike Vrs Covert Driver Serial Number. pdf' instead of 'Output_report.pdf'.
Can someone explain why it works like this? Thank you – May 11 '11 at 22:48.