Error Importing From Excel

[bkclaw113]

When importing a workbook from excel I am getting error 3190:"Too many Fields Defined"

I have 14 workbooks that all came from the same parent workbook. Each data segment was then broken out into its own spreadsheet and sent out to business areas for review and input. Now that I am recieving them back I want to import all of the data back into access for more manipulaiton. The problem is that only about half of the workbooks make it through my import function. The other half result in the error listed above.

All workbooks started off with the same columns, and appear to still have the same columns, so why in the world would some of them import corectly and some not?

 

[vbajock]

An Access table is limited to 255 Columns. If you've got any more than that, you've got to break up your data somehow.

 

[bkclaw113]

Excel, where the data is coming, from also has that limitation, so I do not see how that can be the source of my problems.

 

[vbajock]

Post your function

 

[bkclaw113]

Private Sub cbttnProcess_Click()
On Error GoTo cbttnProcess_Error
Dim i As Integer
DoCmd.SetWarnings (False)
For i = 0 To Me.lboxWorkbooks.ListCount - 1
Debug.Print Me.lboxWorkbooks.ItemData(i)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Market Spreadsheet Input", Me.lboxWorkbooks.ItemData(i), False
DoCmd.OpenQuery "qry_AppendToMasterAllocation"
DoCmd.OpenQuery "qry_DeleteAllFromMarketSpreadsheet"
NextFile:
Next i
DoCmd.SetWarnings (True)
cbttnProcess_Exit:
Exit Sub
cbttnProcess_Error:
If Err.Number = 3190 Then
Debug.Print "Not Processed " & Me.lboxWorkbooks.ItemData(i)
Resume NextFile
End If
MsgBox Err.Description
GoTo cbttnProcess_Exit
End Sub

 

[vbajock]

What line returns the error?

 

[bkclaw113]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Market Spreadsheet Input", Me.lboxWorkbooks.ItemData(i), False

 

[vbajock]

Looks pretty simple:
'use a list box on the screen to get the list of workbooks

For i = 0 To Me.lboxWorkbooks.ListCount - 1

Debug.Print Me.lboxWorkbooks.ItemData(i)

'syntax looks right
'fill a buffer table
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "Market Spreadsheet Input", Me.lboxWorkbooks.ItemData(i), False

'expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, 'HasFieldNames, Range, UseOA)

'plug the data into a pre-defined table
DoCmd.OpenQuery "qry_AppendToMasterAllocation"
'blow away the buffer
DoCmd.OpenQuery "qry_DeleteAllFromMarketSpreadsheet"

'==> might try a Doevents here


'don't know why this is here
NextFile:
Next i

Looks like you might have found a genuine Access bug. Code should work. You might try recoding as a Do Loop.

 

[vbajock]

Also,
Dim iWbookCount as Integer

Replace Me.lboxWorkbooks.ListCount with the variable