Importing Excel into Memo Fields

[Joker via AccessMonster.com]

Hello,

I have an excel file with large cell amounts (over 5000 charicters) that I'm
trying to import into Access. I have a table set up with memo fields so I
believe it should fit in it but I'm getting an error "An error occurred
trying to import file 'C:\Desktop\Book3.xls'. The file was not imported."
I've tried changing file types to CSV and Text to no avail. I've tried
chaning the location of the file to my local hard drive still no luck. I've
tried a few different methods of importing (manual wizard,
transferspreadsheet) still recieving the same error. I've tried just
importing it into a new table, I've tried copying and pasting the values of
the file into a new spreadsheet, still a no go. I'm out of ideas, I would
appreciate any help I can get. Thank you.

 

[Guest]

I believe that only 255 characters can by imported from Excel to Access. One
thing that you can do is convert your excel spreadsheet to have multiple
fields each with 255 characters. Then import them into a table and run a
query to combine them all into one field. This seems like a pain, but may be
your only workaround.

Please let me know if I can provide more assistance.

 

[Ken Snell \(MVP\)]

I believe that only 255 characters can by imported from Excel to Access.

No, if you use TransferSpreadsheet (macro or VBA), you can import text
strings longer than 255 characters. However, if you use File | Get External
Data ... | Import method, then the text strings will be truncated at 255
characters.

 

[Guest]

Hi,

I just finished troubleshooting the same error but a slightly different
scenario (not nearly as much data in the cells) so here are a couple more
ideas to try. What I ended up doing was linking the table rather than
importing then creating a select query, running it to make sure data comes in
ok, then changed the select query to a make table query. I think,
ultimately, there was an issue with one of the column headers. I ended up
retyping the headers and then was able to complete the above method of
getting the data into the databse. Haven't yet tested trying to import the
file now that is has new column headers. Hope this helps!

 

[Joker via AccessMonster.com]

Thanks everyone for your input. I ended up having to import it via coding
with recordsets. Below is how I got it imported.

Option Compare Database
Public Sub ReadExcel()
'==================================================================
' Dim Statements
'==================================================================
Dim adoRecordsetObject
Dim adoObject
Dim xlApp
Dim xlWorkbook
Dim xlSheet
Dim FileLocation
Dim readXlSheet(1 To 598, 1 To 99)

FileLocation = "C:\temp\FileName.xls"

'==================================================================
'Open the Excel Sheet
'==================================================================
Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Open(FileLocation)
Set xlSheet = xlWorkbook.Sheets(1)
'xlSheet.Cells(1, 1).Select
'==================================================================
' Read the Excel sheet from sheet 1 into an Array
'==================================================================
For X = 1 To 598
For Y = 1 To 99
readXlSheet(X, Y) = xlSheet.Cells(X, Y)
Next Y
Next X
xlWorkbook.Close
'=================================================================
'Open The Wires Table and fill table with array data
'=================================================================
Set adoObject = CurrentProject.AccessConnection
Set adoRecordsetObject = CreateObject("ADODB.RecordSet")
adoRecordsetObject.Open "[Table1]", adoObject, adOpenKeyset, adLockOptimistic,
adCmdTable


For X = 0 To 597
adoRecordsetObject.AddNew
For Y = 0 To 98
adoRecordsetObject.Fields(Y).Value = readXlSheet(X + 1, Y + 1)
Next Y
adoRecordsetObject.Update
Next X

End Sub