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

Answer 1

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.

Answer 2

If you are willing to live with installing the .NET runtime you can wrap the library in option #2 with a .NET class exposed to COM. The class just passes calls to ExcelXMLwriter.

The alternative is to study the XML standard and write your own writer but that probably overkill for the project.

As for OpenOffice take a look here. Especially the VBA section of this page here. There appears to be a COM component you can use.

Answer 3

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.

Answer 4

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.

Answer 5

It is possible to create an Excel workbook on a machine without Excel installed** by using a Jet connection -- any Jet connection -- to executing a "make table query" SQL DML or CREATE TABLE SQL DLL where the table in question is an Excel one e.g. consider this VBA code:

Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat  

  ' Create a new Jet .mdb
  .Create _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & _
      Environ$("temp") & "\DropMe.mdb"
  ' Use .mdb's Jet connection to create an Excel table
  ' (will create a new workbook to contain it)
  .ActiveConnection.Execute _
     "CREATE TABLE" & _
     " [Excel 8.0;HDR=NO;Database=C:\db.xls;].[Sheet1]" & _
     " (col FLOAT);"
End With

Jet is a deprecated Windows component that is ever-present on pre-Vista Windows machines and remains a free download from MS via MDAC.

That said, "to print them through the [Excel] app" you clearly need the Excel app!

** I think it is possible: I can't find a machine without Excel on which to test!

Answer 6

Check this: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271572

Do the same and save the file with .xls extension instead.

Alternatively, you can create HTML Table in-momery content and write it into a file, with extension xls.

Answer 7

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.

Answer 8

Creating a SpreadsheetML file from VB6 should be very simple It's just a text file containing a particular flavor of xml. I belive you can get all the docs for SpreadsheetML and WordML from MSDN.

I currently produce around 60 different custom reports from a vb.net application using SpreadsheetML. There is at least one potential problem due to the changes in file extensions and document security implemented in Excel 2007. If you generate a SpreadsheetML based excel file and use the .xls extension it will open just fine in 2003. Excel 2007 will pop up a dialog informing you that the file extension does not match the file content. It will open and display the file correctly after the warning dialog. If you change the extension on the SpreadsheetML document to .xml, Excel 2007 will open it with no complaint. However your xml based spreadsheets may open in IE on machines running Excel 2003.

Answer 9

Can you post a link to the DLL you found?

Answer 10

I use NPOI It does not use excel library and gives output in excel 2003 format