mmorr
asked on
DoCmd.TransferSpreadsheet acImport and Excel field size >255
Hi There,
I've got an ACCESS (Xp) module that imports data from Excel spreadsheets using the following commmand line:
DoCmd.TransferSpreadsheet acImport, , "tmpTNZI", _
TheFile, False
where 'tmpTNZI' is the table name that the data from the Excel spreadsheet is written to and 'TheFile' is a string varaiable containing the path and file name of the source Excel spreadsheet.
The routine works fine, however one of the fields in the Excel spreadsheet sometimes has contents that are longer than 255 characters. I thought that setting the target field in table 'tmpTNZI' to type 'memo' would solve the problem but it hasn't. The contents of the EXcel cell get truncated at 255 characters when written to the ACCESS table. Any ideas on how to get round this one?
Cheers
M
I've got an ACCESS (Xp) module that imports data from Excel spreadsheets using the following commmand line:
DoCmd.TransferSpreadsheet acImport, , "tmpTNZI", _
TheFile, False
where 'tmpTNZI' is the table name that the data from the Excel spreadsheet is written to and 'TheFile' is a string varaiable containing the path and file name of the source Excel spreadsheet.
The routine works fine, however one of the fields in the Excel spreadsheet sometimes has contents that are longer than 255 characters. I thought that setting the target field in table 'tmpTNZI' to type 'memo' would solve the problem but it hasn't. The contents of the EXcel cell get truncated at 255 characters when written to the ACCESS table. Any ideas on how to get round this one?
Cheers
M
ASKER
Thanks Gary,
I didn't realise that ACCESS just looks at the first 25 lines to determine field characteristics. I guess I'll have to write some code to either open the file in Excel and sort it descending on the length of the problem field, or open the file in Excel and save it as a text file and using DoCmd.TransferText?
Cheers,
Mike
I didn't realise that ACCESS just looks at the first 25 lines to determine field characteristics. I guess I'll have to write some code to either open the file in Excel and sort it descending on the length of the problem field, or open the file in Excel and save it as a text file and using DoCmd.TransferText?
Cheers,
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great, thanks Gary,
I went with your suggestion of adding the extra long string and it worked a treat.
Cheers,
Mike
I went with your suggestion of adding the extra long string and it worked a treat.
Cheers,
Mike
Glad to help :>
Take a look at
http://office.microsoft.com/en-us/assistance/HP010950951033.aspx
Troubleshooting -
A common import problem — truncated data
Good Luck
Gary