Error 2391

[Cyberwolf]

This can get really frustrating. I have the following code. When I execute
case 1 it works fine, it deletes the old data and imports the new. If I try
to execute any of the other cases I get Error 2391 stating that a field does
not exist in the destination table. The field is diffrent for each case so I
am thinking it is picking up the excel spreadsheet from case 1 and trying to
import it into the other table.

the code is:

Private Sub butImpStoreInfo_Click()
'On Error GoTo butImpStoreInfo_Click_Err

Dim stTableName As String
Dim stDocName As String

Select Case optStoreImp

Case 1
stDocName = "G:\Accounting\AR\AR Database\walmart stores.xls"
DoCmd.OpenQuery "qry:WalMartStoreDel", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, , "WalmartStores", stDocName, True,
WalMartStores

Case 2
stDocName = "G:\Accounting\AR\AR Database\walmart stores.xls"
DoCmd.OpenQuery "qry:WalMartDCMailDel", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, , "WalmartDCMail", stDocName, True,
WalMartDCMailing

Case 3
stDocName = "G:\Accounting\AR\AR Database\walmart stores.xls"
DoCmd.OpenQuery "qry:WalMartDCRecDel", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, , "WalmartDCRec", stDocName, True,
WalMartDCReceiving

Case 4
stDocName = "G:\Accounting\AR\AR Database\walmart stores.xls"
DoCmd.OpenQuery "qry:SamsClubsDel", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, , "SamsClubs", stDocName, True, SamsClubs

Case 5
stDocName = "G:\Accounting\AR\AR Database\walmart stores.xls"
DoCmd.OpenQuery "qry:SamsDCRecDel", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, , "SamsDCRec", stDocName, True,
SamsDCReceiving

End Select



'butImpStoreInfo_Click_Exit:
' Exit Sub

'butImpStoreInfo_Crick_Err:
' MsgBox Err.Description
' Resume butImpStoreInfo_Click_Exit


End Sub

 

[Klatuu]

It looks like you are trying to import different worksheets from the same
workbook into different tables. That is okay. The problem is that the
worksheet name (range argument) needs to be in quotes. The reason case 1 is
working is that you are passing what Access sees as empty variables, so it is
defaulting to the first sheet in the workbook.

 

[Cyberwolf]

Now I am getting error 3011 "THe Microsoft Jet database engine could not find
the object 'WalMartStores'. Make sure that it exists and that you spell its
name and the path name correctly."

 

[Klatuu]

Is there a worksheet in the spreadsheet nameed WalMartStores? I don't see a
problem with the code.

 

[Cyberwolf]

That was my first thought. Maybe I had misspelled it. What I did was to
copy the worksheet name.

Is it possible to use the literal worksheet name "Sheet(1)" and so forth?

 

[Klatuu]

I think the syntax is either "Sheet1$" or "$Sheet1", but I am not sure which.