Using Macros in Excel with the Report Generation Toolkit

Attached are two examples that show two ways load and run macros in Microsoft Excel. The first example (Sample Report (Excel) Macro String.vi) shows how the macro module can be sent to Excel from a string. The second example (Sample Report (Excel) Macro File.vi) shows how the macro module can be loaded into Excel directly from a file.

Both VIs create a simple report in Excel using high-level Excel specific VIs and then execute a macro that sums the elements in the first row of the table. Both examples contain the same macro module. The module has two macros: SumRow and SumColumn. SumRow sums the first row of the table and SumColumn sums the first column. The macros were created using the Record New Macro function in Excel (Go to Tools » Macro » Record New Macro). When editing the macros in Microsoft Visual Basic (In Excel go to Tools » Macro » Macros... Then select your macro and choose Edit), you can either choose to export the module to a file (Go to File » Export Module) or you can copy the text and paste to a string in LabVIEW.

The Excel Import Module.vi loads the module from the string constant or file into Excel. Then the Macro SumRow is executed by Excel Run Macro.vi. This SumRow will sum the elements in the first row of the table. Finally the module is removed from Excel by calling Excel Remove Module.vi. The last step is optional, and should be skipped if the macros in the module need to be available for use in Excel after the Vi finishes.

Sample Report (Excel) Macro String.vi has the macro module stored in a string constant.

Sample Report (Excel) Macro File.vi has the macro module stored in the file Module1.bas

NOTE: Sample Report (Excel) Macro File.vi assumes that Module1.bas is located in C:\My Documents\. If this is not the case, then you need to modify the path constant in the block diagram.

IMPORTANT: If you are using Windows 2000/NT/XP you need to change an option to allow other programs to import macros into Excel. Otherwise you will get an error "Programmatic access to Visual Basic Project is not trusted". Go to Tools » Options » Security » Macro Security » Trusted Sources and place a check mark in front of Trusted Access to Visual Basic Projects.

Please see KnowledgeBase 2K1DI1OH: Error -2146827284 when Importing Macro to Office 2000/XP (linked below) for more information.

Related Links:

Report Generation Toolkit for Microsoft Office

Knowledge Base 2K1DI1OH: Error -2146827284 when Importing Macro to Office 2002/XP

Requirements

Filename: 203080.zip

Software Requirements

Application Software: LabVIEW Full Development System 6.0.2
Toolkits and Add-Ons: LabVIEW Report Generation Toolkit for Microsoft Office 1.0.1
Language(s): LabVIEW

Reader Comments | Submit a comment »