TransferSpreadsheet for import, Error 3011

[itchyII]

Hello All,
This is my first time on this forum. I have a function that I use to import data from Excel spreadsheets. I have used this code before without problems. The only thing different with this import is that the data I want to import is in non adjacent columns. Here is the code:

Function importMatspc(UploadFile As String) As Boolean
'----------------------------------------------------------------------------
'
' Function: importMatspc
'
' Description: Imports columns from the Matspc Excel file
'
' Arguments: UploadFile - the file selected for upload (should be the matspc
' file updated daily)
'
' Returns: True if successful
' False if failed
'
'-----------------------------------------------------------------------------

Dim filename As String
Dim fs
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim flag As Boolean

flag = False

Set fs = CreateObject("Scripting.FileSystemObject")

filename = UploadFile

If Not fs.FileExists(filename) Then
GoTo noFile
Else
'Purge Results table
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "DELETE * FROM Results"
cmd.Execute

'load data from excel file
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Results", filename, vbYes, "Results!B:B,L:L,P:P,R:R,AA:AA"

GoTo fileImported
End If


noFile:
flag = False
GoTo cleanup

fileImported:
flag = True

cleanup:
Set fs = Nothing
Set cnn = Nothing
Set cmd = Nothing
importMatspc = flag

End Function

and here is the error I am getting:

"Run-time error '3011'

The Microsoft Jet Database engine could not find the object 'Results$B:B,L:L,P:P,R:R,AA:AA'. Make sure the object exists and that you spell its name and the path name correctly."


First I thought it might be something with the file name, so I shortened it, but it still didn't work. Then I tried naming the range in Excel and specifying the named range in my transferspreadsheet command, but it gave me the same error. Can anyone see what is going on? The spreadsheet contains about 55000 rows, could that be a problem?

Any help is appreciated!

Thanks!

[Timtropolis]

Hi Itchy,

Shot in the dark here, but what version of office are you using? If its 2003,
try changing your acSpreadsheetTypeExcel8 ref in the transferspreadsheet to acSpreadsheetTypeExcel9. I had an issue a while back with row counts in office2003 due to acSpreadsheetTypeExcel defaulting to the wrong version and making this change fixed it.

HTH,
Tim

[boblarson]

Excel Version 9 - Office 2000
Excel Version 10 - Office 2002 (XP)
Excel Version 11 - Office 2003

[itchyII]

I am using 2003, will try changing the reference. In the mean time, I found a work-around using another data source...