Opening WorkBooks Using VBA Starting With the 1st 2 Characters

  • Thread starter Thread starter Richard
  • Start date Start date
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.
 
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.
 
Back
Top