Export from Access to Excel Problem

Posted by Anthony Cook on February 13, 2002 3:54 AM

I am exporting queries from Access to Excel using an Access Macro action "TransferSpreadsheet". The data is transferring OK but the formula/lookup tables etc. in Excel are not recognising the new data exported from Access. How do you re-calculate the Excel workbook so that formula/lookup tables etc. recognise the new data exported from Access? (Auto/Manual Recalc doesn't help). Any suggestions would be welcomed.

Posted by DK on February 13, 2002 4:10 AM

You have to ensure that the format of the lookup values in the lookup range are the same as the data exported from Access. E.g. if your Access export contains a text field which is what you're looking up, then the data in the other side of the formula should also be text. The easiest way to ensure this is to put an apostrophe in front of all of the entries in the worksheet i.e. the one that doesn't come from Access.

If this isn't clear, let me know,

Posted by Anthony Cook on February 13, 2002 5:07 AM

Thanks DK. The formats are the same. After exporting from Access, if I go into Excel and double click on a cell with a formula referencing the fields exported from Access then hit enter, the formula then recognises the new data and returns the new correct result. The formula has not been changed in any way but it then works properly reading the new data. I hope this makes sense??

Posted by Barrie Davidson on February 13, 2002 6:14 AM

Anthony, it sounds like the query's output in that field is a text value. You can force it to be a numeric value by using a conversion formula in Access. Something like changing your field from:

Field1:[Table].[Field]

to:

Field1:cdbl([Table].[Field])

Hope this makes sense to you. If not, just let me know, I'll be happy to help out further.

Regards,
Barrie

Barrie Davidson

Posted by Anthony Cook on February 13, 2002 9:06 AM

Barrie/DK

Thanks for your help. I have changed formats on the table the queries are running from in Access and they are downloading to Excel correctly however the formula's and VLOOKUPS are still not reading the new data, but instead returning results from previous data. If I go into individual cells with formula or VLOOKUPS and hit F2 then enter (not changing anything but effectively just prompting it calculate) the formula or VLOOKUP is working correctly This indicates to me the formats are OK but the formula and VLOOKUPS won't read the new data when loaded by an Export from Access???

Posted by Barrie Davidson on February 13, 2002 9:16 AM

Anthony, the formats are definitely not okay. Try this little test (assumes that your Access output is in Book2 and your vlookup table is in Book1 and both cells you are comparing are A1) on two cells that should be identical:

Somewhere in Book2 put =ISTEXT(A1)
Somewhere in Book1 put =ISTEXT(A1)

This is testing to see if the cell is a text value. Both formulas should produce the same results (True or False). I'm guessing, based on my experience, that one will be True and one will be False.

Let me know the results. If you need any further clarification, just let me know.

Barrie

Barrie Davidson

Posted by Anthony Cook on February 14, 2002 1:29 AM

To Barrie Davidson - Access/Excel Problem

Hi Barrie

Have tried =istext(a1) etc. and they do return the same i.e. false as they are numeric fields. The Access export is into different worksheets in the same workbook and the vlookups are looking from one worksheet to another. The formula are on the same worksheet as the exported data is exported to. The formula and lookups just seem to be ignoring there has been a change in data the formula refer to???