-
Aug 6th, 2021, 01:40 PM
#1
Thread Starter
Member
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.
-
Aug 6th, 2021, 02:19 PM
#2
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.
-
Aug 6th, 2021, 02:53 PM
#3
Thread Starter
Member
Re: Appending to an existing Excel spreadsheet, without Office or Excel installed
Originally Posted by OptionBase1
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.
-
Aug 6th, 2021, 03:49 PM
#4
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>
-
Aug 6th, 2021, 04:20 PM
#5
Thread Starter
Member
Re: Appending to an existing Excel spreadsheet, without Office or Excel installed
Originally Posted by wqweto
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|