Using Macro Express to Trigger an Excel VBA Macro

[IceBox]

Does anyone know if this can even be done?

 

Here is what I am trying to do:

 

Use Macro Express to gather some information from an application

Have Macro Express then activate an Excel file that is already open.

Once Excel is activated, I want a VBA macro in the Excel file to automatically be triggered (to clear the sheet)

I then need Macro Express to fill out two cells from the info it gathered at the start of the macro.

Finally, activate a second VBA macro that is already programmed.

 

I have found some code online that seems to work when the file is closed. When the file is open, I get an error stating that I already have it open. In other words, it is looking at the VBScript in Macro Express as another person trying to access the same open file, and will only allow it if I close the file.

 

I already have each of the Excel VBA macros assigned to buttons on the sheet. So having Macro Express click the button would be just as helpful as triggering the VBA macros, as it would achieve the same result obviously. (From what I can tell, the buttons are not recognized by the Get Control tool in Macro Express, or this would've be too easy)

 

Please help. I am out of ideas...

[rberq]

I already have each of the Excel VBA macros assigned to buttons on the sheet. So having Macro Express click the button would be just as helpful as triggering the VBA macros, as it would achieve the same result obviously. (From what I can tell, the buttons are not recognized by the Get Control tool in Macro Express, or this would've be too easy)

 

I wonder if you are making this too hard...

 

In the Macro Express Scripting Editor, click Tools / Mouse Locator, find the screen coordinates of the buttons, and have your macro move the mouse to the button location and click the mouse. Obviously this will work only if the spreadsheet is always in the same position on the screen, so you may want to first make it full-screen and type Ctrl-Home to position to cell A1 before doing the mouse actions.

 

Seems pretty straight-forward, or am I missing something?

[IceBox]

First, thank you for taking the time to respond to my question. I really do appreciate it. With that being said...

 

I know that is always an option, but I think it is pretty standard in the Macro Express world to use mouse moves/clicks as a LAST resort. This macro will be used by dozens of employees at my company, and I cannot guarantee they will all have the same screen resolution or even have the spreadsheet maximized.

[rberq]

This macro will be used by dozens of employees at my company, and I cannot guarantee they will all have the same screen resolution or even have the spreadsheet maximized.

Ah, yes. I always should ask whether a macro is a one-user thing or a many-user thing. Obviously makes a big difference.

 

Can you run the macros via keystrokes?

Pressing Ctrl-Alt gives keystroke access (w then m then v) to the list of macros, where you can then type a macro name and Alt-R to run it.

Maybe that would work -- presumably it would not rely on screen resolution, color scheme, etc. You wouldn't even need the macros assigned to buttons.

[IceBox]

I love that idea! I will see if it works tomorrow when I am back at it at work and I will post the results here. Thank you!

[Cory]

Triggering a hotkey like rberg said is the only way I can think of. You might have a look at this solution. You can run VBS from MEP.

 

I've done something similar to this on a couple occasions. I tried several things and eventually settled on Excel Interop. This eliminates the need for VBA macros. But you would need to be able to write a simple program or have me do one for you. Interop is very cool. And it's very similar to talking to a user. You tell it "Create an instance of Excel and run" and Excel runs and pops up on the screen. Then you tell it to create a new workbook. Then you tell it to set the contents of cell A1 to 1637. Then tell Excel to close and save it. Then you tell Excel to close itself. And of course this can all be a hidden window too. And essentially all the methods are tings a user can do. Create a worksheet, change the format of a cell, import a CSV, whatever a user can do and more. If your'e interested PM me.

[IceBox]

I took rberq's advice and it worked, and then I thought maybe there was a shortkey to open the view macro window. So maybe I could save a keystroke or two. In researching that, I found out that shortkeys can be assigned directly to the macros. So one Excel macro is now CTRL+Q and the other is now CTRL+Z. So my Macro Express code presses those shortkeys, and it is working perfectly. I kind of feel a little dumb that myself and another programmer spent hours making this harder than it needed to be, but thank you for pointing me in the right direction!

[rberq]

I have lots of experience making simple programming stuff difficult. My wife says my talent carries over into everyday life as well.

[acantor]

Here is another (untested) way to trigger VBA macros in Excel via Macro Express.

 

Let's say the Excel macro is called "Testing123"

 

Text Type (Simulate Keystrokes): <ALT><F8> // Activate the Macro dialog box
Delay: 200 milliseconds
Text Type (Simulate Keystrokes): Testing123 // Type the macro name
Text Type (Simulate Keystrokes): <ENTER> // Activate the macro

[paul]

I have lots of experience making simple programming stuff difficult. My wife says my talent carries over into everyday life as well.

It sounds as if we might both be married to the same person! How is this possible?