Results 1 to 5 of 5

Thread: Appending to an existing Excel spreadsheet, without Office or Excel installed

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2015
    Posts
    38

    Appending to an existing Excel spreadsheet, without Office or Excel installed

    Hi,

    I'm using the Excel.Applications object in VBS, and everything is working perfectly. But I discovered that the server we will put the script on doesn't have Excel, and we're not allowed to install it there.

    Although the script builds an Excel spreadsheet from scratch, we don't actually need that full functionality. We can use an existing spreadsheet with only the field headings and the columns already formatted, and set to Freeze top row, greenbar conditional highlighting, etc.

    So we would just need the function of appending to that existing spreadsheet. We can easily do any sorting & filtering of the data prior to importing.

    There are Excel generation libraries out there, but all of the demos that use them are in .Net, usually C#. There don't seem to be any VBScript or VBA samples of using an Excel library that doesn't require Excel.

    My question is, how can we append data to an existing Excel Spreadsheet, via VBScript, without Office installed?

    Regarding the data itself, it's in Tab-Delimited format, but we can transform that programmatically to CSV or Sqlite or any other format prior to appending to Excel.

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Appending to an existing Excel spreadsheet, without Office or Excel installed

    In the past (and presumably is still the case, but I don't have the most current Office to reference), the "Microsoft Excel ##.0 Object Library" that you would be using a reference to points to the Excel.exe file directly. So, without Excel installed, there is no "Excel.Applications" object on the server to make use of.

    Not saying its a show-stopper, there are likely countless ways of getting this done (some more convoluted than others), but as it stands, you'll likely need an additional layer of code somewhere to get data from server A into spreadsheet B.

  3. #3

    Thread Starter
    Member
    Join Date
    Dec 2015
    Posts
    38

    Re: Appending to an existing Excel spreadsheet, without Office or Excel installed

    Quote Originally Posted by OptionBase1 View Post
    In the past (and presumably is still the case, but I don't have the most current Office to reference), the "Microsoft Excel ##.0 Object Library" that you would be using a reference to points to the Excel.exe file directly. So, without Excel installed, there is no "Excel.Applications" object on the server to make use of.

    Not saying its a show-stopper, there are likely countless ways of getting this done (some more convoluted than others), but as it stands, you'll likely need an additional layer of code somewhere to get data from server A into spreadsheet B.
    Thanks, Option. I just searched through the Harbour MiniGui distribution, and there's a library there c:\minigui\SAMPLES\Advanced\HbXlsxWriter\libxlsxwriter.dll that seems to have the needed functionality.

    It can definitely generate an excel file from scratch ; I just tested a utility that I wrote last year, that saves current directory information to an Excel file. Even on that server without Excel, that code works fine.

    What I don't know, is whether it can take an existing spreadsheet that contains all sorts of formatting features, and add to it.

    Will check it out over the weekend, and report back here.

    It wouldn't all be in VBSCript, but SHELLing from VBS and doing the Import in Harbour would be fine.

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Appending to an existing Excel spreadsheet, without Office or Excel installed

    https://github.com/wqweto/Biff12Writer

    This can produce XLSB files from scratch including multiple sheets, text formatting, numeric/text values (no formulas yet) and pictures.

    Unfortunately it cannot round-trip a template file while retaining all of formatting and modifying content on placeholders only but this would be a nice use-case for the library to handle.

    cheers,
    </wqw>

  5. #5

    Thread Starter
    Member
    Join Date
    Dec 2015
    Posts
    38

    Re: Appending to an existing Excel spreadsheet, without Office or Excel installed

    Quote Originally Posted by wqweto View Post
    https://github.com/wqweto/Biff12Writer

    This can produce XLSB files from scratch including multiple sheets, text formatting, numeric/text values (no formulas yet) and pictures.

    Unfortunately it cannot round-trip a template file while retaining all of formatting and modifying content on placeholders only but this would be a nice use-case for the library to handle.

    cheers,
    </wqw>
    Thanks wqweto, I didn't know Excel had a format with this extension.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width