Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re: XML and Databases and Excel, Oh My!

by davies (Vicar)
on May 12, 2010 at 17:08 UTC ( #839689=note: print w/ replies, xml ) Need Help??


in reply to XML and Databases and Excel, Oh My!

It may not be your preferred path, but Excel has a feature - Microsoft Query - that can read from and write to databases directly. On 2000-2003 it is accessed via Data | Import External Data. You can write the SQL directly, but Excel has a ghastly quirk. At about 240 (NOT 255 or anything sensible) characters, it truncates the SQL. The trick is that VBA demands the SQL to be an array of strings. This array can have as many elements as desired, but each element must be relatively short. I've been up to over 40K characters to prove a point. You may need to install Query on your target machines - it's on the Office CDs I've used, but frequently as an optional component, which means that lazy acceptance of Bill's defaults doesn't install it.

Regards,

John Davies


Comment on Re: XML and Databases and Excel, Oh My!
Re^2: XML and Databases and Excel, Oh My!
by jedikaiti (Friar) on May 12, 2010 at 17:55 UTC

    That might be an option, I'll have to check into it. The one likely issue is a matter of how the end user wants to deal with the data, but I bet I can fix that with a macro or something. Thanks!

    Kaiti
    Swiss Army Nerd

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://839689]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (14)
As of 2014-09-18 19:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (123 votes), past polls