CODEWITHSUNDEEP
vbaexcel
zaza
zaza

Reputation: 912

File name presists in Dir() function over multiple sessions

I have some code in a Excel VBA macro and it seems like its persisting values between sessions. The issue is with the BtnUpdate_Click event, the message box doesnt trigger even if the file path in NewDataFilePath is invalid. 

Public NewDataFilePath As String

Private Sub BtnFileBrowse_Click()
    Dim fdlg As FileDialog
    Set fdlg = Application.FileDialog(msoFileDialogOpen)
    fdlg.Title = "Select New Dataset"
    fdlg.Filters.Clear
    fdlg.Filters.Add "Excel Files Only", "*.xls; *.xlsx"
    fdlg.Show

    If fdlg.SelectedItems.Count <> 0 Then
        TxtFilePath = fdlg.SelectedItems(1)
    End If
    NewDataFilePath = TxtFilePath.Text

End Sub

Private Sub BtnUpdate_Click()
    Dim a As String
    a = Dir(NewDataFilePath)

    If Not Dir(NewDataFilePath) <> "" Then
        MsgBox """ & NewDataFilePath & "" is not a valid file path"
    End If

End Sub

I added the string a in the event handler to try and debug the code and its showing me some interesting results:

I have a breakpoint on the if statement in BtnUpdate_Click.

File path is selected

Then i stop debugging the macro and re-run it. if i invoke BtnUpdate_Click without selecting another file the Dir() function seems to keep the last file name:

File path is blank

Any ideas why this could happen?

Upvotes: 0

Views: 56

Answers (2)

DisplayName
DisplayName

Reputation: 13386

Of course there is no persistence of any variable value once you stop a macro

While the observed behavior is due to the following:

1) FileDialog object keeps the last path chosen in its IntialFileName property

2) Dir(path) function with an empty string as "path" would return the first file in the IntialFileName path stored by last FileDialog run

so when you re-run the macro:

  • NewDataFilePath is an empty string

  • a = Dir(NewDataFilePath), would return the first file in the IntialFileName path matching the filters (if any)

What above as the answer to your question

While you could consider the following "nuance" of your code:

Private Sub BtnUpdate_Click()
    If NewDataFilePath <> "" Then 'if 'NewDataFilePath' has been set
        Dim a As String
        a = Dir(NewDataFilePath)

        If Not Dir(NewDataFilePath) <> "" Then  MsgBox """ & NewDataFilePath & "" is not a valid file path"

    Else 'otherwise
            MsgBox "No file path specified!", vbCritical ' inform the user to do so
    End If
End Sub

Upvotes: 1

YasserKhalil
YasserKhalil

Reputation: 9568

Try this code

Public NewDataFilePath As String

Private Sub BtnFileBrowse_Click()
Dim fdlg As FileDialog

Set fdlg = Application.FileDialog(msoFileDialogOpen)
fdlg.Title = "Select New Dataset"
fdlg.Filters.Clear
fdlg.Filters.Add "Excel Files Only", "*.xls; *.xlsx"
fdlg.Show

If fdlg.SelectedItems.Count <> 0 Then
    NewDataFilePath = fdlg.SelectedItems(1)
End If
End Sub

Private Sub BtnUpdate_Click()
Dim a As String

a = Dir(NewDataFilePath)

If DoesFileExist(NewDataFilePath) And NewDataFilePath <> "" Then
    MsgBox NewDataFilePath & " is a valid file path"
Else
    MsgBox NewDataFilePath & " NOT a valid file path"
End If
End Sub

Function DoesFileExist(filePath) As Boolean
DoesFileExist = Dir(filePath) <> ""
End Function

Upvotes: 0

Related Questions