Any way to make OleDb read an Excel file?

[Norman Diamond]

I think the reduce of file size is possibly because we do not copy the Row

Height, Column Width, Page Setup info to the result xls. These choices are
optional to be copied in the CLEANER.XLA.

I did set those options, and only unset the option to copy VBA components.
So I think the reduction of file size was due to some other reason(s). We
found one reason.

It was mentioned that, in the original xls, some little triangles in the
worksheet cells do not show until users click and leave the cells
manually. Would you help to check if the these little triangles show in
the recovered xls generated by CLEANER.XLA?

The recovered xls file has none of those little triangles. In Excel 2003 I
can double-click and leave those cells manually and then Excel 2003 brings
back those little triangles. CLEANER.XLA did the exact opposite, it removed
those little triangles even from cells that used to have them.

I am sorry for the worse results. I hope you have set 'IMEX=1' in the
OLEDB connection string when you do the test on the recovered xls.

Sorry I forgot to try 'IMEX=1' with the recovered xls. I will try again
later.

If the data in the xls is confidential, is it possible for you to produce
a reproducible xls from the old one, changing all its confidential texts
to non-sense value like "aaa"?

I already tried to do that, using Excel 97 to create a new spreadsheet. But
the new spreadsheet worked. The same as your experiment with Excel 2000, we
could not produce a repro.

In addition, would you let me know you email

I can but it will not speed up our communication in this case. My MSDN
membership is personally purchased and connected to a personal e-mail
address.

 

[Norman Diamond]

Correcting my previous posting in several ways. Sorry for my mistakes
around 20 minutes ago.

I think the reduce of file size is possibly because we do not copy the Row
Height, Column Width, Page Setup info to the result xls. These choices are
optional to be copied in the CLEANER.XLA.

I did set those options, and only unset the option to copy VBA components.
But you are right, row heights and column widths and font sizes were not
copied.

It was mentioned that, in the original xls, some little triangles in the
worksheet cells do not show until users click and leave the cells
manually. Would you help to check if the these little triangles show in
the recovered xls generated by CLEANER.XLA?

The recovered xls file has none of those little triangles. In Excel 2003 I
can double-click and leave those cells manually and then Excel 2003 brings
back those little triangles. CLEANER.XLA did the exact opposite, it removed
those little triangles even from cells that used to have them.

I am sorry for the worse results. I hope you have set 'IMEX=1' in the
OLEDB connection string when you do the test on the recovered xls.

Sorry, in my first test on the recovered xls, I forgot to try 'IMEX=1'.
This time I tried it with 'IMEX=1'. This time it recognized string values
in every cell of the second row of every worksheet. However, in some later
rows it had the same problem that 'IMEX=1' had before. This time I saw the
problem in row 61 of one worksheet, reading DBNull instead of the actual
string in the worksheet. The program and the user are not even aware that
row 61 was read incorrectly.

If the data in the xls is confidential, is it possible for you to produce
a reproducible xls from the old one, changing all its confidential texts
to non-sense value like "aaa"?

I already tried to do that, using Excel 2000 to create a new spreadsheet.
But the new spreadsheet worked. The same as your experiment with Excel 97,
we could not produce a repro.

In addition, would you let me know you email

I can but it will not speed up our communication in this case. My MSDN
membership is personally purchased and connected to a personal e-mail
address.

 

[Jialiang Ge [MSFT]]

Hello,

Thank you for the information. Because it is still not reproducible on my
side, I am not able to add more comments for you. Would you please let me
know if the issue (use IMEX=1) is critical to your business? If that, I
hope you could send me an email so that we can discuss other support
options.

Thanks
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Norman Diamond]

I had better not use IMEX=1 because it prevents detection of misread files.
With IMEX=1, row 2 reads correctly but row 61 reads incorrectly, and in row
61 I cannot detect that it read incorrectly. I can only detect problems in
row 2 because I know the expected constant strings for all cells in that
row.

When I detect a problem in row 2, I pop up a message to the user. It will
not be fun for the user to contact their designer and adjust the Excel
spreadsheet, but at least they will be informed of the need.

 

[Jialiang Ge [MSFT]]

Hello,

I had better not use IMEX=1 because it prevents detection of misread
files. With IMEX=1, row 2 reads correctly but row 61 reads incorrectly,
and in row 61 I cannot detect that it read incorrectly. I can only detect
problems in row 2 because I know the expected constant strings for
all cells in that row.

Yes, this is a better solution for the current situation. Again, if you
encounter any other problem, please feel free to let us know. We will do
our best to help you.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.