CODEWITHSUNDEEP
excelvba
JakubTracz
JakubTracz

Reputation: 49

How to get rid of Dir Error 52 Bad File Name?

Context: In the previous sub the user creates a folder and files whose names correspond to the chosen month.
E.g. if the user chooses "March" the folder will be named "03.2020 March" and all files within it like this: "03.2020 March France" "03.2020 March Germany" etc.

I want to loop through all those files to paste a filtered data set based on the chosen country. I used DIR with wildcard characters.

Problem: When trying the DIR statement I come up against this error ("bad file name or number").

I've tried to pull out only the folder name, also with wildcard characters. I got the folder name in the Debug.Print. I assume therefore that the problem lies within the file name.

In the watch window the full path string after the first loop equals:

"C:\Users\A135604\Desktop\Projekt*March*Austria.xls*"

Sub CopyToFNOLCountrySheets()

Dim FilterRegion As Range
Dim country(1 To 10) As String
Dim i As Byte
Dim r As Byte
Dim reportmonth As String `March
Dim folderpath As String
Dim countryfile As String
Dim fullpath As String  
Dim filetoopen As String

reportmonth = ThisWorkbook.Sheets("Cockpit").Range("A16").Value
folderpath = "C:\Users\A135604\Desktop\Projekt\" & "*" & reportmonth

Set fnwb = Workbooks("FNOLDataSource")
Set fnws = fnwb.Worksheets("FNOLAllData")
Set FilterRegion = fnws.Range("A1").CurrentRegion

For i = 1 To 10
    country(i) = ThisWorkbook.Sheets("Cockpit").Cells(i + 17, 1)
    countryfile = "\*" & country(i) & ".xls*"
    fullpath = folderpath & countryfile
    filetoopen= Dir(fullpath, vbDirectory)
    Debug.Print filetoopen
Next i

Upvotes: 0

Views: 4378

Answers (1)

Storax
Storax

Reputation: 12167

Change your code like that

folderpath = "C:\Users\A135604\Desktop\Projekt\" & "*" & reportmonth
folderpath = Dir(folderpath,vbDirectory)
folderpath = "C:\Users\A135604\Desktop\Projekt\" & folderpath

This will give you the correct directory name in this step.

In the loop you need to change the line for the filetoopen

filetoopen = Dir(fullpath, vbNormal)

Upvotes: 1

Related Questions