PDA

View Full Version : Error while trying to run a macro using a selected workbook



simora
07-28-2025, 07:42 PM
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
07-28-2025, 09:14 PM
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
07-30-2025, 12:56 AM
You omitted a period between ActiveWorkbook and Name. This is the correct code:


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

DocAElstein
07-30-2025, 01:52 PM
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
07-30-2025, 02:46 PM
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
07-31-2025, 01:34 AM
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
07-31-2025, 02:26 AM
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
07-31-2025, 09:00 AM
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
07-31-2025, 09:27 AM
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)