Transferspreadsheet Issue with Data Types

[Sash]

I'm trying to transfer a spreadsheet through VBA code and it's dropping some
data. The column is PatientNum. This column may contain 123456,
A324940....in other words, all numbers or letters and numbers; therefore, I
made the table field a text field. However, it's still dropping some data.
Any ideas?

 

[bhicks11 via AccessMonster.com]

Have you tried putting a letter in front of the first column that contains
the numbers. It will default to text and then you can remove it after import.


Bonnie
http://www.dataplus-svc.com

 

[golfinray]

Have you tried changing the filed type to text in Excel first and then
transfering? Are you using something like:
Docmd.transferspreadsheet,, "your spreadsheet name","Your spreadsheet
location",true, range if needed?

 

[Sash]

Milton,
This is exactly how I'm doing the transfer. I can try to change the column
to text. I was hoping to have little to no interaction required by the user.
Now, they basically complete a spreadsheet, open my application and push a
button to generated a formatted file to be imported into a collection system.
Looks like I'll need to find another way tomorrow.
Thanks,
Robin

 

[Piet Linden]

Milton,
This is exactly how I'm doing the transfer.  I can try to change the column
to text.  I was hoping to have little to no interaction required by theuser.
 Now, they basically complete a spreadsheet, open my application and push a
button to generated a formatted file to be imported into a collection system.
 Looks like I'll need to find another way tomorrow.
Thanks,
Robin

If the columns remain constant, why not just create an import
specification and then just use it... Then Access doesn't ever guess
what kind of field something is, because you already gave that
information in the spec...

 

[Sash]

I didn't think you could create specs for spreadsheets. Whenever I manually
import a spreadsheet, it never gives me the "Advanced" button where you
create specs. I'd love to know how to do this for spreadsheets. Can you
give me the steps or some guideance here?

 

[Ken Snell MVP]

Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr

 

[bhicks11 via AccessMonster.com]

I think Ken's option of importing directly into a pre-formatted table in this
link is what you need here.

Bonnie
http://www.dataplus-svc.com

Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr

I'm trying to transfer a spreadsheet through VBA code and it's dropping
some

[quoted text clipped - 4 lines]

data.
Any ideas?