I am currently responsible for an application that should export quite a
bit of data to lots of different files, preferably an older Excel-format
because we think this would be the format most of the recipients would
know what to do with (in other words, we don't trust the recipients to
be able to open a tab-separated file in Excel/Lotus123 or whatever).

I've tried to use Crystal Reports to do this, the version that comes
with Visual Basic 5.0 Enterprise Edition (currently running SP3), but I
can't get the formatting right. If I make all the fields in the report
narrow enough, the line up in adjacent columns, but this isn't very
readable. If I make the fields wider, CRW skips columns instead of
making them wider, with the result that there are one or more empty
columns in between my data.

I've also tried to use ODBC to do this, which is a little better but has
the drawback that the ODBC-driver for Excel has to be installed on all
my client machines separately (and they have a *lot* of computers here
(well, somewhere around 10-15.000)). Also this would include a lot of
file copying and stuff. I'd like to avoid this if at all possible.

Any suggestions on how I should go about doing this would be greatly
appreciated.

Thanks a lot,

Trond Solberg
Systems Developer

--
My opinions are my own, and do not reflect the views and/or policies of
my employer.

The Excel file formats are detailed in the MSDN Library, but I would
seriously consider using OLE Automation to put the data into Excel 97 and
then force Excel to export in one of it's older formats. You will probably
find that this is far less work than writing to a native Excel format
yourself.

Regards,
Simon Jones
PC Pro Magazine

There is a tool from Innovative Solutions & Technologies
(http://www.istonline.com/) called OpenExchange DLL that allows you to
create various files types from VB just like creating ASCII text files.  It
doesn't cost too much and would offer you or your end users a lot of
flexibility in choosing the file type you want to create.

HTH
-----
Earl Damron, MCSD, MVP

Thanks to Simon and Earl for their input.

I forgot to mention that my employer doesn't have Excel (!) Here, all the
office application are Lotus-products (mostly, with the exception of MS
Project). So I can't use OLE to write the Excel format through Excel97 (or
whatever).

However, I have made a small test-project where I try to create these files
through Lotus123 97, I just need to learn a little more about how to call
the methods in Lotus from VB (specifically, I can't yet select a range,
because of some strange (IMHO) syntax). My macro (script in Lotus lingo)
recorder gives me this code to do some stuff:

---code snippets start here---
'Folk4.TXT is a Tab-separated ASCII-file
'In Lotus 1-2-3: Open a file, the next code segment should be on one line
CurrentApplication.OpenDocument
"G:\PROSJEKT\ENERGI\EMSU\SOURCE\UtrTest\Folk4.TXT",,"Text
(TXT;PRN;CSV;DAT;OUT;ASC)",,False,True,True

'"Rewritten" in VB: Open a file, the next code segment should be on one line
(oLotus123 is an object which is instanced and this line works, too :))
oLotus123.application.opendocument
"G:\prosjekt\energi\emsu\source\utrtest\folk4.txt", "", "Text
(TXT;PRN;CSV;DAT;OUT;ASC)", "", False, True, True

'...do some stuff...

'In Lotus 1-2-3: Select a cell
[A:A1..A:F8192].Select

'"Rewritten" for VB, none of the next lines work
oLotus123.application.activedocument.currentsheet.[A:A1..A:F8192].Select
oLotus123.application.activedocument.[A:A1..A:F8192].Select
oLotus123.application.[A:A1..A:F8192].Select
oLotus123.[A:A1..A:F8192].Select
---code snippets end here

For now I'm stumped! We might just try out (and maybe buy) that DLL that
Earl mentioned in his posting.

Best regards, Trond Solberg