Hi guys. I'm a newbie so please forgive this - but I don't know what
else to do. This spreadsheet is from hell.
I have been having such a problem with automating this excel
spreadsheet. The users wanted to update a field in an oracle table
from this spreadsheet. The problem is either with the refresh or the
loop, but it will only (intermittently) update the first record (if I
comment out the refresh background query) and skip the rest, or if I
leave refresh on or set it to false, then it gives me the dreaded
'1004' error.

I know parameter queries are tricky with oracle/microsoft OLE DB for
Oracle. But I don't see a reason why this should blow up like it
does. With the .Refresh, it goes straight to the error handler with
the prompt and the '1004' error.

Has anyone had this problem? If anyone has, could you help this
newb? I'm desperate.

I attached the spaghetti code below. I inherited this learning
opportunity. I'm sure you all know what that's like. It's fun. I
apologize for the following:


Sub TCN_Update_Sub()
On Error GoTo Error_Handler

If EventsTurnedOffBy = "" Then
objExcel.EnableEvents = False
objExcel.ScreenUpdating = False
EventsTurnedOffBy = "TCN_Update_Sub"
End If

' The TCN is updated in the Table without manual user intervention.

If v_instance = "t4" Then
strCnn = "OLEDB;Provider=MSDAORA;Data Source=dev_t4;User
ID=A_USER;Password=test1234"
Else
strCnn = "OLEDB;Provider=MSDAORA;Data Source=prod_p4;User
ID=A_USER;Password=prod1234"
End If

Set objMyQueryTable = ActiveSheet.QueryTables.Add( _
Connection:=strCnn, Destination:=Range("FA1"))

With objMyQueryTable
For Each v_cell In
objExcel.Workbooks(v_coal_spreadsheet).Worksheets( "LAB").Range("W3:W"
& LastVendorRow).Cells
objExcel.Cells(v_cell.Row,
TrafficControlNoCol).Interior.ColorIndex = xlNone
v_comment = ""
objExcel.Range("CS" & v_cell.Row).NumberFormat = "0"
' No Sample number
If Len(objExcel.Cells(v_cell.Row, LabIDCol)) = 0 Then
GoTo Get_Next_TCN_Cell
End If
' No Traffic Control Number data to update
If Len(objExcel.Cells(v_cell.Row, TrafficControlNoCol)) = 0
Then
GoTo Get_Next_TCN_Cell
End If
' PrelogcoalCol Values a
' 0 means that sample is already registered and the TCN can
be updated.
' 1 means that that the sample is in PRELOG status (not
registered)
If objExcel.Cells(v_cell.Row, PreLogCoalCol) < 0 Then
GoTo Get_Next_TCN_Cell
End If

If objExcel.Cells(v_cell.Row, TrafficControlNoCol).Value =
objExcel.Range("CS" & v_cell.Row).Value Then
v_comment = v_comment & "TCN Not Updated because Labworks
already contains this value." & Chr(10)
objExcel.Cells(v_cell.Row,
TrafficControlNoCol).ClearComments
objExcel.Cells(v_cell.Row, TrafficControlNoCol).AddComment
v_comment
objExcel.Range(Cells(v_cell.Row, PlantIDCol),
Cells(v_cell.Row, FlatFileProcessedCol)).Interior.ColorIndex = 35
GoTo Get_Next_TCN_Cell
End If

objExcel.Range(Cells(v_cell.Row, PlantIDCol),
Cells(v_cell.Row, FlatFileProcessedCol)).Interior.ColorIndex = xlNone
objExcel.Goto
Reference:=objExcel.Worksheets("LAB").Range(Cells( v_cell.Row,
LabIDCol)), Scroll:=True

ThisWorkbook.Worksheets("TVA_LAB").Cells(v_cell.Ro w,
TrafficControlNoCol).ClearComments

