PDA

View Full Version : Solved: Call sub on active worksheet using macro on Personal Macro Workbook



hkeiner
10-20-2010, 12:15 PM
My objective is to create a macro (PasteTokenData_M) on the Personal Macros Workbook that can run subs (e.g., ReplaceTokens) existing in a module (mPointLinksFieldsXls) on various worksheets. I will also add a QAT icon that will run this macro when a particular worksheet containing the subs is active. I have not been able to make this work. I get an error message on the Application.Run command. (see attached)

A simplified version of my macro code is below. Is there an obvious mistake in my code? Perhaps it is in how I am using the "GetFileName" variable to call the subs on the active worksheet. .



Option Explicit
Public Sub PasteTokenData_M()
'
'
'DETERMINE NAME OF ACTIVE DOCUMENT
Dim GetFileName As String
GetFileName = ActiveWorkbook.Name

'RUN CODE ON ACTIVE WORKSHEET TO REPLACE TOKENS
Application.Run "GetFileName!ReplaceTokens"

End Sub

Simon Lloyd
10-20-2010, 12:35 PM
I've not tested it nor have the time to test it but it may be this lineApplication.Run "GetFileName!ReplaceTokens"
should perhaps beApplication.Run GetFileName & "!ReplaceTokens"

Bob Phillips
10-20-2010, 12:39 PM
And you will need single quotes if the filename has embedded spaces.

hkeiner
10-20-2010, 03:33 PM
Application.Run GetFileName & "!ReplaceTokens"


The above change did the trick. It also seems to work OK when the file names have spaces. Thanks for the help.

I am just starting to use VBA with Excel and I am gueessing that learing some of the common tricks (pitfalls) will help me avoid a lot of frustration. For example,



1) When and why does the GetFileName variable and the sub name have to be separated by a "&" when using the Application.Run command?

2) Exactly how would I use the 'single quote' method if spaces in file names exist and do become a problem. Would it be as follows:


Application.Run 'GetFileName' & "!ReplaceTokens"

Bob Phillips
10-20-2010, 03:46 PM
1) When and why does the GetFileName variable and the sub name have to be separated by a "&" when using the Application.Run command?

When you need to concatenate a variable and a text value.



2) Exactly how would I use the 'single quote' method if spaces in file names exist and do become a problem. Would it be as follows:


Application.Run 'GetFileName' & "!ReplaceTokens"


No


Application.Run "'" & GetFileName & "'!ReplaceTokens"

mdmackillop
10-20-2010, 03:50 PM
GetFileName is a string, so & joins two strings into the one required to define the macro.

Single Quotes as follows


Application.Run "'" & GetFileName & "'!ReplaceTokens"

hkeiner
10-20-2010, 04:09 PM
Thanks guys.

I am just an accounting manager dabbling with VBA trying to develop some Excel 2007 and Word 2007 tools for my staff to use. I can see that this tip (pitfall) is something I must know and understand if I am going to get much done using VBA macros in Excel 2007. I didn't seem to have this same problem when dabbling with similar VBA macro code in Word 2007. Of course I used "Document" instead of "String" when defining the variable in Word 2007, as shown below. I am guessing that is the reason for the difference and that VBA in Excel 2007 does not have the "Document" type or something similar.


Dim GetFileName as Document

Bob Phillips
10-20-2010, 11:59 PM
In Excel, you can use Workbook


Dim GetFilename As Workbook


and use the name property



Application.Run "'" & GetFileName.Name & "'!ReplaceTokens"


but you still need the single quotes if the name has embedded spaces.

I haven't tested it here, but I would expect it to be the same in Word.

hkeiner
10-21-2010, 01:14 PM
You information has been gold for me.

I just bought myself "VBA for Dummies Excel 2007" and am plowing though it. My intent is not to become a skilled VBA programmer (my real love is accounting not programming) but I realize it is not practical (or appropriate ) to use this forum to learn the basics. I'll save my forum questions for when I am stumped after learning more of the basics.

Thanks again.

Simon Lloyd
10-21-2010, 10:51 PM
The good folk here have no problem answering any of your questions, no matter how daft they sound to you! - we all started somewhere (in fact sometimes i feel like im still in the starting blocks :))