Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

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.

Regards,

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

Title:
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!
  • 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
  • Outside of code tags, you may need to use entities for some characters:
            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?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others chilling in the Monastery: (8)
    As of 2014-07-30 23:35 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      My favorite superfluous repetitious redundant duplicative phrase is:









      Results (241 votes), past polls