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.
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.
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.TransferSpread sheet acImport, acSpreadsheetTypeExcel12, "tblCashPro", gSelectedFile, False, "A1:T1000"
DoCmd.OpenQuery "qryAppendBankTranasctions ToExportal l", acViewNormal
Me.Phase = "Complete"
DoCmd.SetWarnings True
GoTo SkipError
ErrorPoint:
Call ErrorHandler("frmImportDet ailTransac tions", "Import Detailed Transactions", Err.Number, Err.Description)
SkipError:
End Sub
========================== ====
The spreadsheet clearly had the entire text in it.
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.TransferSpread
DoCmd.OpenQuery "qryAppendBankTranasctions
Me.Phase = "Complete"
DoCmd.SetWarnings True
GoTo SkipError
ErrorPoint:
Call ErrorHandler("frmImportDet
SkipError:
End Sub
==========================
The spreadsheet clearly had the entire text in it.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
More testing. I converted the excel to a CSV and a TXT and i get the same results.
ASKER
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.
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