T
Troy Munford
Hi all,
I have a workbook that queries into an Oracle database with 19 different queries.
Here's the problem:
I'm using a relatively simple piece of code that doesn't seem to return errors every time it fails (NOTE: It does returns errors most of the time).
Basically, the refresh may occur on some queries, but not all of them, and no error is returned. Sometimes it fails for all of them, but no error is returned. Again, most of the time it fails, it returns some sort of error that says so, but it's the times that it doesn't that are causing us major headaches.
I automate the running of the function (macro) from a VB6 executable.
Excel Function/Macro:
Public Function refresh_all() As Boolean
'---------DO NOT MODIFY---------
Dim fOK As Boolean
On Error GoTo Proc_Err
'---------END DO NOT MODIFY---------
'
Sheets("Raw Data").Select
ActiveWorkbook.RefreshAll
ActiveWindow.SelectedSheets.Visible = False
Sheets("Reference").Select
'Only modify the following if your code modifies it, otherwise leave it alone
fOK = True
'---------DO NOT MODIFY---------
Proc_Exit:
refresh_all = fOK
Exit Function
Proc_Err:
fOK = False
GoTo Proc_Exit
'---------END DO NOT MODIFY---------
End Function
Code snippet that calls the function:
In the case of the code below, there is actually a loop that runs each macro. The "ParameterName" is the name of the macro (in this case the macro is named "refresh_all"). I have standard error handling (on error goto Proc_err), and do not have "on error resume next" in the function at all. The function is running the first one because the macro has a return value of boolean.
'check for boolean in table to determine if we expect a return value
If .Fields("IsReturnValue").Value <> 0 Then
'Run and return as a boolean
fok = ExcelApp.Run(.Fields("ParameterName").Value)
If fok Then
.MoveNext
Else
'The macro failed so don't continue running additional macros
Exit Do
End If
Else
'We're not expecting a return value, so run as normal
ExcelApp.Run .Fields("ParameterName").Value
.MoveNext
fok = True
End If
Special notes:
The day it fails, we generally have a network issue that would prevent each query from connecting to the database at all. Is it possible that the network issues are causing it to fail, but the Excel query that happens to be running at the time (refreshAll runs them sequentially, right?) is simply unaware that it really failed becasue it couldn't get all the results?
Problem is sporadic, but causes us pain as we have aproximately 17 Excel files that run like this every day. If one failed, it's like they all failed, and we need to do massive research and explain why the customer sees blank data, or worse - data that was saved with the initial results of the query when the report was designed (last year).
I have a workbook that queries into an Oracle database with 19 different queries.
Here's the problem:
I'm using a relatively simple piece of code that doesn't seem to return errors every time it fails (NOTE: It does returns errors most of the time).
Basically, the refresh may occur on some queries, but not all of them, and no error is returned. Sometimes it fails for all of them, but no error is returned. Again, most of the time it fails, it returns some sort of error that says so, but it's the times that it doesn't that are causing us major headaches.
I automate the running of the function (macro) from a VB6 executable.
Excel Function/Macro:
Public Function refresh_all() As Boolean
'---------DO NOT MODIFY---------
Dim fOK As Boolean
On Error GoTo Proc_Err
'---------END DO NOT MODIFY---------
'
Sheets("Raw Data").Select
ActiveWorkbook.RefreshAll
ActiveWindow.SelectedSheets.Visible = False
Sheets("Reference").Select
'Only modify the following if your code modifies it, otherwise leave it alone
fOK = True
'---------DO NOT MODIFY---------
Proc_Exit:
refresh_all = fOK
Exit Function
Proc_Err:
fOK = False
GoTo Proc_Exit
'---------END DO NOT MODIFY---------
End Function
Code snippet that calls the function:
In the case of the code below, there is actually a loop that runs each macro. The "ParameterName" is the name of the macro (in this case the macro is named "refresh_all"). I have standard error handling (on error goto Proc_err), and do not have "on error resume next" in the function at all. The function is running the first one because the macro has a return value of boolean.
'check for boolean in table to determine if we expect a return value
If .Fields("IsReturnValue").Value <> 0 Then
'Run and return as a boolean
fok = ExcelApp.Run(.Fields("ParameterName").Value)
If fok Then
.MoveNext
Else
'The macro failed so don't continue running additional macros
Exit Do
End If
Else
'We're not expecting a return value, so run as normal
ExcelApp.Run .Fields("ParameterName").Value
.MoveNext
fok = True
End If
Special notes:
The day it fails, we generally have a network issue that would prevent each query from connecting to the database at all. Is it possible that the network issues are causing it to fail, but the Excel query that happens to be running at the time (refreshAll runs them sequentially, right?) is simply unaware that it really failed becasue it couldn't get all the results?
Problem is sporadic, but causes us pain as we have aproximately 17 Excel files that run like this every day. If one failed, it's like they all failed, and we need to do massive research and explain why the customer sees blank data, or worse - data that was saved with the initial results of the query when the report was designed (last year).