TransferSpreadsheet Action in a Macro

[cterjesen]

I am using the TransferSpreadsheet Action in a macro to grab data from an Excel spreadsheet. It pulls in the numeric data fine, but the text data from Excel does not come over into the table that I'm putting the data in in Access.

Can anyone help?

Thanks!

 

[cstlaurent]

did you make sure that the field is not numeric in the table.

the only think I can thing wriht now with that info

 

[cterjesen]

Yes, the field in the table is text. Does it matter that where Access is grabbing the data from Excel all of the cells are links (not actual typed text)?

 

[SydneyGeek]

Hi, I'm wondering if the links are causing the problem, or alternatively Access is having trouble with the data types. ... Try a dummy run where you do this:
1. Try importing this into a dummy Access table -- let Access create a NEW table for you.

Does this help?

If not, modify the Excel data as follows and try again:
2. Copy the Excel data to a new workbook / sheet but use Paste Special to paste values and formats. Now import THIS dataset into a new table that Access creates.

If either of these methods helps, you can use the working table for further imports.

Denis

 

[cterjesen]

It looks like the problem was with the links in Excel. It was putting a zero in fields that were null. Access was not reading this information as text when it imported it.

I changed the formula to put the word "null" when the linked cell was null. Access was able to figure this out.

Thanks for your help.