If Cells(v_cell.Row, DataErrorCol).Value = 0 Then
sq_upd1 = "update labworks.suserflds " _
& " set traf_cont_numb = " + UCase(Cells(v_cell.Row,
TrafficControlNoCol).Value) _
& " where sampno = '" _
& Cells(v_cell.Row, LabIDCol).Value & "' "

sq_upd2 = "update labworks.sample " _
& " set moddate = sysdate " _
& " where sampno = '" _
& Cells(v_cell.Row, LabIDCol).Value & "' "

.Name = "Query1"
.CommandText = Array(sq_upd1)
.CommandType = xlCmdDefault
.Refresh False
.CommandText = Array(sq_upd2)
.RefreshOnChange = True
objExcel.Range("CS" & v_cell.Row).Value =
objExcel.Cells(v_cell.Row, TrafficControlNoCol).Value
End If

Get_Next_TCN_Cell:

Next v_cell

End With

If EventsTurnedOffBy = "TCN_Update_Sub" Then
objExcel.EnableEvents = True
objExcel.ScreenUpdating = True
EventsTurnedOffBy = ""
End If

subroutine_end:

Exit Sub

Error_Handler:

If EventsTurnedOffBy = "TCN_Update_Sub" Then
objExcel.EnableEvents = True
objExcel.ScreenUpdating = True
EventsTurnedOffBy = ""
End If

MsgBox Error, , "TCN Update"
MsgBox "An error has occurred." & vbCr & Err.Number & vbCr &
Err.Description
objExcel.Range(Cells(v_cell.Row, PlantIDCol), Cells(v_cell.Row,
FlatFileProcessedCol)).Interior.ColorIndex = 35
v_comment = v_comment & "TCN NOT Updated" & Chr(10)
objExcel.Cells(v_cell.Row, TrafficControlNoCol).AddComment
v_comment

End Sub

On Nov 6, 3:13 pm, Holly wrote:
Hi guys. I'm a newbie so please forgive this - but I don't know what
else to do. This spreadsheet is from hell.
I have been having such a problem with automating this excel
spreadsheet. The users wanted to update a field in an oracle table
from this spreadsheet. The problem is either with the refresh or the
loop, but it will only (intermittently) update the first record (if I
comment out the refresh background query) and skip the rest, or if I
leave refresh on or set it to false, then it gives me the dreaded
'1004' error.

I know parameter queries are tricky with oracle/microsoft OLE DB for
Oracle. But I don't see a reason why this should blow up like it
does. With the .Refresh, it goes straight to the error handler with
the prompt and the '1004' error.

Has anyone had this problem? If anyone has, could you help this
newb? I'm desperate.

I attached the spaghetti code below. I inherited this learning
opportunity. I'm sure you all know what that's like. It's fun. I
apologize for the following:

Sub TCN_Update_Sub()
On Error GoTo Error_Handler

If EventsTurnedOffBy = "" Then
objExcel.EnableEvents = False
objExcel.ScreenUpdating = False
EventsTurnedOffBy = "TCN_Update_Sub"
End If

' The TCN is updated in the Table without manual user intervention.

If v_instance = "t4" Then
strCnn = "OLEDB;Provider=MSDAORA;Data Source=dev_t4;User
ID=A_USER;Password=test1234"
Else
strCnn = "OLEDB;Provider=MSDAORA;Data Source=prod_p4;User
ID=A_USER;Password=prod1234"
End If

Set objMyQueryTable = ActiveSheet.QueryTables.Add( _
Connection:=strCnn, Destination:=Range("FA1"))

