• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2409
  • Last Modified:

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
0
mmorr
Asked:
mmorr
  • 3
  • 2
1 Solution
 
tbsgadiCommented:
Hi,
Take a look at
http://office.microsoft.com/en-us/assistance/HP010950951033.aspx
Troubleshooting -
A common import problem — truncated data

Good Luck
Gary
0
 
mmorrAuthor Commented:
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
0
 
tbsgadiCommented:
Hi Mike
Out of the 2 I'd go for the first method...
Here's another idea Add to the beginning of the memo field an extra string of 255 characters & then get rid of it after importing...

Gary
0
 
mmorrAuthor Commented:
Great, thanks Gary,

I went with your suggestion of adding the extra long string and it worked a treat.

Cheers,
Mike
0
 
tbsgadiCommented:
Glad to help :>
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now