Excel feedback in VB6
-
:(Hi all who know all I have a problem in that I need to automate an excel spreadsheet with external data sources and update the data from a remote server. Problem is, this is a standard spreadsheet which I cannot (not allowed) change yet want to read certain data from it once it has updated. Starting Excel and automating the update is not a problem (see code below). However, I need to know when the update is complete since I then have to extract specific data from this spreadsheet in my code in VB to use. How can I get feedback from Excel to say that the update is complete or alternatively pass execution to another piece of code once the update is done. "Command1_Click" section of code works fine - its the "AfterRefresh" bit thats got me stumped since it don't work!!!!!!!! The "AfterRefresh" section apparently refers to a PivotTable according to the excel Help section but I am not using a PivotTable. Any help would be GREATLY appreciated Regards Jeremy Private Sub Command1_Click() Set m_objExcel = Excel.Workbooks Dim msExcelWorkbook As String msExcelWorkbook = App.Path & "\ExcelReports\Update.xls" m_objExcel.Open msExcelWorkbook m_objExcel.Application.Visible = True m_objExcel.Application.ActiveWorkbook.RefreshAll End Sub Private Sub ActiveWorkBook_AfterRefresh(Success As Boolean) If Success = True Then m_objExcel.Application.ActiveWorkbook.Save m_objExcel.Application.ActiveWorkbook.Close m_objExcel.Application.Quit Call MsgBox("Refreshed all Data", vbOKOnly) Else ' failed or was cancelled End If End Sub
-
:(Hi all who know all I have a problem in that I need to automate an excel spreadsheet with external data sources and update the data from a remote server. Problem is, this is a standard spreadsheet which I cannot (not allowed) change yet want to read certain data from it once it has updated. Starting Excel and automating the update is not a problem (see code below). However, I need to know when the update is complete since I then have to extract specific data from this spreadsheet in my code in VB to use. How can I get feedback from Excel to say that the update is complete or alternatively pass execution to another piece of code once the update is done. "Command1_Click" section of code works fine - its the "AfterRefresh" bit thats got me stumped since it don't work!!!!!!!! The "AfterRefresh" section apparently refers to a PivotTable according to the excel Help section but I am not using a PivotTable. Any help would be GREATLY appreciated Regards Jeremy Private Sub Command1_Click() Set m_objExcel = Excel.Workbooks Dim msExcelWorkbook As String msExcelWorkbook = App.Path & "\ExcelReports\Update.xls" m_objExcel.Open msExcelWorkbook m_objExcel.Application.Visible = True m_objExcel.Application.ActiveWorkbook.RefreshAll End Sub Private Sub ActiveWorkBook_AfterRefresh(Success As Boolean) If Success = True Then m_objExcel.Application.ActiveWorkbook.Save m_objExcel.Application.ActiveWorkbook.Close m_objExcel.Application.Quit Call MsgBox("Refreshed all Data", vbOKOnly) Else ' failed or was cancelled End If End Sub
Try using the WithEvents keyword. Dim WithEvents xl As Excel.Application Dim WithEvents Wb As Excel.Workbook Dim WithEvents ws As Excel.Worksheet You will have to create a reference to the Microsoft Excel Object library. I have 9.0 on my computer. Below is a cut-n-past directly from the help file. Simply highlight the WorkBook or WorkSheet words in the Dim statements above and press F1 for help. The Microsoft Excel Visual Basic Reference Help window should appear. CUT-N-PASTE from the Help File: SheetChange Event Occurs when cells in any worksheet are changed by the user or by an external link. Syntax Private Sub object_SheetChange(ByVal Sh As Object, ByVal Source As Range) object Application or Workbook. For more information about using events with the Application object, see Using Events with the Application Object. Sh A Worksheet object that represents the sheet. Source The changed range. Remarks This event doesn't occur on chart sheets. Not sure this is what your looking for but it may help. Michael
-
Try using the WithEvents keyword. Dim WithEvents xl As Excel.Application Dim WithEvents Wb As Excel.Workbook Dim WithEvents ws As Excel.Worksheet You will have to create a reference to the Microsoft Excel Object library. I have 9.0 on my computer. Below is a cut-n-past directly from the help file. Simply highlight the WorkBook or WorkSheet words in the Dim statements above and press F1 for help. The Microsoft Excel Visual Basic Reference Help window should appear. CUT-N-PASTE from the Help File: SheetChange Event Occurs when cells in any worksheet are changed by the user or by an external link. Syntax Private Sub object_SheetChange(ByVal Sh As Object, ByVal Source As Range) object Application or Workbook. For more information about using events with the Application object, see Using Events with the Application Object. Sh A Worksheet object that represents the sheet. Source The changed range. Remarks This event doesn't occur on chart sheets. Not sure this is what your looking for but it may help. Michael
-
We are on the right track. This looks promising Being a newbie, how do I trigger this event or pass info to this sub once Excel has updated. Many thanks Jeremy
The Excel object should fire the events. Make sure the withevents declaration is at the class or module level. Now click on the left dropdown above the code and select the Excel object variable then right dropdown to select the appropriate events. I have not personally used this but the events should fire. I just don't an event you are needing. You will have to play around with them so see which one will suite your needs. Michael