How to Search Directory for An incomplete file name in VBA

[powershot32]

Hey everyone. I am trying to write code for a macro in VBA to search my directory and open a different excel spreadsheet. The basics of the macro would be taking the ActiveCell and searching a folder for a file that contains that value and opening the excel spreadsheet. I am struggling with how I can use the string value for the ActiveCell and insert it into either Dir() function or other search functions.

Currently I have

Dim SearchValue As String
SearchValue = ActiveCell.Value

The values entered in the activecell would be something like A12345 and the file names in the directory are like A12345 and then a bunch more descriptive numbers and words afterwords.

So then I have

Dir\filepath & SearchValue*.xls)

But this obviously isn't working as it is just searching the directory for any files called SearchValue. I can't figure out how to put the string value in the Dir function.

Also there are subfolders within the directory that could potentially hold the file too so is there a better function to search and open the file or should I just run a loop with more Dir() functions with the subfolders until the file is found?

Thanks for any help.

 

[Andrew Poulsom]

How about?

Workbooks.Open "K:\PMT\Computers & Electronics\" & ActualValue

 

[powershot32]

How about?

Workbooks.Open "K:\PMT\Computers & Electronics\" & ActualValue

That worked. Thank you very much.

 

[iliauk]

View; locals window will help with this kind of debugging!

I'm wondering if it is trying to find a file with .xls.xls instead of .xls. The error says



So does that mean its looking for a file with the name FILE.xls.xls? or is it just looking for FILE.xls.

 

[powershot32]

Ok new problem.

I'm trying to create this for another directory and its not working.

I'm using the same code except changing the directory to another folder. I keep getting the same error message no matter what folder I use except if I try the original directory.

So the first code which works perfectly fine is

Dim SearchValue As String, ActualValue As String
SearchValue = ActiveCell.Value
ActualValue = Dir("K:\PMT\Computers & Electronics\" & SearchValue & "*.xls")
Workbooks.Open "K:\PMT\Computers & Electronics\" & ActualValue

Now for a folder within that directory

Dim SearchValue As String, ActualValue As String
SearchValue = ActiveCell.Value
ActualValue = Dir("K:\PMT\Computers & Electronics\Electronics\" & SearchValue & "*.xls")
Workbooks.Open "K:\PMT\Computers & Electronics\Electronics\" & ActualValue

Everything is the same except the Directory, which is changed only by adding the folder "Electronics" past the original Computers & Electronics folder. Should be fine right? Well it gives me this error message.

Run-time error '1004':

Sorry we couldn't find K:\PMT\Computers & Electronics\Electronics\. Is it possible it was moved, renamed or deleted?

The folder is there so I don't know why it's giving me this message. It's like the code is trying to find a file called Electronics which that doesn't exist, so i can understand why it wouldn't find it, but there is a folder called Electronics and I don't understand why the Dir() function would start searching for a file type when I have the \ after Electronics and the & with the file after. I've also tried entering the exact path for a file in the Workbooks.Open function, i.e Workbooks.Open "K:\PMT\Computers & Electronics\Electronics\ExampleFile.xls" and it still gives me the same error that it cannot find "K:\PMT\Computers & Electronics\Electronics\". So what is going on?

 

[Andrew Poulsom]

If you record a macro while opening a workbook in that folder what VBA code do you get?

 

[powershot32]

If you record a macro while opening a workbook in that folder what VBA code do you get?

Workbooks.Open Filename:= _
"K:\PMT\Computers & Electronics\Electronics\SAMPLEFILE.xlsx" _
, UpdateLinks:=0


Also I found out if I leave the active cell blank, the macro will open the most recently edited file within the folder. So it finds the correct folder, in this case "K:\PMT\Computers & Electronics\Electronics\" and then opens up that most recent file. So don't know if that gives you any ideas.

Also tried putting in Filename:= before the directory path on the original macro and it doesn't work, same message.

 

[Andrew Poulsom]

It would appear that a file that begins with what's in the ActiveCell does not exist in that folder, so Dir is returning Null.

 

[powershot32]

So why when I take the exact file path, and take the exact file name from the folder and put it into a Workbooks.Open function it still doesn't work?

Although when I do it that way it gives me the same error message but then a slightly different error message that reads

Microsoft Excel cannot access the file "SAMPLEFILE". There are several possible reasons:

- The file name or path does not exist.
- The file is being used by another program.
- The workbook you are trying to save has the same name as a...(can't read the rest)

 

[Andrew Poulsom]

Is it SAMPLEFILE.xls or SAMPLEFILE.xlsx. Your code is looking for the former.

As an aside I suggest that you uncheck 'Hide extensions for known file types' in Windows Explorer Folder Options.

 

[powershot32]

It would appear that a file that begins with what's in the ActiveCell does not exist in that folder, so Dir is returning Null.

Wow, ok it was as easy as switching the file search from .xls to .xlsx because it was a newer Excel file. Is there a way I can search for both .xls and .xlsx within that code?

Nevermind, just made and if then statement where if ActiveValue = "" then look for .xls files.

Thanks again for all the help.