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,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,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!
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,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,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!