open workbook using a wildcard

[almagg]

i want to open a workbook that i know starts with "EMEA".
each month this file will be in a folder that has the month name e.g. Jul FY11
the path is C:\\Monthly Reports\Jul FY11\EMEA...

there are other files that will be opened also and the wildcard will be in different places i.e., *[reference word] or *[reference word*] or [reference word]*

thanks for the help

 

[Ruddles]

1. The Dir() function will return the first file which matches the name pattern you pass to it.

2. You can inject parts of dates into a string using the Format() function.

Try something like this:-

[FONT=Fixedsys]Dim sFolder As String
Dim sFileMask As String
Dim sFile As String[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]sFolder = "C:\Monthly Reports\" & Format(Now(), "mmm") & " FY" & Format(Now(), "yy") & "\"
sFileMask = "EMEA*.*"
[/FONT]
[FONT=Fixedsys][COLOR=blue]MsgBox "Looking for " & sFolder & sFileMask[/COLOR][/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]sFile = Dir(sFolder & sFileMask)[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys][COLOR=red]If sFile <> "" Then
  Workbooks.Open sFolder & sFile
Else
  MsgBox "Couldn't find file " & sFolder & sFileMask
End If[/COLOR][/FONT]

The bit in red you'd modify to do whatever you want done when the file is found or if it fails to be found. You'd remove the bit in blue in the finished code - that's just there to show you what's hapening.

 

[almagg]

ok
thanks
changed the date because we get the data for the july report in august.

anyways i thought this should have worked

Workbooks.Open "C:\Monthly Reports\" & Format(Now() - Day(Now()), "mmm") & " FY" & Format(Now(), "yy") & "\TFP EHS Report\Flash Report*.*"

but error message says file cannot be found.

 

[Ruddles]

Are you saying that the code is generating the correct filename but VBA is claiming that the file doesn't exist, or are you saying that the code is generating the wrong filename?

If you use the filename that's being generated and use it to open the workbook manually, does that work?

 

[almagg]

just to be clear - your code works.

sFolder = "C:\Monthly Reports\" & Format(Now() - Day(Now()), "mmm") & " FY" & Format(Now(), "yy") & "\TFP EHS Report\"
sFileMask = "Flash Report*.*"

and the file opens using your code.

but when i try this:

Workbooks.Open "C:\Monthly Reports\" & Format(Now() - Day(Now()), "mmm") & " FY" & Format(Now(), "yy") & "\TFP EHS Report\Flash Report*.*"

the message box says "...the file cannot be found...check spelling...path etc..."

 

[Ruddles]

I don't know. Your file is definitely called:-
C:\Monthly Reports\Jul FY11\TFP EHS Report\Flash Report*.*
?

And that string doesn't open the file in the macro or if you try to open the file in Start > Run?

 

[almagg]

no
i just tried Start > Run and it did not find it.
i dunno

 

[Ruddles]

Sorry, I said a stupid thing: that would never have worked with a wildcard in the file name. Sorry!

In your code, insert the following lines immediately before the Workbooks.Open command:-

[FONT=Courier New]Dim sFileMask As String[/FONT]
[FONT=Courier New]sFileMask = "C:\Monthly Reports\" & Format(Now() - Day(Now()), "mmm") & " FY" & Format(Now(), "yy") & "\TFP EHS Report\Flash Report*.*"
If Dir(sFileMask) = "" Then[/FONT]
[FONT=Courier New]  MsgBox sFileMask & " doesn't exist!"[/FONT]
[FONT=Courier New]Else[/FONT]
[FONT=Courier New][FONT=Courier New]  MsgBox sFileMask & " exists."[/FONT]
End If
[/FONT]

If it reports "doesn't exist!" then there isn't a file with that specification and you need to check the filename which is being generated very closely against the name of your file, including the folder names.

 

[almagg]

yeah, it 'exists'
like i said your code worked.
thanks.

 

[Ruddles]

Sorry, I misunderstood... it's been a long day at the end of a long week!

As long as you have a working set of code, that's the important thing.

Strange that one set worked and the other didn't though.