PDA

View Full Version : Solved: excel data to access as cell values



philix007
10-25-2007, 09:53 AM
I'm working to export data from excel to an access db..
it's failing now at " .Fields(ARCELLS(i)) = Range(ARCELLS(i))"
getting error 1004 method 'range' of object'_Global' failed

It's got me stumped now... I'm guessing it's something silly and small that I'm just missing when I read it over...

thanks in advance!!

Phil


Sub progworksheettoaccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\progers\Desktop\quoting\QUOTING.mdb;"
Set rs = New ADODB.Recordset
rs.Open "PROGRESSIVEWORKSHEET", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rs
.AddNew
Dim ARCELLS() As Variant
Dim i As Integer

ARCELLS = Array("D3", "I3", "O3", "R3", "G5", "M5", "Q5", "F7", "G8", "O8", "G45", "P45", "E47", "I47", "L47", "O47", "Q47", "G49", "P49", "D51", "H51", "O51", "R51", "D54", "C9", "E9", "G9", "I9", "K9", "M9", "N9", "O9", "P9", "Q9", "R9", "C10", "E10", "G10", "I10", "K10", "M10", "N10", "O10", "P10", "Q10", "R10" & _
"C11", "E11", "G11", "I11", "K11", "M11", "N11", "O11", "P11", "Q11", "R11", "C12", "E12", "G12", "I12", "K12", "M12", "N12", "O12", "P12", "Q12", "R12", "C13", "E13", "G13", "I13", "K13", "M13", "N13", "O13", "P13", "Q13", "R13", "C14", "E14", "G14", "I14", "K14", "M14", "N14", "O14", "P14", "Q14", "R14", "C15", "E15", "G15", "I15", "K15", "M15", "N15", "O15", "P15", "Q15", "R15" & _
"C16", "E16", "G16", "I16", "K16", "M16", "N16", "O16", "P16", "Q16", "R16", "C17", "E17", "G17", "I17", "K17", "M17", "N17", "O17", "P17", "Q17", "R17", "C18", "E18", "G18", "I18", "K18", "M18", "N18", "O18", "P18", "Q18", "R18", "C19", "E19", "G19", "I19", "K19", "M19", "N19", "O19", "P19", "Q19", "R19", "C20", "E20", "G20", "I20", "K20", "M20", "N20", "O20", "P20", "Q20", "R20")

i = 0

Do While i < 155

.Fields(ARCELLS(i)) = Range(ARCELLS(i))

i = i + 1
Loop
.Update
End With

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub