Excel export to Access - Run-time error 1004

[Ralph]

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

[Barb Reinhardt]

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

[OssieMac]

Hi Ralph,

Because it worked for 14 cells I decided to test your 15th cell and your
posted Range("046").Value is actually zero46 not the alpha O46.

Tested by copying and pasting 046 as text into a worksheet and using
=CODE(LEFT(A1,1)) and it returns 48.

--
Regards,

OssieMac

[OssieMac]

Hi again Ralph,

I decided to do a search for where you have zeros instead of alpha O and
found quite a few. In your VBA editor, select the section of code and use the
Edit -> Find and enter a zero and you will find them all.

Note that I think that Barbara's answer is the better solution. However,
personally I would modify the following so that I did not have to work out
the column number of CQ. It then becomes self documenting for the column Id
and easy to modify if required.

lastCol = Columns("CQ").Column
'Then use in following
For myCol = 1 To lastCol

--
Regards,

OssieMac

[Ralph]

thank you, I am not sure how this works to replace the detail in - SQLStr =

"INSERT INTO [tblPage1]
([ReviewNo],[CaseNo],[StateCode])Values('" & Range("A45").Value & "','"

Range("B45").Value & "','" & Range("C45").Value & "')"

Does the code you suggest get inserted ahead of this part?

I was able to get things to work after making the fields in the Access table
all text fields. Some of the Excel cells are text, others are dates and
numbers. Will your code handle that? I am thinking I can reformate as
necessary in Access.

thanks again

[Ralph]

thanks, I've written Barbara with a few questions. I was able to get my
method to work after fixing the "O"s and a few other changes to the cell
data. I did have to change everything to text because I don't the skills to
handle dates and numbers which some of the fields are. Is that a relatively
simple thing? I'll be transmitting the data once collected in the Access
table and need to conform to predefined field values. I can formate the data
once in Access but it might be easier to keep as much of it as I can when
uploading it from Excel to Access.

Thanks again,
Ralph

[OssieMac]

Hi again Ralph,

As you are having problems with the various cell contents (dates etc) I
would consider copying all of the data to a separate worksheet and then
simply import that to an Access table. All of the numberformats should then
work fine.

--
Regards,

OssieMac