access bug importing from excel

[Guest]

I'm using Basic to import a simple one row table from Excel into Access.
Access does fine with non-calculated Excel cells, but seems to want to
memorize the values in calculated fields and use them ever after. The first
time I import or link a new sheet everything is fine, but after that Access
always uses the original values in the calculated cells. I can delete the
linked or imported tables and re-do them; I can delete them, close Access,
open Excel to verify different values, open Access again, re-link or
re-import - nothing works. Access insists on using the originally imported
values for the calculated Excel cells. Is there some way to force Access to
take a fresh look at the calculated cells and report the values currently
shown in Excel?

 

[Guest]

I would suggest first saving the Excel worksheet as a .csv file and then
importing the .csv file. The data will be saved in the .csv 'as is', instead
of the formulas being saved.

G

 

[Guest]

This might be a multiple post - my browser did something odd...

Anyway - try first saving the worksheet as a .csv file, then importing that
..csv file into Access. The .csv file will save the values in the cells, not
the formulae.

G

 

[Guest]

If you are importing the data, I would expect the data in the Access table
would not change when the source (the Excel file) changes. If you link to
the source file, I would expect the data in the Access table to change when
the source file changes. I just tested this and Access worked as above.

 

[Guest]

That might be an option if I could open the .xls and save it as .csv with
Basic from inside Access - but I'm guessing that's not possible?

 

[Guest]

Hmmm, that's not the results I get. I'm using Access 2003 and an Excel 2000
spreadsheet. Not only will the calculated fields in linked tables not update
when the Excel sheets change, but Access memorizes the values in the calc
fields. I can delete the linked or imported tables, close Access, open up
again and relink or reimport, and Access plugs in its memorized values to the
calculated fields. The non-calculated fields update just fine.

I can even rename the spreadsheet and relink its table - Access somehow
knows it's seen this sheet before, and plugs its memorized values into the
calc fields. The only thing that will cause it to take a fresh look is if I
change anything structural in the spreadsheet, even in worksheets I'm not
looking at. If I add a value or a calculation anywhere in the spreadsheet
Access will look again the next time it is asked to link or import, and then
gives the proper updated values.

I've simplified a spreadsheet to three cells a * b = c, and a database that
does nothing but link to those three cells; same results. A and b are fine,
but Access always plugs in a memorized value to c, the value that was there
when Access first linked or imported the sheet.