VBA Excel Getting File Path - Not working

Question

I've been using the following code to specify a folder path in Excel:

Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
diaFolder.Show

I would Export pdf files (via VBA code) to that path. This code worked flawlessly when I had Excel 2007 running on Windows XP. I recently upgraded my operating system and office version, now I have Excel 2010 running on Windows 7. The code no longer works as it did, the problem is that the path keeps on moving up by one level every time I run the code.

For example, suppose I select the following Path:

\users\AK\Desktop\Projects\ProjectM

The actual pdfs are saved in \users\AK\Desktop\Projects\

If I select: \users\AK\Desktop\Projects\ the pdfs are saved in \users\AK\Desktop\

If I keep running the code, it will always jump up by one level, so lets say I run it 3 times sequentially (without selecting my path each time), my pdf files would be stored at: \users\AK\

I don't know if its an Excel 2010 issue or Windows 7 issue. Has anyone encountered that?

Edit: Full code included:

Private Sub CODERUN()
    ' Define values
    FN_A = Cells(2, 2).Value

    ' Print PDF version
    Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
    diaFolder.AllowMultiSelect = False
    diaFolder.Show

    ' Print PDF version
    Sheets("Part A").Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=FN_A & "_A", _
                                    Quality:=xlQualityStandard, _
                                    IncludeDocProperties:=True, _
                                    IgnorePrintAreas:=False, _
                                    OpenAfterPublish:=False
End Sub

You should show the other relvant parts of your code. What you've listed isn't enough to diagnose the issue. — Tim Williams
– Tim Williams, Commented Jan 21, 2014 at 23:26
If you run the above each time, you will have to select the path each time; I agree with @TimWilliams that you need to show a complete piece of code - how do you use the path that is returned, and how do you loop? With those two pieces of information we may be able to reproduce the problem and help. — Floris
– Floris, Commented Jan 21, 2014 at 23:53
agree with both comments above. anyways, you can also try GetSaveAsFilename to save files in any format although i haven't tried it with PDF since above is a little bit more complicated. — L42
– L42, Commented Jan 22, 2014 at 0:56
FULL CODE: Private Sub CODERUN() ' Define values FN_A = Cells(2, 2).Value ' Print PDF version Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker) diaFolder.AllowMultiSelect = False diaFolder.Show ' Print PDF version Sheets("Part A").Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FN_A & "_A", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False End Sub — AK-74
– AK-74, Commented Jan 22, 2014 at 15:07

CuberChase · Accepted Answer · 2014-01-22 23:09:07Z

1

I understand what you're asking because I've hit the same problem. I believe your problem is that if you run the code you have and then hit cancel or ok on the dialog, the next time you run the code, the initial path is up a level from the previous initial path. The reason this happens is because the Dialog does not put on a trailing \ and then truncates the path to the next \. Hence each time you use the Dialog you lose a level in your path.

The following code should work for you and you can accept or cancel until your heart is content without the initial directory changing.

Private Sub CODERUN()
    Dim sFilePath as String

    ' Define values
    FN_A = Cells(2, 2).Value

    ' Print PDF version
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        If .Show = -1 Then
            sFilePath =  .SelectedItems(1) & "\"
            .InitialFileName = sFilePath  '<--Remove this line to see the problem
        End If
    End With

    ' Print PDF version
    Sheets("Part A").ExportAsFixedFormat Type:=xlTypePDF, filename:=sFilePath & FN_A & "_A", _
                                    Quality:=xlQualityStandard, _
                                    IncludeDocProperties:=True, _
                                    IgnorePrintAreas:=False, _
                                    OpenAfterPublish:=False
End Sub

edited Jan 22, 2014 at 23:09

answered Jan 22, 2014 at 0:45

CuberChase

4,5185 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AK-74 Over a year ago

Thanks CuberChase, your solution worked in that now, the directory (i.e., path) does not change, however, when I try exporting the files to that selected folder, it keeps on saving it in a directory one level above that selected. FN_A = Cells(2, 2).Value ' Selects Name for the file Sheets("Part A").Select ‘ goes to the sheet A ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FN_A & "_A", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

CuberChase Over a year ago

@AK-74 you weren't specifying the file path in the export. I've updated the answer which I haven't tested but I'm reasonably confident it should work. Let me know.

AK-74 Over a year ago

it Worked! Thanks #CuberChase. But what exactly was the issue? was it related to Excel 2010, or Win7? Thanks again!

CuberChase Over a year ago

I'm not sure to be honest, I'm guessing Excel since it's an application call but it may not be.

Collectives™ on Stack Overflow

VBA Excel Getting File Path - Not working

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related