Import and append spreadsheet with multiple tabs with button

[socasteel21 via AccessMonster.com]

I have a spreadsheet that has 3 tabs each of the worksheets is setup exactly
like a cooresponding table in Access. I created a button that should import
each tab to a new table and then append those records in the new table to the
cooresponding table. Here is my code for the button:

----(start of code)-----------------------------------------------------------
-------------------------------------------------------------------

Private Sub cmdImportClaim_Click()
On Error GoTo Err_cmdImportClaim_Click
DoCmd.TransferSpreadsheet acImport, , "Import: tblClaim", "C:\Documents
and Settings\cau1042\Desktop\Database Backup\AUTAC Tables Backup\AUTAC
Warranty Claim Import.xls", -1, "tblClaim"

Dim stDocName As String

stDocName = "Append: tblClaim"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.TransferSpreadsheet acImport, , "Import: tblPartPerClaim", "C:\
Documents and Settings\cau1042\Desktop\Database Backup\AUTAC Tables Backup\
AUTAC Warranty Claim Import.xls", -1, "tblPartPerClaim"

Dim stDocNameB As String

stDocNameB = "Append: tblPartPerClaim"
DoCmd.OpenQuery stDocNameB, acNormal, acEdit


DoCmd.TransferSpreadsheet acImport, , "Import: tblJobCodesPerClaim", "C:\
Documents and Settings\cau1042\Desktop\Database Backup\AUTAC Tables Backup\
AUTAC Warranty Claim Import.xls", -1, "tblJobCodesPerClaim"

Dim stDocNameC As String

stDocNameC = "Append: tblJobCodesPerClaim"
DoCmd.OpenQuery stDocNameC, acNormal, acEdit

DoCmd.Close
DoCmd.OpenForm "frmClaim"
Exit_cmdImportClaim_Click:
Exit Sub

Err_cmdImportClaim_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdImportClaim_Click

End Sub
------------------------------------------------------------------------------
----------------------------------------------(end of code)
Each time I press the button it imports and appends the first tab, but it
will not do the same for the other 2 tabs.

The error message I get is some kind of error saying that "tblPartPerClaim"
not found. This tab is spelled correctly in my Excel spreadsheet and in the
database. I can't figure out why it can't see these other 2 tabs.
Any help would be greatly appreciated.

Shannan

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200606/1

[socasteel21 via AccessMonster.com]

Nevermind, I figured out my problem. I had to select the cells on the other
worksheet tabs and name each of those selections "tblClaim",
"tblPartPerClaim","tblJobCodesPerClaim". You can do this by clicking in the
name dialog box in Excel (upper left hand corner) and typing a name and
pressing return.

Shannan

socasteel21 wrote:

I have a spreadsheet that has 3 tabs each of the worksheets is setup exactly
like a cooresponding table in Access. I created a button that should import
each tab to a new table and then append those records in the new table to the
cooresponding table. Here is my code for the button:

----(start of code)-----------------------------------------------------------
-------------------------------------------------------------------

Private Sub cmdImportClaim_Click()
On Error GoTo Err_cmdImportClaim_Click

DoCmd.TransferSpreadsheet acImport, , "Import: tblClaim", "C:\Documents
and Settings\cau1042\Desktop\Database Backup\AUTAC Tables Backup\AUTAC
Warranty Claim Import.xls", -1, "tblClaim"

Dim stDocName As String

stDocName = "Append: tblClaim"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.TransferSpreadsheet acImport, , "Import: tblPartPerClaim", "C:\
Documents and Settings\cau1042\Desktop\Database Backup\AUTAC Tables Backup\
AUTAC Warranty Claim Import.xls", -1, "tblPartPerClaim"

Dim stDocNameB As String

stDocNameB = "Append: tblPartPerClaim"
DoCmd.OpenQuery stDocNameB, acNormal, acEdit


DoCmd.TransferSpreadsheet acImport, , "Import: tblJobCodesPerClaim", "C:\
Documents and Settings\cau1042\Desktop\Database Backup\AUTAC Tables Backup\
AUTAC Warranty Claim Import.xls", -1, "tblJobCodesPerClaim"

Dim stDocNameC As String

stDocNameC = "Append: tblJobCodesPerClaim"
DoCmd.OpenQuery stDocNameC, acNormal, acEdit

DoCmd.Close
DoCmd.OpenForm "frmClaim"
Exit_cmdImportClaim_Click:
Exit Sub

Err_cmdImportClaim_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdImportClaim_Click

End Sub
------------------------------------------------------------------------------
----------------------------------------------(end of code)

Each time I press the button it imports and appends the first tab, but it
will not do the same for the other 2 tabs.

The error message I get is some kind of error saying that "tblPartPerClaim"
not found. This tab is spelled correctly in my Excel spreadsheet and in the
database. I can't figure out why it can't see these other 2 tabs.

Any help would be greatly appreciated.

Shannan

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200606/1