Link to home
Start Free TrialLog in
Avatar of ChuckRush
ChuckRush

asked on

Excel Import

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.
Avatar of mbizup
mbizup
Flag of Kazakhstan image

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
Avatar of ChuckRush
ChuckRush

ASKER

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.
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.
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
More testing. I converted the excel to a CSV and a TXT and i get the same results.
We posted close to the same time.

Just wanted to make sure you saw my comment at http:#a37604376.
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.