[RESOLVED] Export data (dbGrid or Flexgrid) to .xls format - NO EXCEL INSTALLED

[Dave Sell]

All,

Is there some tool out there with which to export a dbGrid or an ADO recordset to .xls format? An .OCX will be fine, but there is no MS Excel installed on the client's machine.

Thanks!

Dave

[dglienna]

I doubt it. Can't you export to text, and then import into Excel?

[RobDog888]

Do you have Crystal Reports available?

[Dave Sell]

Yes, I could do text but the client is specifically asking for my OPC utility to export logs and import projects as .xls (Excel) spreadsheets. No special software is installed, its straight WinXP Pro and my utilities.

Any .DLL or .OCX or .BAS or whatever will do, I just wanna output some stuff as .xls format. Gotta love those "client requests"...

[dglienna]

Write a macro to import the data, and he'll have an Excel file.

[Dave Sell]

Tis not enough as I also need to import .xls into the app. How do you save an Excel macro in a csv file such that Excel will execute it? Or is the macro saved into his Excel app?

[dglienna]

Give him an Excel workbook that has an autoexec macro that imports a file and saves it in XLS format. That would be simple. You could either use the txt file, or the saved workbook to input into your app. Why bother, though. You'd still have to load it twice, and convert it twice. Just read it once, and save the xls file, and then use the array in your app.

[birthjay]

Can you use OpenOffice or StarOffice? They can save as .xls. Just throwing it out there in case it might spur and idea.

[RobDog888]

Two possibilities:

1.) Use a CR report hidden that when they want to save to an xls file it will actually be Exporting using CR's Export feature to actual xls format. Only the dependent dlls from certain CR files are necessary on the client system.

2.) Export out in a textfile format but as a comma sepparated values format and file extension. Then when the user opens the csv by double-clicking the file it will open correctly formatted in Excel. Then they can save as xls if they choose to. At least the file will be compatible with Excel without having Excel on the system.

HTH

[dglienna]

Why would a client want an XLS format if he doesn't have Excel?

[RobDog888]

Well thats what Dave is asking for on behalf of his client. Maybe the client wants to email the workbooks to someone that has Excel.

[Dave Sell]

My app is server-side.

He wants to upload data as .XLS and download output from the server-side utility as .XLS format.

The server has no Office software, his laptop does.

[Dave Sell]

Two possibilities:
1.) Use a CR report hidden that when they want to save to an xls file it will actually be Exporting using CR's Export feature to actual xls format. Only the dependent dlls from certain CR files are necessary on the client system.

That software is not available to me.

2.) Export out in a textfile format but as a comma sepparated values format and file extension. Then when the user opens the csv by double-clicking the file it will open correctly formatted in Excel. Then they can save as xls if they choose to. At least the file will be compatible with Excel without having Excel on the system.

HTH

No good. Again, this has to be .xls in, and .xls out. I cannot use regular csv or t-n-t text files. Is there no such exporting utility out there?

[dglienna]

I think he may be out of luck.

[Dave Sell]

What about hooking ADO up to an ODBC Excel driver? I worked up a little code for a start, but my SELECTs and INSERTs dont work cause theres no table name to reference in an Excel file.

Private Sub Command1_Click()
    '
    Dim db As ADODB.Connection
    Set db = New ADODB.Connection
    '
    db.Open "DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=""c:\Temp\Book1.xls"";DBQ=c:\Temp\Book1.xls"
    '
    db.Execute ("SELECT * FROM Sheet1")
    '
End Sub

Any new ideas?

[Dave Sell]

This seems to work for an existing file:

Private Sub Command1_Click()
    '
    Dim db As ADODB.Connection
    Set db = New ADODB.Connection
    '
    db.Open "DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=""c:\Temp\Book1.xls"";DBQ=c:\Temp\Book1.xls"
    '
    Set rs = db.Execute("SELECT * FROM [sheet1$A1:B3]")
    '
End Sub

Now I need to get an INSERT INTO to work...

[Dave Sell]

This creates a new worksheet in an existing file:

Private Sub Command1_Click()
    '
    Dim db As ADODB.Connection
    Set db = New ADODB.Connection
    '
    db.Open "DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=""c:\Temp\Book1.xls"";DBQ=c:\Temp\Book1.xls"
    '
    db.Execute ("CREATE TABLE demo (Name TEXT,Age NUMBER)")
    '
End Sub

On to INSERT INTO...

[dglienna]

Doesn't that require Office? Can you distribute the files?

[Dave Sell]

I *think* this is built into any XP machine as a default ODBC driver. I am using my laptop right now which has Excel, but I have seen the .XLS ODBC driver on new machines with no software.

[Dave Sell]

This is the INSERT INTO on an existing file:

Private Sub Command1_Click()
    '
    Dim db As ADODB.Connection
    Dim rs As ADODB.Recordset
    '
    Set db = New ADODB.Connection
    '
    db.Open "DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=""c:\Temp\Book1.xls"";DBQ=c:\Temp\Book1.xls"
    '
    db.Execute ("INSERT INTO demo (Name,Age) VALUES ('Dave Sell',35)")
    '
    '
End Sub

On to non-existant files...

[Dave Sell]

Well, I compiled the simple EXE and executed it on a machine with Vis Stud 6 but not Excel of any kind (Windows 200 Term Server).

It created the .XLS from scratch and made my sheet and inserted data. I think I can manage the rest from here. It pays to refuse to give up.

Private Sub Command1_Click()
    '
    Dim db As ADODB.Connection
    Dim rs As ADODB.Recordset
    '
    Set db = New ADODB.Connection
    '
    db.Open "DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=""c:\Temp\Book1.xls"";DBQ=c:\Temp\Book1.xls"
    '
    db.Execute ("CREATE TABLE demo (Name TEXT,Age NUMBER)")
    db.Execute ("INSERT INTO demo (Name,Age) VALUES ('Dave Sell',35)")
    '
    'Set rs = db.Execute("SELECT * FROM [sheet1$A1:B3]")
    '
End Sub

[Dave Sell]

P.S. I just tried it on a clean Windows 2000 Workstation, no special software installed and it worked. I was surprised to see the ADO stuff work... whew!

[Dave Sell]

I just came across this link on MSDN describing more of the above. The DELETE statement is not supported for Excel Files.

http://support.microsoft.com/kb/257819

[growers]

Hi Dave, if you're still around thanks for the above, really helped me out!

[Dave Sell]

Ya, still around. Rate the post if it helped you. Did you notice the CREATE TABLE actually creates a new .XLS file on disk from scratch? I always that that was cool.

[growers]

I haven't needed to create a file from scratch (yet), but being able to insert into an existing file without having Excel installed is very useful. Previously I've only been able to read data from Excel via ODBC as the .additem method of a recordset throws an error.

[Dave Sell]

BTW I have used the above technique on CSV and Access Database files (MDB files) and they all work. The only notable exception is the driver won't CREATE a new .MDB file from scratch. You have to make one in MS Access first. Glad this helped you out!