TransferSpreadsheet Action in a Macro

cterjesen

Board Regular
Joined
Jun 23, 2003
Messages
119
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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

cstlaurent

Board Regular
Joined
Jan 14, 2005
Messages
182
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

Board Regular
Joined
Jun 23, 2003
Messages
119
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Board Regular
Joined
Jun 23, 2003
Messages
119
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.
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,020
Latest member
LongDoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top