CODEWITHSUNDEEP
excelvbafile
Istvanb
Istvanb

Reputation: 412

How Dir() works in VBA

In my current VBA code I generate a variable called myFileName and would like to use it as a parameter when I am checking if the file exists but I get an error message "Runtime error 52, bad file name or number" in the line I use the Dir command.

Interestingly if I type the the filepath to the Dir command manually instead of using the myFileName variable then it works without an issue. (no typo, I can use the myFileName in reading or writing the file, it only drops an error for the Dir command)

Any ideas how can I make the Dir(myFileName) working?

    Set fs = CreateObject("Scripting.FileSystemObject")

    myFileName = Environ("USERPROFILE") & "\Application Data\myFile.txt"

    If Dir(myFileName) = "" Then
        Set a = fs.CreateTextFile(myFileName, True)
        a.Write ("0")
        a.Close
    End If

Upvotes: 2

Views: 523

Answers (2)

Doug Coats
Doug Coats

Reputation: 7117

your issue is:

myFileName = Environ("USERPROFILE") & "\Application Data\myFile.txt"

it should be

myFileName = Environ("USERPROFILE") & "\Application Data" & "\" & "myFile" & ".txt"

This is a common question and really worth acknowledging that you have to insert the last forward slash like this. IDK why vba reads like this, but this is the work around

I just ran this with no issue and the file created. 

Private Sub createafile()
    Set fs = CreateObject("Scripting.FileSystemObject")

    myFileName = Environ("USERPROFILE") & "\Desktop" & "\" & Format(Date, "ddmmyyyy") & ".xlsm"

    If Dir(myFileName) = "" Then
        Set a = fs.CreateTextFile(myFileName, True)
        a.Write ("0")
        a.Close
    End If
End Sub

Upvotes: 0

Florent B.
Florent B.

Reputation: 42538

The \Application Data is not a folder but a shortcut. Use LOCALAPPDATA or APPDATA instead:

myFileName = Environ("LOCALAPPDATA") & "\myFile.txt"
myFileName = Environ("APPDATA") & "\myFile.txt"

Upvotes: 2

Related Questions