Slicemahn
08-30-2007, 07:34 AM
Hello Everyone!
I am trying to write lines from Excel spreadsheet into my Access table. I know that the code works because I have executed it many times before. When I step through the code I receive a run time error 91: Object variable or With Block variable not set. I haven't a clue of what this means. Does anyone have any ideas? My code is as follows:
Sub DataUpload()
Dim conn As ADODB.Connection
Dim rcdst As ADODB.Recordset
Dim Row As Long
With ActiveSheet
Set conn = New ADODB.Connection
' connect to the database
conn.Open "Provider=Microsoft.JET.OLEDB.4.0; " & _
"Data Source=S:\RSS\BU 03\RESP 7070\DEPT 210\NWFPlanning\BUDGET TRACKING REPORTS\Audrey Ling\NAT DIS\National Call DisconnectsDB.mdb;"
'Open a recordset
rcdst.Open "tblNationalCallDisconnects", conn, adOpenKeyset, adLockOptimistic, adCmdTable
Row = 6
' the start row in the worksheet
Do While Len(Range("A" & Row).Formula) > 0
' add to the recordset
With rcdst
.AddNew
.Fields("ReportDate") = Cells(2, 8).Value
.Fields("UniversalCallID") = Range("A" & Row).Value
.Fields("CallID") = Range("B" & Row).Value
.Fields("SegmentNumber") = Range("C" & Row).Value
.Fields("ACD") = Range("D" & Row).Value
.Fields("CMS") = Range("E" & Row).Value
.Fields("StartDate") = Range("F" & Row).Value
.Fields("StartTime") = Range("G" & Row).Value
.Fields("StopDate") = Range("H" & Row).Value
.Fields("StopTime") = Range("I" & Row).Value
.Fields("DOW") = Range("J" & Row).Value
.Fields("CallingParty") = Range("K" & Row).Value
.Fields("DialedNumber") = Range("L" & Row).Value
.Fields("FirstVDN") = Range("M" & Row).Value
.Fields("DispositionTime") = Range("N" & Row).Value
.Fields("AnsweringSplit") = Range("O" & Row).Value
.Fields("AvayaID") = Range("P" & Row).Value
.Fields("AnsweringLoginID") = Range("Q" & Row).Value
.Fields("Duration") = Range("R" & Row).Value
.Update
End With
Row = Row + 1
Loop
rcdst.Close
Set rcdst = Nothing
conn.Close
Set conn = Nothing
End With
End Sub
Many thanks for any help that you may provide.
I am trying to write lines from Excel spreadsheet into my Access table. I know that the code works because I have executed it many times before. When I step through the code I receive a run time error 91: Object variable or With Block variable not set. I haven't a clue of what this means. Does anyone have any ideas? My code is as follows:
Sub DataUpload()
Dim conn As ADODB.Connection
Dim rcdst As ADODB.Recordset
Dim Row As Long
With ActiveSheet
Set conn = New ADODB.Connection
' connect to the database
conn.Open "Provider=Microsoft.JET.OLEDB.4.0; " & _
"Data Source=S:\RSS\BU 03\RESP 7070\DEPT 210\NWFPlanning\BUDGET TRACKING REPORTS\Audrey Ling\NAT DIS\National Call DisconnectsDB.mdb;"
'Open a recordset
rcdst.Open "tblNationalCallDisconnects", conn, adOpenKeyset, adLockOptimistic, adCmdTable
Row = 6
' the start row in the worksheet
Do While Len(Range("A" & Row).Formula) > 0
' add to the recordset
With rcdst
.AddNew
.Fields("ReportDate") = Cells(2, 8).Value
.Fields("UniversalCallID") = Range("A" & Row).Value
.Fields("CallID") = Range("B" & Row).Value
.Fields("SegmentNumber") = Range("C" & Row).Value
.Fields("ACD") = Range("D" & Row).Value
.Fields("CMS") = Range("E" & Row).Value
.Fields("StartDate") = Range("F" & Row).Value
.Fields("StartTime") = Range("G" & Row).Value
.Fields("StopDate") = Range("H" & Row).Value
.Fields("StopTime") = Range("I" & Row).Value
.Fields("DOW") = Range("J" & Row).Value
.Fields("CallingParty") = Range("K" & Row).Value
.Fields("DialedNumber") = Range("L" & Row).Value
.Fields("FirstVDN") = Range("M" & Row).Value
.Fields("DispositionTime") = Range("N" & Row).Value
.Fields("AnsweringSplit") = Range("O" & Row).Value
.Fields("AvayaID") = Range("P" & Row).Value
.Fields("AnsweringLoginID") = Range("Q" & Row).Value
.Fields("Duration") = Range("R" & Row).Value
.Update
End With
Row = Row + 1
Loop
rcdst.Close
Set rcdst = Nothing
conn.Close
Set conn = Nothing
End With
End Sub
Many thanks for any help that you may provide.