Opening Excel worksheet missing cell values

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

When I open a worksheet and display it in a grid some cells are empty that
are populated when opening it in Excel.

First I noticed that some of these cells had formulas so I tried saving the
xls as csv and opening it but then I get the missing ISAM error.

I then resaved it as a xls file. This got rid of the formulas but the cells
are still empty in the DataGrid yet populated in Excel.

Any ideas?
 
¤ When I open a worksheet and display it in a grid some cells are empty that
¤ are populated when opening it in Excel.
¤
¤ First I noticed that some of these cells had formulas so I tried saving the
¤ xls as csv and opening it but then I get the missing ISAM error.
¤
¤ I then resaved it as a xls file. This got rid of the formulas but the cells
¤ are still empty in the DataGrid yet populated in Excel.
¤
¤ Any ideas?
¤

Try specifying the IMEX argument in your connection string;

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;" & _
"Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Thanks. Tried that and it works for the most part but there are still some
cells without their values.

 
¤ Thanks. Tried that and it works for the most part but there are still some
¤ cells without their values.
¤

The only other thing I can think of is that these missing cells are deriving their values from
formulas (or indirectly from other cells), which of course would be executed via Excel and not data
access.

Have you verified that this is not the case with the cells that are returning null values?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
yes. I checked it. I even tried saving it as a text file and checking the
values to make sure they are still there.

So far it's not a problem because I don't need these values but I will in
the future.

Paul Clement said:
¤ Thanks. Tried that and it works for the most part but there are still some
¤ cells without their values.
¤

The only other thing I can think of is that these missing cells are deriving their values from
formulas (or indirectly from other cells), which of course would be
Click to expand...
executed via Excel and not data
Click to expand...
 
¤ yes. I checked it. I even tried saving it as a text file and checking the
¤ values to make sure they are still there.
¤
¤ So far it's not a problem because I don't need these values but I will in
¤ the future.

What kind of data is missing? Is it numeric? Alpha?

Is there anything distinctly different between the data that is missing and the data that is
present?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Found something else really weird.

It I open my excel file on one machine values that were % (when viewed in
excel) are in their decimal format but it I open the exact same file with
the exact same program on another machine the values display as percentages.

Why would this happen?

 
¤ Found something else really weird.
¤
¤ It I open my excel file on one machine values that were % (when viewed in
¤ excel) are in their decimal format but it I open the exact same file with
¤ the exact same program on another machine the values display as percentages.
¤
¤ Why would this happen?

I'm not an Excel expert but it sounds like a style is being applied to change the formatting. I
don't see anything in the Regional Options in Control Panel but you can check there as well.

You could post to one of the microsoft.public.excel newsgroups for an answer to this question.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
It must have something to do with the Jet config since the second machine
(the one displaying percents) doesn't have Excel installed on it.

I'll try and repost to both this group and excel.

Thanks,
Joe

 
Back
Top