Error while trying to run a macro using a selected workbook

[simora]

I'm trying to run a macro using Application.Run from a workbook that is selected.
I'm getting an error telling me that the macro is NOT Available.
I'm using Attach = Application.GetOpenFilename to select a workbook so that I can run a macro on it.
After I have selected the workbook, I've asssigned ol as the variable for it.
I had tried using macroName but that didn't work either, so I hardCoded the Macro Name for simpicity.

Need some help with my obviously wrong syntax.


Dim macroString As String
Dim ol As Workbook
Dim macroName As String
macroName = "MakeFinalVendors" ' The name of the Sub procedure in the target workbook
Set ol = ActiveWorkbook
ol.Activate
' Checking to make sure that I'm on the correct workbook
MsgBox ActiveWorkbook.Name
macroString = "'" & activeWorkbookName & "'!MakeFinalVendors'"
' Execute the macro
Application.Run macroString [ THIS IS GIVING ME THE ERROR ]

[June7]

Where is the code for GetOpenFilename?

Why have variable macroName then not even use it?

Where is variable activeWorkbookName declared and set?

I tested code using GetOpenFilename. The active workbook is the one with posted procedure, not the one with MakeFinalVendors.

This one-liner worked:

Sub test()
Application.Run "'" & Application.GetOpenFilename & "'!MakeFinalVendors'"
End Sub

[Jan Karel Pieterse]

You omitted a period between ActiveWorkbook and Name. This is the correct code:


macroString = "'" & activeWorkbook.Name & "'!MakeFinalVendors'"

[DocAElstein]

Hi
I have an answer, but could not get it to post here. Strangly it posted OK in the test sub forum, so you can check it out in my last post there, ( Post #5 in my Thread there )
( I am denied if I try to give you the URL link to it )
Alan

32140
32141

[June7]

Alan, you did post in this thread, with embedded images and attachments - so exactly what issue did you encounter? Was it posting code within CODE tags? Odd that it was okay in other thread.

[DocAElstein]

Hello June7
It took a long time for me to finally get this reply to you. The forum is almost certainly having issues currently…

The error I experienced / experience the most was that
Error 503

32144


I had a few others, well known to me***, such as the database one

32145




In addition to that I was occasionally told that there were forbidden words in the post. (I did try rewording but that had no effect. Finally what I posted in the other test sub forum Thread was exactly what I had tried many times and tried the most in the main Thread. It did not post first time there in the test sub forum either, as I got the Error 503 etc. , a few times there as well.)



Odd that it was okay in other thread.
I did not seem to get that main error so often in the test sub forum, that is all. That may not mean much - ### It is very difficult to draw any conclusions to where the problems lie:
Possibly this forum is having some issues similar to many forums since May of this year. I see that a few people here have mentioned these issues over the last few months. It seems to have its origins in the Forum being flooded with views from a new breed of Bots, most likely originating in the Peoples Republic of China.


***I have been fighting this problem hard for a few months now on and off over at Excel Fox
I have a ton of experiments and results, and observations, documented currently in Threads at Excel Fox. I am just starting to get some possible conclusions and solutions to the problem. (We are using the same software as here, vBulletin Version 4.2.5 )

###It is very difficult to draw conclusions as to the exact cause of such problems, or to find the solution, as what is going on is not very constant or consistent at all. For example they can occasionally go away completely, and then suddenly they come back with great vengeance, but doing their dasterdly deeds in a slightly different way.

Alan

(P.S. it is likely that as a newish member I will also be denied posting URLs for a while. I am aware that this a typical anti spam feature. )

[DocAElstein]

You omitted a period between ActiveWorkbook and Name. This is the correct code:


macroString = "'" & activeWorkbook.Name & "'!MakeFinalVendors'"

Hi Jan,
I think you may be missing a ' in your suggestion

macroString = "'" & activeWorkbook.Name & "'!'MakeFinalVendors'"



Alan

Edit, P.S: simora also had that missing ' in the original Post

Edit: Having just reached a post count of 10 I can now possibly pass a link to my solutiuon
http://www.vbaexpress.com/forum/showthread.php?71162-Just-tesitng-checkin-out-and-messin-with-the-BBCodes-and-stuff&p=426145&viewfull=1#post426145
https://www.excelfox.com/forum/showthread.php/2404-Notes-tests-ByVal-ByRef-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=25100&viewfull=1#post25100

[June7]

Oops, copy/paste error on my part - I copy/pasted OPs code for macro name part to replace my macro name. My corrected code without extra apostrophe:


Application.Run "'" & Application.GetOpenFilename & "'!MakeFinalVendors"
Apostrophes around macro name are not required but will work.

[DocAElstein]

Oops, copy/paste error on my part - I copy/pasted OPs code for macro name part to replace my macro name. My corrected code without extra apostrophe:


Application.Run "'" & Application.GetOpenFilename & "'!MakeFinalVendors"
Apostrophes around macro name are not required but will work.
Ahh, yes, I had not noticed that you also had that error.
I learnt something as well. I never noticed that one could do away with the Apostrophes around the macro name.
(Access to the forum seems better for me today)

[Paul_Hossler]

Ahh, yes, I had not noticed that you also had that error.
I learnt something as well. I never noticed that one could do away with the Apostrophes around the macro name.
(Access to the forum seems better for me today)

Actually I think the single quotes are around the workbook file name, not the macro name

If the wb file name contrains spaces I'd bracket it with single quotes.

Since a macro name cannot have spaces, it's not really a problem thee

[June7]

The apostrophes HAVE to be around filepath\name even if there are no spaces. They CAN be around macro name.

[DocAElstein]

Hi
Yes that is the thing.
I had prepared an answer to Paul_Hossler, but had the problem again of not getting access to this forum, and so consequently also missed your reply as well, June7, ( ........There may be other replies also, but you will not receive any more notifications until you visit the forum again.........)

I am basically saying the same thing in slightly more detail with the OPs example.
The TLDR
_1 As you say, (Paul_Hossler ) on the RHS of the ! you will never need bracketing with ' s, (because it is a macro name that cannot have spaces (As June7 says, they CAN be there) )
_2 On the LHS of the ! you will need bracketing with ' s in two situations
_2a) If the workbook name has a space or more in it
_2b) if you are using a full path, regardless of whether the entire string has any spaces or not. (It seems to be the path that insists on the bracketing with ' s , I have seen that sort of thing in other situations))


Maybe just for completeness here and posterity I will just drop a link to the reply I had prepared, (as it is a longer reply that tends to increase the likelihood of me getting difficulty in posting due to the current forum software issues here)

https://www.excelfox.com/forum/showthread.php/2404?p=25102&viewfull=1#post25102
https://www.excelfox.com/forum/showthread.php/2404/page5#post25102

Alan

[Paul_Hossler]

:thumb