Executing a VBA macro from a copy of a template

Mr_Semper · May 6th, 2002, 04:44 PM

I've set up a billingsheet.xlt with embedded macros. The staff is to copy the xlt over to a xls and edit it. I am addressing the macro as "Application.Run "'DPPriceSheet1.xlt'!POST_TO_WIP", etc. - But need to change it to read something like "'thisworkbook'!POST_TO_WIP" and can't find the command. Keep in mind that the workbook could, conceivably, be called anything, so I can't force a name in.
Thanks in advance.

Qroozn · May 6th, 2002, 05:23 PM

'include the line
thisworkbook= activeworkbook.name

'this line will define the name of the current workbook being used in excel.

Mr_Semper · May 6th, 2002, 05:38 PM

Thanks. It almost worked, but I think that the single quotes around the filename is leading it to return a runtime error 438
"Object doesn't support this property or method".

Any other ideas?? Thanks

Qroozn · May 6th, 2002, 06:30 PM

you are right, now that we have defined the name of the workbook as "this workbook" we no longer need the apostrophies. so try this.

thisworkbook=activeworkbook.name
Application.Run "thisworkbook!POST_TO_WIP

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 6th, 2002, 04:44 PM	#1
Mr_Semper New Member Join Date: Apr 2002 Posts: 10	I've set up a billingsheet.xlt with embedded macros. The staff is to copy the xlt over to a xls and edit it. I am addressing the macro as "Application.Run "'DPPriceSheet1.xlt'!POST_TO_WIP", etc. - But need to change it to read something like "'thisworkbook'!POST_TO_WIP" and can't find the command. Keep in mind that the workbook could, conceivably, be called anything, so I can't force a name in. Thanks in advance.

May 6th, 2002, 05:23 PM	#2
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	'include the line thisworkbook= activeworkbook.name 'this line will define the name of the current workbook being used in excel.

May 6th, 2002, 05:38 PM	#3
Mr_Semper New Member Join Date: Apr 2002 Posts: 10	Thanks. It almost worked, but I think that the single quotes around the filename is leading it to return a runtime error 438 "Object doesn't support this property or method". Any other ideas?? Thanks

May 6th, 2002, 06:30 PM	#4
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	you are right, now that we have defined the name of the workbook as "this workbook" we no longer need the apostrophies. so try this. thisworkbook=activeworkbook.name Application.Run "thisworkbook!POST_TO_WIP