I have Office 2003 and would like to export cell contents to an Access table
I've created using an Excel worksheet button.
The code below works when there are only 14 cells but bigger than that I get
"method range of object worksheet failed. Run-time error 1004."

arrrg.

Private Sub CommandButton5_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= C:\temp\QCRData
FY2010.mdb"

' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
' Strings

'works okay - SQLStr = "INSERT INTO [tblPage1] ([ReviewNo])Values('" &
Range("A45").Value & "')"
'works okay - SQLStr = "INSERT INTO [tblPage1]
([ReviewNo],[CaseNo],[StateCode])Values('" & Range("A45").Value & "','" & _
'Range("B45").Value & "','" & Range("C45").Value & "')"


SQLStr = "INSERT INTO [tblPage1] Values( '" & _
Range("A46").Value & "','" & Range("B46").Value & "','" & Range("C46").Value
& "','" & Range("D46").Value & "','" & Range("E46").Value & "','" & _
Range("F46").Value & "','" & Range("G46").Value & "','" & Range("H46").Value
& "','" & Range("I46").Value & "','" & Range("J46").Value & "','" & _
Range("K46").Value & "','" & Range("L46").Value & "','" & Range("M46").Value
& "','" & Range("N46").Value & "','" & Range("046").Value & "','" &
Range("P46").Value & "')"

'I broke it off here to see if it would work but need contents from all
these cells.
','" & Range("Q45").Value & "','" & Range("R45").Value & "','" &
Range("S45").Value & "','" & Range("T45").Value & "','" & _
Range("U45").Value & "','" & Range("V45").Value & "','" & Range("W45").Value
& "','" & Range("X45").Value & "','" & Range("Y45").Value & "','" &
Range("Z45").Value & "','" & _
Range("AA45").Value & "','" & Range("AB45").Value & "','" &
Range("AC45").Value & "','" & Range("AD45").Value & "','" &
Range("AE45").Value & "','" & _
Range("AF45").Value & "','" & Range("AG45").Value & "','" &
Range("AH45").Value & "','" & Range("AI45").Value & "','" &
Range("AJ45").Value & "','" & _
Range("AK45").Value & "','" & Range("AL45").Value & "','" &
Range("AM45").Value & "','" & Range("AN45").Value & "','" &
Range("A045").Value & "','" & _
Range("AP45").Value & "','" & Range("AQ45").Value & "','" &
Range("AR45").Value & "','" & Range("AS45").Value & "','" &
Range("AT45").Value & "','" & _
Range("AU45").Value & "','" & Range("AV45").Value & "','" &
Range("AW45").Value & "','" & Range("AX45").Value & "','" &
Range("AY45").Value & "','" & Range("AZ45").Value & "','" & _
Range("BA45").Value & "','" & Range("BB45").Value & "','" &
Range("BC45").Value & "','" & Range("BD45").Value & "','" &
Range("BE45").Value & "','" & _
Range("BF45").Value & "','" & Range("BG45").Value & "','" &
Range("BH45").Value & "','" & Range("BI45").Value & "','" &
Range("BJ45").Value & "','" & _
Range("BK45").Value & "','" & Range("BL45").Value & "','" &
Range("BM45").Value & "','" & Range("BN45").Value & "','" &
Range("B045").Value & "','" & _
Range("BP45").Value & "','" & Range("BQ45").Value & "','" &
Range("BR45").Value & "','" & Range("BS45").Value & "','" &
Range("BT45").Value & "','" & _
Range("BU45").Value & "','" & Range("BV45").Value & "','" &
Range("BW45").Value & "','" & Range("BX45").Value & "','" &
Range("BY45").Value & "','" & Range("BZ45").Value & "','" & _
Range("CA45").Value & "','" & Range("CB45").Value & "','" &
Range("CC45").Value & "','" & Range("CD45").Value & "','" &
Range("CE45").Value & "','" & _
Range("CF45").Value & "','" & Range("CG45").Value & "','" &
Range("CH45").Value & "','" & Range("CI45").Value & "','" &
Range("CJ45").Value & "','" & _
Range("CK45").Value & "','" & Range("CL45").Value & "','" &
Range("CM45").Value & "','" & Range("CN45").Value & "','" &
Range("C045").Value & "','" & _
Range("CP45").Value & "','" & Range("CQ45").Value & "')"





' NOTE: The above assumes all fields are TEXT data type, that is why
' the "'"s; might have trouble with other data types unless you match
' the format expected by the database. The order I give the values in
' corresponds to their resulting position in the database fields.

MyCn.Execute (SQLStr)
'MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

MsgBox "Successfully updated the table!", vbInformation


End Sub