How do you create an Excel worksheet through VB6 without the Excel Object?

Question

I was recently put in charge of updating a VB6 data collection app, to add the ability to generate Excel reports and print them through the app (both have to be done on same computer). Normally this wouldn't be an issue, I've generated Excel reports with VB6 before using the Excel Object.

So I went ahead and coded together the changes and presented them, and then I was told that the machine on which this program will be isntalled won't have a full copy of Excel on it, and I must come up with another solution.

I tried creating delimited files (comma seperated or text), but when these files are opened with excel they do not format nicely i.e. if one cell has 20 characters, half the chracters will be cut off due to the fixed cell size.

I have a couple more ideas:

1) I know openoffice has an api. Can this api be used to generate a properly formatted excel file? Is there a COM object I can use?

2)I found this tool: http://www.carlosag.net/Tools/ExcelXmlWriter/ However, it was written in VB.NET. Can I still use this tool in VB6?

I'm really stumped and don't know in which direction to head next. Anybody have any ideas regarding the utitiles above? Also, I am also open to any other suggestions/ better methods. Anything that would help me accomplish this task would be appreciated!

NOTE: The Excel version that will be used to view these reports is Excel 2007

Solution 1

I found a solution:

I found a DLL that can be used to create and print Excel Spreadsheets. I think it is the same as the XML writer but for VB6.

Solution 2

Let's take a look at these two requirements:

add the ability to generate Excel reports and print them through the app.

and

the machine on which this program will be isntalled[sic] won't have a full copy of Excel

Unless I misunderstand you, those seem to be mutually exclusive. If you mean you'll just be generating the reports on one machine and they'll be viewed and printed elsewhere, you might try using SpreadSheetML.

Be careful when googling for additional info on SpreadSheetML: there's a lot of misinformation out there that confuses SpreadSheetML with the new Xml format used for Excel in Office 2007. SpreadSheetML works as far back as OfficeXP, and even in a limited sense in Office 2000.

Solution 3

You can save excel files as html. In a web app I wrote that needed to export the data as excel, I just created an html file like the one excel exports. Then I served it as application/vnd.ms-excel. Excel opens it just fine. You could probably do something similar.

You can also find a library like the one you linked that writes the new office file format directly as I think they are derivatives of xml.

Solution 4

Create the CSV file as you would normally.

Let your desktop app do the formatting on the machine where XLS files are available, before it is printed.

View more solutions