Import of Database VB app into Excel Spreadsheet
 

Original Question:

We have a Database program here (SQL not Access) that
exports reports to Excel. These reports contain odd
combinations of numbers that mean something to the users
of the database and to the programmers of it, but not to
me. Anyway, I recently upgraded from Office 2000 on our
terminal server (Win2K) to Office XP. Since then the
num,bers in this worksheet are being truncated and the
formats are changing. For instance, a number such as
2E152 appears as 2.00E+152 or something like 0100a will
appear as 1:00 AM, we are also having the leading 0's
for a number deleted. I know how I can change the format
for each cell individually, but how can I change the
formatting for cells in the entire spreadhseet to read
the numbers as text only. Or some other fix would be
great. I have Office XP on a nother server and this export
isn't goofed like this. Don't understand it and have too
much to do to spend more time on it so any help would be
awesome! Thanks!

Answer #1
It would depend on what you mean by export. If it is
exported as a text file (but not .csv), then imported, you
can import it manually through file=Open which will
invoke the text import wizard. On the last dialog of
the wizard, you can specify to treat specific columns as
text and no conversion will take place. Do this with the
macro recorder turned on to get the code to import it
without going through the text import wizard, but
still use the settings you specified.

Other than that, I think you would have to elaborate on
the format of the file that is exported.

Excel is seeing 2E152 as being a number in scientific or
exponential notation. This is pretty standard, so I am
not sure why earlier versions were not having a similar
problem. Same with 0100a being seen as a time
value. Leading zeros are lost when the value is stored
as a number again, this is pretty common in all versions
as well. For some reason, the machines without the
problems must have treated these columns as Text.


Regards,
Tom Ogilvy


Reply to answer:
The export is happening inside a VB6.0 Application.

The excel object is opened and a worksheet created.

We fill the cells using the .copyfromrecordset method of
the cell object within the excel object.

The load point for Office XP for the working system is
different from the load point for the non-working system.
Maybe some pre install features were configured
differently, not sure. I was just wondering if there may
be some registry changes I could make so the default cell
category is text.

Thanks,
Donna