powershot32
New Member
- Joined
- Aug 26, 2014
- Messages
- 18
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.
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.