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.


John Davies
  • Comment on Re: XML and Databases and Excel, Oh My!

Replies are listed 'Best First'.
Re^2: XML and Databases and Excel, Oh My!
by jedikaiti (Hermit) 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!

    Swiss Army Nerd