Wildcards In VBA Macros

[TONYC]

I have devised a long macro (only a small part shown below) whic
extractes data from a file called 'Risk Question.xls.' when complete
and returned to me via Microsoft outlook. However, since its creatio
I have aquired Windows XP, which I understand adds a sequential digi
after the file name when opened (ie Risk Question1.xls), when openin
several files woith the same name one after the other. (Even thought
ensure I close the active file beofre openeing the new file.)

My quesion is could I use a wild card (eg 'Risk Question*.xls') t
extract data from these files. I havent changed the macro as its ver
long and wanted advise first.

Name
Windows("Risk Question.xls").Activate
Range("C5:E5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Risk Register.xls").Activate
Range("C" & nextlineno).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False


Regards

Ton

 

[Guest]

Tony,

Rather than use wild cards, when you open a file (and before you do anything
else) read the files name into a string variable and then navigate using that
variable. This sould pick up any sequential digits and make your life much
easier.

 

[Bernie Deitrick]

Tony,

Actually, you shouldn't care about the filename. The best way to be
filename-independent is to use a variable declared as a workbook object:

Dim myWBK As Workbook

Then when you open the workbook, you set the object variable to be the
workbook:

Set myWBK = Workbooks.Open(....)

And then instead of using

Windows("Risk Question.xls").Activate

you can use

myWBK.Activate

Of course, you rarely need to activate or select (pastespecial is a common
exception to that rule), but that will be a lesson for another day ;-)

So your code could be something like this, after setting the myWBK object
variable:

myWBK.Activate
Worksheets("Sheetname").Select
Range("C5:E5").Copy
Range("C" & nextlineno).PasteSpecial Paste:=xlValues

HTH,
Bernie
MS Excel MVP