We help IT Professionals succeed at work.

Excel Import

ChuckRush
ChuckRush asked
on
645 Views
Last Modified: 2012-02-17
Experts

I have an import command that is truncating a memo field

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblCashPro", gSelectedFile, False, "A1:T1000"

tblCashPro is a table that matched the structure of the spreadsheet. The last field in the sheet is a memo field. I have defined it as such in the table and i have removed formatting restrictions (I looked at all of the other comments in the Experts DB). No matter what i do the memo field is truncated to 255.

I am out of options.

Thanks in advance.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try linking to your excel spreadsheet first:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "tblLinkToXL", gSelectedFile, False, "A1:T1000"


Does the linked spreadsheet (the linked table is called tblLinkToXL)  show the whole memo field?

If so, try running an Append query to get the linked spreadsheet data into tblCashPro

Author

Commented:
Thanks mbizup

When i use your suggestion, the linked table created shows a text field. So i get the same results
Here is my code:
=======================================

Private Sub ImportFile_Click()
On Error GoTo ErrorPoint
Dim wFileSelected As String
gSelectedFile = ""
Me.Phase = ""
Call SelectImportFile(0, 1, 0, "Select File to Import")
Me.SelectedFile = gSelectedFile
Me.Phase = "Importing Excel List"
DoCmd.SetWarnings False
If gSelectedFile = "" Then GoTo SkipError
Me.Phase = "Updating Detailed Transaction Table"
Me.Refresh
DoCmd.OpenQuery "qryDeleteCashPro", acViewNormal
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "ImportedBankData", gSelectedFile, False, "A1:T1000"
'Original command
''''''DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblCashPro", gSelectedFile, False, "A1:T1000"
DoCmd.OpenQuery "qryAppendBankTranasctionsToExportall", acViewNormal
Me.Phase = "Complete"
DoCmd.SetWarnings True
GoTo SkipError
ErrorPoint:
    Call ErrorHandler("frmImportDetailTransactions", "Import Detailed Transactions", Err.Number, Err.Description)
SkipError:
End Sub
==============================

The spreadsheet clearly had the entire text in it.

Author

Commented:
Further testing:

If i use the wizard to import the spreadsheet and specify that the field i need is memo, then it does come in. I am not sure how to specify columns in the transferspreadsheet function.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
More testing. I converted the excel to a CSV and a TXT and i get the same results.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
We posted close to the same time.

Just wanted to make sure you saw my comment at http:#a37604376.

Author

Commented:
Thanks so much this works great. BTW i tested the importspreadsheet on Access 2007 instead of 2010 and it works fine. Not sure what changed.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.