ignoring part of a filename during a search

[furnace]

I am new to macros and programming in general.
I have a question/problem which i believe should be easy to fix, here it goes.

I have a macro written to find an .xls file in a certain directory.
the .xls file has a numerical value at the end which will change.
I am looking for a simple way to be able to get this file just by searching the content before the numerical value.
Ex:

ThePath = "C:\Temp\test\original_001.xls"

I want to be able to search "C:\Temp\test\orignal" and leave out the 001 part. (it will always be an .xls file)
Can this be done. I've tried looking up wildcards * but i dont think they exist in VB.
can someone help me.

 

[Tom Urtis]

Wildcards do indeed function in VBA, so would this work?

ThePath = "C:\Temp\test\original*.xls"

 

[Gary McMaster]

Have a look at the following functions in VBA help

Split
Instr
InstrRev

You could find the underscore character with any of the 3.

Hope it helps.

Gary

 

[furnace]

I believe i tried that, but it is not working
Not sure why.
I even tried
ThePath= "C:\Temp\test\original_*.xls"
and nothing.

it says cannot find C:\Temp\test\original_*.xls
it seems like it is looking for the whole file name.

 

[Tom Urtis]

Well it's odd that your variable is "ThePath" when it refers not to a path but to a specific workbook.

The syntax you (I think) should be using is

Dim ThePath as String
ThePath = "C:\Temp\test\"

Since you are referring to .xls and not .xlsx which would be a version 2007 file type extension, I assume you are using version 2003 or before.

Therefore, this would open all workbooks in the folder that are .xls so you can do something with them:

Sub OpenAllWorkbooks2003()
'Open a With structure for the Application object and prepare Excel.
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.AskToUpdateLinks = False
 
'Declare and define variables
Dim ThePath As String, iFile As Integer
ThePath = "C:\Temp\test\"
 
'Open each workbook in the source folder, and do something with it.
With .FileSearch
.LookIn = ThePath
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
For iFile = 1 To .FoundFiles.Count
Workbooks.Open (.FoundFiles(iFile))
 
'This is where your actual code would go to do whatever you have in mind with these workbooks.
MsgBox "Workbook that is open now:" & vbCrLf & ActiveWorkbook.Name & vbCrLf & vbCrLf & _
"Your code would go here to do something with these workbooks.", , "Example"
'Save and close the workbook and move on to the next one.
ActiveWorkbook.Close True
Next iFile
 
Else
 
'Advise the user if no workbooks exist in the folder.
With Application
.AskToUpdateLinks = True
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
MsgBox "No such files exist in the path " & ThePath & ".", , "Nothing to open."
Exit Sub
End If
End With
 
'Close the With structure for the Application object and reset Excel.
.AskToUpdateLinks = True
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
 
'Advise the user that the macro is complete
MsgBox "All workbooks in " & ThePath & vbCrLf & _
"have been opened and closed.", 64, "OK, all done !!"
End Sub

If 2007:

Sub OpenAllWorkbooks2007()
'Declare and define variables
Dim objWB As Workbook, ThePath As String, wbName As String
ThePath = "C:\Temp\test\"
 
'Identify the directory of interest
ChDir ThePath
'Identify the workbook extension of interest (note .xlsx for version 2007)
wbName = Dir("*.xlsx")
 
'Open a With structure for the Application object and prepare Excel.
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.AskToUpdateLinks = False
 
'Open a Do Loop to open all workbooks in the ThePath with the .xlsx extension.
 
Do
'Error bypass in case no workbooks exist in the SourceFolder with a .xlsx extension.
On Error Resume Next
Set objWB = Workbooks.Open(ThePath & wbName)
If Err.Number <> 0 Then
Err.Clear
With Application
.AskToUpdateLinks = True
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
MsgBox "There were no files with the specified extension" & vbCrLf & _
"in the path " & ThePath & ".", 48, "Cannot continue, nothing to open."
Exit Sub
End If
 
'This is where your actual code would go to do whatever you have in mind with these workbooks.
MsgBox "Workbook that is open now:" & vbCrLf & ActiveWorkbook.Name & vbCrLf & vbCrLf & _
"Your code would go here to do something with these workbooks.", , "Example"
'Save and close the workbook and move on to the next one.
objWB.Close True
wbName = Dir
Loop While wbName <> ""
 
'Close the With structure for the Application object and reset Excel.
.AskToUpdateLinks = True
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
 
'Advise the user that the macro is complete.
MsgBox "All workbooks in " & ThePath & vbCrLf & _
"have been opened and closed.", 64, "OK, all done !!"
End Sub

 

[furnace]

I should mention that the "Temp" , "test" and "original" parts of the path are strings which i have taken from cells in another workbook.
Ex.
TheDrive = "C:\Temp\"
TheSub = "test"
TheFile = "original"
note: all are stings

then
Workbooks.Open = TheDirve & TheSub & TheFile

can i use this method with a wildcard? if so, where can i put the *

 

[Tom Urtis]

In the first macro I posted, you should be OK with changing this
.Filename = "*.xls"

to this
.Filename = "original*.xls"


or for the second macro (2007 version), change this
wbName = Dir("*.xlsx")

to this
wbName = Dir("original*.xlsx")



If I were you, knowing now that the folder components are cell values, instead of hard coding the values, refer to the cells, example
TheDrive = Range("A1").Value
TheSub = Range("A2").Value
ThePath = TheDrive & "\" & TheSub & "\"

or if you have already hard-coded the variable definitions as you inferred from your last post, then
ThePath = TheDrive & "\" & TheSub & "\"

 

[furnace]

This is the way I have my code right now.....
as you mentioned in the last post
ThePath = TheDrive & "\" & TheSub & "\"
Hard coded in.

so if i added (from my last post)
& TheFile & "*.xls" to the example you gave last post, would this work?

 

[Tom Urtis]

The Swami says...Test it and see !!

 

[furnace]

I tried it.....and it didnt work.

here is my code:

Dim wbResults As Workbook<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim TheSub As String<o></o>
Dim TheFile As String<o></o>
Dim ThePath As String<o></o>
Dim TheTool As String<o></o>
Dim TheDate As String<o></o>
<o></o>
TheDate = Format(Date, "yyyy_mm_dd")<o></o>
TheTool = Range("A1") & ("-") & Range("B1") & "\"<o></o>
ThePath = "D:\Tooldata\"<o></o>
TheFile = "original_" & TheDate<o></o>
TheSub = "ABC\"<o></o>
<o></o>
Workbooks.Open ThePath & TheTool & TheSub & TheFile & "_" & “*.xls”<o></o>
<o> </o>
This gives me an error: it says “cannot find D:\Tooldata\ARC-222\ABC\original_*.xls”<o></o>
Where is the error here? Im at a loss.