Hi @C_Haines_ENG. In the past I have read about, and looked into complicated scenarios where multiple instances of Excel are open at the same time, and trying to work with a specific instance of it, but I never went far enough into it for it to become 'readily available info in my head', because I simply never needed that level of control where I work, and therefore do not have that working experience with that specific scenario.
I can however get Workbook from Application, or Application from Workbook. Funny thing is that most of these Types of objects are Interface, instead of Class instances, so we can not simply use 'New' keyword, and must instead use Property values to get them. The Workbook object has a Property named Application, and also one called ActiveSheet (Object value Type). The Application has several 'Active...' type properties, including the ActiveWorkbook Property, ActiveSheet, ActiveCell, and also has the Workbooks Property. Worksheet also has an Application Property.
1) What is odd to me at the moment is, if I do not have an instance of Excel open, and use the 'GetObject' method, and specify the full file name of the Excel file I want to open, it will give me a Workbook object, which I can then use to get the Application object, which I can then use to get the Worksheet object. However it seems to only allow me to visibly show the Application, and not the Workbook or Worksheet.
2) If I use the GetObject method, and specify an empty Strings for path, and specify the Class, it will return an ApplicationClass object Type. I can then use that to 'Open' the file, to get the Workbook object. I can then use the Workbook object to get the Worksheet object. In this scenario, I can make all objects involved visible.
3) If I use the CreateObject method, it will give me the ApplicationClass object, which I can use to open the file to get the Workbook, then the Worksheet, and can also make all of them visible.
Example of first scenario test code (CreateObject, supplying full file name):
Dim sFile As String = "C:\Temp\Test.xlsx"
If Not System.IO.File.Exists(sFile) Then Return
'gets the Workbook object
Dim oWB As Object = GetObject(sFile, "Excel.Application")
Logger.Info("GetObject Type = " & TypeName(oWB))
'gets the Application object
Dim oXLApp As Object = oWB.Application
Logger.Info("oXLApp Type = " & TypeName(oXLApp))
oXLApp.Visible = True 'make the application visible
'there is no Workbook.Visible property or method, and ActiveWorkbook is ReadOnly
'gets the Worksheet object
Dim oWS As Object = oWB.ActiveSheet
Logger.Info("oWS Type = " & TypeName(oWS))
oWS.Visible = True 'supposed to make the Worksheet visible, but does not in test
MsgBox("Review Opened Excel file.", vbInformation, "iLogic")
oWB.Close
MsgBox("Review Workbook Closed, but not Excel.", vbInformation, "iLogic")
oXLApp.Quit
oWS = Nothing
oWB = Nothing
oXLApp = Nothing
Example of second scenario test code (GetObject, empty String supplied):
Dim sFile As String = "C:\Temp\Test.xlsx"
If Not System.IO.File.Exists(sFile) Then Return
'get the ApplicationClass object
Dim oXLApp As Object = GetObject("", "Excel.Application")
Logger.Info("oXLApp Type = " & TypeName(oXLApp))
oXLApp.Visible = True 'make the application visible
'get the Workbook object
Dim oWB As Object = oXLApp.Workbooks.Open(sFile)
Logger.Info("GetObject Type = " & TypeName(oWB))
'gets the Worksheet object
Dim oWS As Object = oWB.ActiveSheet
Logger.Info("oWS Type = " & TypeName(oWS))
oWS.Visible = True 'supposed to make the Worksheet visible, but does not in test
MsgBox("Review Opened Excel file.", vbInformation, "iLogic")
oWB.Close
MsgBox("Review Workbook Closed, but not Excel.", vbInformation, "iLogic")
oXLApp.Quit
oWS = Nothing
oWB = Nothing
oXLApp = Nothing
Example of scenario 3 test code (CreateObject):
Dim sFile As String = "C:\Temp\Test.xlsx"
If Not System.IO.File.Exists(sFile) Then Return
'gets the ApplicationClass object
Dim oXLApp As Object = CreateObject("Excel.Application")
Logger.Info("oXLApp Type = " & TypeName(oXLApp))
oXLApp.Visible = True 'make Excel visible
'gets the Workbook object
Dim oWB As Object = oXLApp.Workbooks.Open(sFile)
Logger.Info("oWB Type = " & TypeName(oWB))
'gets the Worksheet object
Dim oWS As Object = oWB.ActiveSheet
Logger.Info("oWS Type = " & TypeName(oWS))
oWS.Visible = True 'make the Worksheet visible
MsgBox("Review Opened Excel file.", vbInformation, "iLogic")
oWB.Close
MsgBox("Review Workbook Closed, but not Excel.", vbInformation, "iLogic")
oXLApp.Quit
oWS = Nothing
oWB = Nothing
oXLApp = Nothing
Not sure why scenario 1 is acting like that, but maybe that is part of the problem you seem to be experiencing...aside from the multiple instances of Excel issue.
Wesley Crihfield
(Not an Autodesk Employee)