With objMyQueryTable
For Each v_cell In
objExcel.Workbooks(v_coal_spreadsheet).Worksheets( "LAB").Range("W3:W"
& LastVendorRow).Cells
objExcel.Cells(v_cell.Row,
TrafficControlNoCol).Interior.ColorIndex = xlNone
v_comment = ""
objExcel.Range("CS" & v_cell.Row).NumberFormat = "0"
' No Sample number
If Len(objExcel.Cells(v_cell.Row, LabIDCol)) = 0 Then
GoTo Get_Next_TCN_Cell
End If
' No Traffic Control Number data to update
If Len(objExcel.Cells(v_cell.Row, TrafficControlNoCol)) = 0
Then
GoTo Get_Next_TCN_Cell
End If
' PrelogcoalCol Values a
' 0 means that sample is already registered and the TCN can
be updated.
' 1 means that that the sample is in PRELOG status (not
registered)
If objExcel.Cells(v_cell.Row, PreLogCoalCol) < 0 Then
GoTo Get_Next_TCN_Cell
End If

If objExcel.Cells(v_cell.Row, TrafficControlNoCol).Value =
objExcel.Range("CS" & v_cell.Row).Value Then
v_comment = v_comment & "TCN Not Updated because Labworks
already contains this value." & Chr(10)
objExcel.Cells(v_cell.Row,
TrafficControlNoCol).ClearComments
objExcel.Cells(v_cell.Row, TrafficControlNoCol).AddComment
v_comment
objExcel.Range(Cells(v_cell.Row, PlantIDCol),
Cells(v_cell.Row, FlatFileProcessedCol)).Interior.ColorIndex = 35
GoTo Get_Next_TCN_Cell
End If

objExcel.Range(Cells(v_cell.Row, PlantIDCol),
Cells(v_cell.Row, FlatFileProcessedCol)).Interior.ColorIndex = xlNone
objExcel.Goto
Reference:=objExcel.Worksheets("LAB").Range(Cells( v_cell.Row,
LabIDCol)), Scroll:=True

ThisWorkbook.Worksheets("TVA_LAB").Cells(v_cell.Ro w,
TrafficControlNoCol).ClearComments

If Cells(v_cell.Row, DataErrorCol).Value = 0 Then
sq_upd1 = "update labworks.suserflds " _
& " set traf_cont_numb = " + UCase(Cells(v_cell.Row,
TrafficControlNoCol).Value) _
& " where sampno = '" _
& Cells(v_cell.Row, LabIDCol).Value & "' "

sq_upd2 = "update labworks.sample " _
& " set moddate = sysdate " _
& " where sampno = '" _
& Cells(v_cell.Row, LabIDCol).Value & "' "

.Name = "Query1"
.CommandText = Array(sq_upd1)
.CommandType = xlCmdDefault
.Refresh False
.CommandText = Array(sq_upd2)
.RefreshOnChange = True
objExcel.Range("CS" & v_cell.Row).Value =
objExcel.Cells(v_cell.Row, TrafficControlNoCol).Value
End If

Get_Next_TCN_Cell:

Next v_cell

End With

If EventsTurnedOffBy = "TCN_Update_Sub" Then
objExcel.EnableEvents = True
objExcel.ScreenUpdating = True
EventsTurnedOffBy = ""
End If

subroutine_end:

Exit Sub

Error_Handler:

If EventsTurnedOffBy = "TCN_Update_Sub" Then
objExcel.EnableEvents = True
objExcel.ScreenUpdating = True
EventsTurnedOffBy = ""
End If

MsgBox Error, , "TCN Update"
MsgBox "An error has occurred." & vbCr & Err.Number & vbCr &
Err.Description
objExcel.Range(Cells(v_cell.Row, PlantIDCol), Cells(v_cell.Row,
FlatFileProcessedCol)).Interior.ColorIndex = 35
v_comment = v_comment & "TCN NOT Updated" & Chr(10)
objExcel.Cells(v_cell.Row, TrafficControlNoCol).AddComment
v_comment

End Sub

In case anyone else has a problem - I think we've figured it out.
The OLEDB driver has an issue with the refresh statement, and we
changed the connection to an ODBC connection.
We also had a "timing" issue with the database, so we put in some
waits after the refresh background query statements, and then a cancel
refresh to get around the problem.