Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

RFC Tutorial: Adding and extracting VBA to and from Excel files

by davies (Prior)
on Sep 23, 2011 at 11:19 UTC ( [id://927532]=perlmeditation: print w/replies, xml ) Need Help??

Yesterday, two people expressed interest in the chatterbox in getting VBA out of Excel and into a text file. Away from PerlMonks, a friend asked the same thing later in the day. I put up a smaller variant of the following code in my scratchpad for those on line at the time, and thought it might be worth both explaining the code in detail and expanding the code to show how to import a module as well. Comments and criticism will be gratefully received.

Importing and Exporting VBA using Excel and Win32::OLE

Introduction

This is not intended as an introduction to automating Excel with Perl. There are plenty of other nodes and resources on how to do this. Please post requests for advice on code as new SOPW posts, not as replies to this tutorial.

It is not currently possible to extract or insert code using Spreadsheet::WriteExcel or any of its close relatives. I have proposed a technique to allow this to John McNamara (jmcnamara, the author). He has offered to get me started if I want to write the code, but the existing code is too advanced for me to understand. We are both working on other projects, so don't expect anything to happen in the short term. Progress will depend on either or both of us getting the tuits.

use strict; use warnings; use Win32::OLE; my $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1; my $wb = $xl->Workbooks->Add; if ($wb->Sheets->{Count} > 1) { for (2..$wb->Sheets->{Count}) { $wb->Sheets(2)->Delete; # +Comment 1 } } $xl->VBE->ActiveVBProject->VBComponents->Add(1); # +Comment 2 my $cm = $xl->VBE->ActiveVBProject->VBComponents(3)->CodeModule; # +Comment 3 my $line = int($cm->CountOfLines); # +Comment 4 $cm->InsertLines(++$line,"Function Example(n as double) as double"); # +Comment 5 $cm->InsertLines(++$line,"Example = n^2"); $cm->InsertLines(++$line,"End Function"); my $filename = "z:\\data\\perl\\Example.bas"; # +Comment 6 $xl->VBE->ActiveVBProject->VBComponents(3)->Export($filename); # +Comment 7 $xl->{DisplayAlerts} = 0; # +Comment 8 $wb->Close; $wb = $xl->Workbooks->Add; $xl->VBE->ActiveVBProject->VBComponents->Import($filename); # +Comment 9

Comment 1

My installations of Excel start with one sheet. So, I suspect, should yours, although they probably start with three for no better reason than three being Bill Gates's default. In this simple example, it is easier to know how many sheets there are than to write more complicated code to find out a number that we will use later

Although it is not the subject of this tutorial, a FAQ is how to delete $object in Excel. Note that this line deletes sheet 2 every time. This is because Excel re-indexes after every deletion. If I used $_ instead, I would get an error if you start with three or more sheets. Assuming 3, the first pass would delete sheet 2, but sheet 3 would then be re-indexed so that it became Sheets(2). The second pass through the loop would attempt to delete the third sheet of 2 and would throw an error.

Comment 2

The add command requires a parameter telling it what type of component is to be added. 1 is a standard code module, and can sometimes be represented in VBA as vbext_ct_StdModule. However, this cannot be used as a constant in Perl via the normal route because it is not an Excel constant but a VB extension constant. Even using it in Excel requires the insertion of a reference to "Microsoft Visual Basic for Applications Extensibility $version".

Comment 3

This is where we need to know how many objects there are in the file. By restricting the number of sheets to 1 in a new file, I know that the code module just added will be number 3, after the single sheet and ThisWorkbook. In anything more complicated where there may be an unknown number of objects, you will need to look at VBComponents->{Count} to find out what its number will be.

Comment 4

Strange though it may seem, a newly added module may not be empty. They certainly aren't on my setups. I always set the option to "Require variable declaration". This means that VBA inserts Option Explicit at the top of every module. This is roughly equivalent to Perl's use strict;. If you don't know why you should do this, I suggest you stop reading this and look at something at a more introductory level.

Comment 5

It is important to increment the line count before adding the text, otherwise the relevant line will be overwritten.

Comment 6

You will need to double the backslashes that Windows needs in paths if you use double quotes. Single quotes will enable you to use single backslashes, but you will not be able to include variables. This file name works on my systems. It is extremely unlikely to work on yours, but I can't predict what your systems will look like.

Comment 7

This creates the text file everyone wanted yesterday. It does not delete it from the current workbook.

Comment 8

This is needed to prevent users having to respond to a confirmation dialogue box.

Comment 9

Code can be entered line by line using the technique shown above. But importing an entire code module is far simpler and probably more common. Having closed the workbook without saving it, we now create a new workbook. This time, I don't care how many sheets - or other components - it has.

Closing remarks

This is an extremely dangerous technique. It is possible, using it, to write malicious software. VBA is Turing complete, so anything can be done (given Excel's powerful lookup features, I've often wondered about writing an assembler in Excel. It might not even need VBA). But I accept no liability whatsoever for any problems that result from you using this technique. There is no way to check the code that is passed to Excel, so apart from being malicious, it might be full of syntax errors or otherwise buggy. There is not even the slightest guarantee that the code is even in VBA.

This is also a very powerful technique. I use it routinely to automate the writing of spreadsheets, tests and documentation. There's nothing either good or bad but thinking makes it so (Shakespeare, Hamlet, act 2 scene 2 I think).

Update 2014-12-31 See http://www.theregister.co.uk/2014/12/31/vbscript_office_macros_peril/ for a comment on some of the dangers.

Regards,

John Davies

Updates: Noted John McNamara as author of S:WE (Thanks MidLifeXis).

Modified comment 6 to suggest use of single quotes. I think it's better to point out this issue than just to use single quotes, as I expect most people to be using variables and if the contents come from Excel, they will be without the trailing backslash. Therefore people are likely to want to combine variable expansion with backslashes. Thoughts on this are welcome (Thanks MidLifeXis).

Replies are listed 'Best First'.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://927532]
Front-paged by jmcnamara
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (4)
As of 2024-03-19 05:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found