Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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

In reply to Re: Perl Excel and ODBC by davies
in thread Perl Excel and ODBC by ashojoseJ

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and all is quiet...

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

      Results (257 votes). Check out past polls.