Excel VBA – Execute macro code in another workbook

File this in the realm of why would I ever do that – then maybe after I explain you’ll see it makes sense.

I do this in situations where I’m ‘exploding’ data into reports.  And while this is more of a PeopleSoft nVision trick, I don’t see why it wouldn’t be useable in other applications.

Let me set up the scenario.  I have a chunk of data dumped into a workbook – I’m going to call it wbSource.  Inside that workbook is a macro that is going to run thru a worksheet and select a section of it based on some value in a column.

I copy and then paste the subset of data into another worksheet.  Nice, but I want it in another workbook.

Okay.  A bit of work here, I create another workbook and use it as a template.  That workbook has whatever base formatting I want along with macro code of its own.

Inside my wbSource I have the following code:

Private Function CreateTargetWorkbook() As Workbook

Dim wbTarget As Workbook
Set wbTarget = Workbooks.Add()
wbTarget.Activate
ActiveWorkbook.SaveAs Filename:=, FileFormat:=FileFormatNum
Set CreateTargetWorkbook = wbTarget
Set wbTarget = Nothing
End Function

The above function gets called this way:

Dim wbTarget As Workbook
Dim strRunCommand as String
Set wbTarget = CreateTargetWorkbook

I do my copy and paste code here…


strRunCommand = “‘” &  & “‘!Report_Main”
Application.Run strRunCommand

The italics make it a bit hard to read.  The string you create has a single quote surrounded by double quotes, the fully qualified path and file name, then another double quote, a single quote, an exclamation point, then the name of the macro that gets run followed by an ending double quote.  That gets added as a parameter to the Application.Run command.

Once the called macro code finishes control is passed back to the code in the source/caller workbook.

Now about why I go thru this.  First of all it follows good programming practice in that this follows the Principle of least privilege.  Code that knows how to section and subsection data is now separate from code that knows how to beautify a worksheet.  It also makes it easier to debug.  And later if changes are needed to the way the page is displayed, you don’t take a chance of breaking the code that chunks thru the data.  Vice versa if a change in terms of data occurs.

Advertisements
%d bloggers like this: