Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Re: Perl Excel and ODBC

by davies (Parson)
on Oct 07, 2011 at 15:41 UTC ( #930204=note: print w/replies, xml ) Need Help??

in reply to Perl Excel and ODBC

Congratulations. You have successfully identified one of the most horrible rat's nests of complexity that Microsoft has ever inflicted on this miserable world. You are likely to catch some flak for not showing us what you have tried, though. In general, it's a good idea to record a macro in Excel and then to try to translate that into decent Perl, bearing in mind that the VBA generated by the macro recorder is not decent VBA.

Getting back to what you want, however, I think it will look like this:

use strict; use warnings; use diagnostics; use Win32::OLE; my $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1; my $wb = $xl->Workbooks->Add(); for (2..$wb->Sheets->{Count}) { $wb->Sheets(2)->Delete; } my @cmd = ("SELECT bug_severity, short_desc FROM bugs"); my $qry = $wb->Sheets(1)->QueryTables->Add({ Connection => "ODBC;DATABASE=bugs;DSN=MyODBC;OPTION=0;PWD=sockmon +key;PORT=0;UID=bugs", Destination => $wb->Sheets(1)->Range("A1")}); $qry->{CommandText} = \@cmd; $qry->{Name} = "Query from MyODBC"; $qry->Refresh({BackgroundQuery => 0});

The first two blocks in the code are pretty standard stuff needed to set up a clean instance of Excel. I'm not sure whether Corion's approach will actually do what you want. If you need to embed a query in the Excel spreadsheet so that a user will be able to refresh it without having Perl on his machine, I can't immediately see how it can be done that way. The code above will embed the SQL query so that it can be refreshed provided that the ODBC setup is the same on the target machine. This is not something I know how to control from Perl!

You won't want to embed the user name (UID) and password in the code, but these are the defaults for BugZilla, and if anyone wants to see what I have on my machine, they're welcome as it's currently meaningless.

The real nonsense comes with the CommandText option. Excel requires this to be an array. Passing it as a reference seems to be essential, as if you leave out the backslash, you get nothing unless you use diagnostics, in which case you get a SQL error. Passing the array without the backslash and printing the contents of the CommandText property returns the number of elements.

Nor does the nonsense stop there. While the SQL is effectively unlimited in length, each array element is limited to something close to 240 characters. It's not 255 characters or anything sensible. You might want to write a little loop to check the array element lengths before committing to the query, as with the tail of a few lines knocked off in the wrong places, you might get something seriously unpatriotic happening to your server.


John Davies

Update: fixed two minor typos

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://930204]
[Discipulus]: go to sleep LanX I'll give you a phone when it will happens..
LanX really needs a new phone
Discipulus LanX is obsessed by Brexit..

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (9)
As of 2018-02-23 11:18 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (301 votes). Check out past polls.