Using msoFileDialogFolderPicker to select a folder

Question

Hello

The following code is written to allow the user to select the folder where they wish to save a PDF of the worksheet and the file name will consist of data located in cells D4 and D5.  It also checks to confirm that the data in cell D5 contains 3 characters.  The code works fine but with one issue.  If the user drills down say 4 levels of subfolders the file does not save in the folder, it saves in the folder one level up. 

I can't figure out why the file is not saved it the folder selected.

Can someone indicate what I am missing?

Thank you

Answer 1

Hi

Have a try with this

Dim strCurrDir As String

Dim fldpath As String

Debug.Print CurDir

strCurrDir = CurDir 'keep track of current directory

Application.FileDialog(msoFileDialogFolderPicker).Show

Debug.Print CurDir

fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

ChDir fldpath 'chang to the new directory

Debug.Print CurDir

'Do your stuff

ChDir strCurrDir 'reset to previous current directory

End Sub

HTH

Regards

JY

Answer 2

Post your actual code, not just a picture of it....

Do you know how I can paste a copy of my code instead of typing it in?  Paste does not work for me?

Answer 3

Hi

Have a try with this

Dim strCurrDir As String

Dim fldpath As String

Debug.Print CurDir

strCurrDir = CurDir 'keep track of current directory

Application.FileDialog(msoFileDialogFolderPicker).Show

Debug.Print CurDir

fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

ChDir fldpath 'chang to the new directory

Debug.Print CurDir

'Do your stuff

ChDir strCurrDir 'reset to previous current directory

End Sub

HTH

Regards

JY

Changing to the new directory before running the Export worked.

Thank you  JY

Answer 4

I just copy and paste - but a picture will do. I thought you may have a error in your code and it is easier with actual code.

What happens with

Sub TestMacro()

    Dim FldPath As String

    Application.FileDialog(msoFileDialogFolderPicker).Show

    FldPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

    ActiveSheet.ExportAsFixedFormat xlTypePDF, FldPath & "\Eng test export.pdf"

End Sub

Answer 5

I just copy and paste - but a picture will do. I thought you may have a error in your code and it is easier with actual code.

What happens with

Sub TestMacro()

    Dim FldPath As String

    Application.FileDialog(msoFileDialogFolderPicker).Show

    FldPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

    ActiveSheet.ExportAsFixedFormat xlTypePDF, FldPath & "\Eng test export.pdf"

End Sub

For some reason I am not able to paste to this window.

I have added a line

"ChDir fldpath" right after the "fldpath = Application..........." line and the code is working fine now.

Thanks for your input.