Error 2391

C

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
 
K

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.
 
C

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."
 
K

Klatuu

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

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top