CODEWITHSUNDEEP
vbaexcel
AK-74
AK-74

Reputation: 13

VBA Excel Getting File Path - Not working

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

Upvotes: 1

Views: 4163

Answers (1)

CuberChase
CuberChase

Reputation: 4518

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

Upvotes: 1

Related Questions