CODEWITHSUNDEEP
vbaexcel
Cenderze
Cenderze

Reputation: 1212

Dir function does not find a file, even after copy pasting the path as a parameter

I've tried to get the Dir() function to work some time now using a rather complicated concatenated string, as seen below:

  Dim Path as String

  Path = Dir("PathToSubfolder\" & Year(Date) & "\" & MonthName(Month(Date)) &  _ 
                             "\Production " & MonthName(Month(Date)) & "*.xlsx")
  MsgBox Path

The message box prints nothing (it's just a blank Message Box). After trying to figure out whether I had mistyped the Path somehow, I opened up the correct file, and copypasted its actual Path from options, and subsequently performed: Path = Dir("PathToSubFolder\2016\June\Production June 2016.xlsx"), i.e. without any concatenation or anything, simply just the actual filepath and -name. However, Printing MsgBox Path returned nothing (NULL) again.

Does anyone have any clue as to why this wont work? I have used Dir quite extensively the last days from the same workbook (albeit not from the same module) without any issues.

Edit: Finally found a workaround. I simply made a variable with the path to the file, pathtoFile, and subsequently:

Dim pathtoFile As String 
pathtoFile = "C:\Path.to.file\"
Path = Dir(pathtoFile & "*" & MonthName(Month(Date)) & "*")

Upvotes: 2

Views: 3288

Answers (2)

Marc-André
Marc-André

Reputation: 1

if you concatene strings you need to reattribute the value to the variable then it will be considered as a string. ex.: dossierannuel = chemingeneral & Year(Date) & "" dossierannuel = dossierannuel

Upvotes: 0

SierraOscar
SierraOscar

Reputation: 17637

Month(Date)

Will return a number, not a name. So you are passing the following argument:

PathToSubFolder\2016\6\Production 6 2016.xlsx

Which doesn't exist, hence you get a null string returned.

Try

Dim Path as String

Path = Dir("PathToSubfolder\" & Year(Date) & "\" & MonthName(Month(Date)) &  _ 
                             "\Production " & MonthName(Month(Date)) & "*.xlsx")
MsgBox Path

the MonthName() method takes a number between 1 - 12 and returns the name of that month, which is what you need for your string.

Upvotes: 2

Related Questions