Import Tables from Excel

[Beverly76]

I have about twenty data tables that I need from Excel. It seems like when I
either link or import, I get errors that I do not get by simply opening the
Excel file, copying the cells and pasting them as a new table in Access. Am
I missing something? Isn't Import supposed to do the same thing?

When I use linked tables, I get a overflow error in my queries and I think
that import is changing the first field name. I just want to avoid the
tedious copying and pasting.

Any ideas?

 

[Lord Kelvan]

what is hapening is that you most likly have mixed data types in your
columns access reads the first 8 rows of the column and then decides
the data type for the link table problem is if it is a text data type
it cannot link in numbers for some reason i had a problem where half
my data was missing because of mixed data types how i fixed it was i
created a macro and added a space infront of the mixed data type
column this in turn made them all text but you also have to make the
column format text as well.

http://support.microsoft.com/kb/815277/en-us

Sub Addspace()
Dim cell As Object
For Each cell In Selection
cell.Value = " " & cell.Value
'cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Next
End Sub

that procedure did it for me it is fromt hat site but i found i had to
remove the line i commented out because when i hid the space i still
got the error so to do what i wanted i had to leave the space

i hope this helps

regards
kelvan

 

[ryguy7272]

Access is very finicky about what kinds of data it permits (and you tell it
what to permit in the Design View > Data Type). Try to set everything in
Access to Data Type = 'Text'. That is the most forgiving data type in the
Access arsenal. Retry. It should work for you after you make the changes
that I mentioned above.

Regards,
Ryan--