Re: Import and append spread*** with multiple tabs with button

Nevermind, I figured out my problem. I had to select the cells on the other
work*** 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.TransferSpread*** 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.TransferSpread*** 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.TransferSpread*** 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 spread*** 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/Forums.aspx/databases-ms-access/200606/1
.