From Excel VBA to an MEP variable?

[terrypin]

Anyone here using VBA with ME/MEP please? I'm wondering if there's a better method than I'm presently using to get data out of Excel and into an MEP variable.

For example my VBA to copy cell B3 in Excel to the existing MEP variable looks like this:

 

Sub CopyTrackDescrToClipbrd()
' Copies contents of B3 in active TrackDataSheet to clipboard
' Remains on clipboard after macro finishes
    Range("B3").Copy
End Sub

And my MEP macro includes this:

 

// Source is ACTIVE trackdata worksheet.
Window Activate: .xlsm
Wait for Window Title: .xlsm
Delay: 0.2 seconds
Text Type (Simulate Keystrokes): <ALT><F8> // Opens Excel's Macro dialog.
Delay: 0.2 seconds
Text Type (Use Clipboard and Paste Text): PERSONAL.xlsb!CopyTrackDescrToClipbrd
Delay: 0.2 seconds
Text Type (Simulate Keystrokes): <ENTER>
Delay: 0.2 seconds
Variable Set String %tTrackDescr% from the clipboard contents

 

 

[Cory]

I can't help you with that Terry. Years back I had a task to seek out and extract data from thousands of XLSX files. But there weer some inconsistencies so I need to use some logic to find the data i needed or flag those which needed to be done by  a human. I started messing with VBA and MEP but then I tried Microsoft.Office.Interop.Excel. VBA is designed to in in Excel. Interop is an API for other programs to access Excel programatically. Exactly what you want to do. It's very cool and it works much the same as commanding a human. And since it's going to be in a modern language like VB.NET, C# or whatever, it's much simpler to write the program. 

I was intimidated by it but I installed the free version of Visual Studio and created my first .NET program. It was surprisingly simple. Since I didn't really know enough to write the whole program in VB, I simply exported a worksheet to CSV and then did the rest in MEP. Later I did many other projects with Inerop and each time it was a joy. One doesn't even need to have Excel visible. Just BAM and done. For more reliable and simple that trying to mess around with MEP to export it. 

I know it's not the kind of solution you're seeking, but if you're interested in such an approach, let me know and I can either write a program for you quick or help you learn. Here's a good example as a teaser.  But if you decide to keep going the VBA method, I hope it goes well. 

 

[terrypin]

Thanks Cory, appreciate the fast response and your kind offer.

 

It's tempting to tackle yet another tool but I'm going to pass thank you. I have a hard enough time re-learning enough VBA each time I come to write a new Sub, or even edit one I wrote a year or decade ago!

 

And for the last few weeks I've also been studying the 'Arduino' version of C/C++. I decided I needed Arduino to help me drag my practical electronics hobby into the 21st Century. Downside is a shed workshop full of ancient components that will mostly never get used 😉

 

But your mention of CSV prompts me to reconsider more carefully in future the relative merits of using Excel versus plain text files. Upsides of Excel include visual appeal for many of my applications (see example below), plus access to VBA (which can be SO much faster than MEP). But Text would let me work more easily in MEP. 

[acantor]

Hi Terry,

 

The method I use to trigger a VBA script via Macro Express is similar to yours. Although kludgy, it gets the job done reliably, which is important given that MEP and VBA are very different beasts that don't talk to one another.

[Cartwheels]

I combine the the 2 a lot. I do things similar to you if not the same. If I start off with excel, I will use vba to get to the cell and even use the copy command in vba before triggering MEP. I’m sure you are aware of the command line triggers for vba that start a MEP macro. Then if there are more cells I use MEP to navigate to those cells. If it is a larger range of cells I use ASCII. ASCII is great for getting lots of info through into variables, but the data needs to be organized in rows and columns. If running MEP first, active excel (macro enabled worksheet) if it is 1 cell use F5 to get the “Go To” window use MEP to type in the cell (or range for ASCII). Like your example,  an excel macro by using alt F8, use MEP to type in the name of the excel macro. I like to use a unique name to avoid screwing things up.

 

By combining these to methods you can trigger back and forth between the programs.

[terrypin]

Many thanks Cartwheels, some interesting new angles that I will explore. Good to know of at least one other VBA/MEP user.

 

BTW, I’ve sometimes even tried OCR as a data transfer tool (and not just with Excel). It can be very handy for small volumes when other methods are elusive, but of course  can never be relied upon at the 100.0% level.

 

Digressing,  I’d  love an MEP feature that would allow us to hide all external operations initiated by MEP. Even better, the facility to go off and do other stuff after starting a macro!

[terrypin]

Thanks Alan. I’d forgotten that you too are using VBA.

 

Yes, that basic method does indeed seem to be working reliably here. Just wish I could find something that did it in the background.

[Pete]

I know this topic is 6 months old but I thought I'd add something....

 

The following registry key can be accessed from and written to by VBA code.

 

HKEY_CURRENT_USER\Software\VB and VBA Program Settings\

 

I have many ME macros which open an Excel Workbook and run a specific Excel macro utilising this registry key to pass data.

 

To run a specific Excel Macro when a workbook is opened:-

 

1. Add the following code to the Excel Workbook

 

Private Sub Workbook_Open()
    Dim sMacroName As String

   
    'Run the Macro in Registry RunExcelMacro, Clear Registry value
    
    sMacroName = GetSetting("MyProject", "RunExcelMacro")
    SaveSetting "MyProject", "RunExcelMacro", ""
 
    If sMacroName <> "" Then
        Application.Run sMacroName
    End If

End Sub

 

2. Write a registry string containing the name of the Excel Macro, E.G. HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyProject\RunExcelMacro

3. Launch your Excel workbook and the Excel Macro will run.

 

Hope this helps someone, Pete

 

 

[terrypin]

Interesting, thanks Pete, I'll explore that alternative more thoroughly to see if I can get a simple example working. But from initial study it looks a fair bit more complex than my usual method of launching a VBA macro from an MEP macro, namely with Alt+F8 as discussed up-thread.

 

However it opens up a possible new approach to transferring data between Excel and MEP that I'd never been aware of before. I've been using either

- the clipboard

- or reading an intermediate (and otherwise redundant) text file.

So many thanks for the heads up!