Opening WorkBooks Using VBA Starting With the 1st 2 Characters

R

Richard

OS: MS XP, Excel 2003

I want to open an Excel file using VBA.
I want to use only the 1st 2 Characters of the file name.
The 1st two characters are the week number in a year.
Such as 01, 02, 03, 04 ~~~~, 09, 10, 11 Etc.
How would I use the Wild Card “*†in the file name to achieve this?
Or is there another way?


Thanks in advanced.
 
N

Norman Jones

Hi Richard,

Assuming that the two (or more)
characters uniquely identify the file
of interest, try something like:

'=========>>
Public Sub TestIt()
Dim res As String
Dim sStr As String

sStr = "02" '<<==== CHANGE
Call OpenIt(sStr)
End Sub

'------------>>
Public Sub OpenIt(sPartName As String)
Dim sFile As String
Const sPath As String = _
"C:\Users\Norman\Documents\" '<<==== CHANGE

sFile = Dir(sPath & sPartName & "*.x*")

If Len(sFile) > 0 Then
On Error Resume Next
Workbooks.Open Filename:=sPath & sFile
On Error GoTo 0
End If

End Sub
'<<=========

Depending on your requirements, the
two letter string could be passes as
the response to an InputBox or a cell
value.
 
Top