You may want to try something like this to prepare your query

Sub Test()
Dim myString As String
Dim myRow As Long
Dim myCol As Long
Dim myCell As Excel.Range
Dim aWS As Excel.Worksheet
Dim myVal As Long

Set aWS = ActiveSheet

myString = ""

'gets data from row 45 column A to CQ (I believe)
For myRow = 45 To 45
For myCol = 1 To 95
Set myCell = aWS.Cells(myRow, myCol)
debug.print myrow,mycol, mycell.address
If myString = "" Then
myString = myCell.Value & ","
Else
myString = myString & myCell.Value & ","
End If
Next myCol

Next myRow
Debug.Print myString

'Replace last comma
myVal = InStrRev(myString, ",")
myString = Left(myString, myVal - 1)
Debug.Print myString

End Sub

--
HTH,

Barb Reinhardt



"Ralph" wrote:

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