Wildcards In VBA Macros
 

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[b]*.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

--
TONY
-----------------------------------------------------------------------
TONYC's Profile: http://www.excelforum.com/member.php...fo&userid=1246
View this thread: http://www.excelforum.com/showthread.php?threadid=26196

Wildcards In VBA Macros
 

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.

"TONYC" wrote:


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

My quesion is could I use a wild card (eg 'Risk Question[b]*.xls') to
extract data from these files. I havent changed the macro as its very
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

Tony


--
TONYC
------------------------------------------------------------------------
TONYC's Profile: http://www.excelforum.com/member.php...o&userid=12468
View this thread: http://www.excelforum.com/showthread...hreadid=261967

Wildcards In VBA Macros
 

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

"TONYC" wrote in message
...

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

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

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


Regards

Tony


--
TONYC
------------------------------------------------------------------------
TONYC's Profile:
http://www.excelforum.com/member.php...o&userid=12468
View this thread: http://www.excelforum.com/showthread...hreadid=261967