Utilizing wildcards in Excel/VBA

[HansHamm]

I am having an issue calling/activating a workbook(s) utilizing wildcards, from my main workbook, i.e. (MAP) I have it set up so that the MAP workbook will open all other workbooks in a folder utilizing names such as m1, m2, m3 etc...
The wildcard that I state is "m*.htm" in the code. This is working fine, all "M" named files open and it executes a piece of code on that particular workbook and moves onto the next workbook name "M". Here is where I hit a wall and cannot get it to work.

The line of code I have is this:

Windows("MAP Report Lite-Test Phase").Activate
Dim LastRow As Long
With Worksheets("Summary")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("D34" & LastRow)

.Formula = "=IF(ISERROR(INDEX('-*.htm'!$E:$E,MATCH(C34,'-*.htm'!$A:$A,0))),"""",INDEX('-*.htm'!$E:$E,MATCH(C34,'-*.htm'!$A:$A,0)))"
.Value = .Value
End With

End With

The error resides in the formula. I can "hard" name each file, but then I have to change it manually. I need to "read" the wildcard, so I don't have to do this...
I have tried every variation that I can think of to "call" the "m*.htm" workbook inside the "Summary" worksheet, which is in the MAP workbook to no avail.

The number of "m*" named files will vary each time, so I need it to work as the same as my opening line...find all related files named "m*"


Any help would be greatly appreciated,

Hans

 

[PHV]

Have a look at the Dir function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GVF]

Hans,

I was going to write about why I don't think you can use wildcards in a formula, but first I will ask: Have you come up with a solution?

This helps me avoid looking dumber than I am.

Greg

 

[GlennUK]

Hi GVF,

PHV has given a hint for the way to a solution, and that is using the Dir function. What's your question exactly? Or would it better to start your own thread do you think?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GVF]

PH's suggestion about using Dir is right on the mark. I was going to comment on the fact that Excel would see the wildcard characters in Hans' formula as arithmetic operators (not as wildcard characters as Hans had hoped) and so, prior to inserting the formula into a worksheet, he should determine which workbook he wants to link to and then hard code the workbook name into the formula.

Greg