Write to Excel File that Is already Open

[C_Haines_ENG]

Id like to be able to write to an excel file that's open, I'm not entirely sure if that's possible with the way I've set this up but having the program close every time is counter productive and id like for the excel file to remain open and writeable. 

However, every time I run my rule it wants to open a new instance of inventor which I assume is due to the highlighted code below:

'define Excel Application object
excelApp = CreateObject("Excel.Application")

'workbook exists, open it
excelWorkbook = excelApp.Workbooks.Open(xlsPath)

However modifying this code, even removing the "Open" command still makes it create a new instance of Excel

the full code is below:

'define the file to create/open
xlsPath = "C:\BOM Calculator\Test.xlsx"

'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run visibly, change to false if you want to run it invisibly
excelApp.Visible = True
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False



'check for existing file 
If Dir(xlsPath) <> "" Then
'workbook exists, open it
excelWorkbook = excelApp.Workbooks.Open(xlsPath)
ExcelSheet = excelWorkbook.Worksheets(1)
Else
'workbook does NOT exist, so create a new one
excelWorkbook = excelApp.Workbooks.Add
End If

']


'Insert data into Excel.
With excelApp
	
	If .Range("A1").Value < 2
		
		RowNum = 2
	   .Range("A1").Value = 2

	Else 
		
		RowNUM = .Range("A1").Value
		
	End If
	
			.Range("B" & RowNum) = iProperties.Value(modelName,"Project", "Part Number")
			.Range("C" & RowNum) = iProperties.Value(modelName, "Project", "Description")
		
End With   

'set all of the columns to autofit
excelApp.Columns.AutoFit   
'save the file
excelWorkbook.SaveAs(xlsPath)

''close the workbook and the Excel Application
''uncomment if you want to close the xls file at the end
excelWorkbook.Close
excelApp.Quit
excelApp = Nothing     

[A.Acheson]

Try getting the object rather than creating a new version.

Dim oExcel As Object ' or Excel.Application
Set oExcel = GetObject("Workbook_Name.xlsx").Application

Here is the link discussing that.  

[C_Haines_ENG]

How can i detect if its already open? I will have to create a new session for the first run through of the program. 

[WCrihfield]

Hi @C_Haines_ENG.  Below is a fairly simple little custom Function I often use for getting the Excel application in some of my solutions which access the Excel API directly.  It will first 'try' to get the already running instance of the application, and if that fails, it will then try to create a new instance of the application, and return that.  The process is pretty old, but still effective.

Function GetExcel(Optional oVisible As Boolean = False) As Excel.Application
	Dim oXL As Excel.Application
	Try
		'try to find an already running instance of the Excel Application
		oXL = GetObject(, "Excel.Application")
	Catch
		'it wasn't found open, so create an instance of it (start the application)
		oXL = CreateObject("Excel.Application")
	Catch
		Return Nothing
	End Try
	oXL.Visible = oVisible
	Return oXL
End Function

PS.  Using this may require that you have included a reference to the Excel API within the Header of your rule.

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

[C_Haines_ENG]

Inserting that into my code now gives me errors of

Ive tried to reorient the code like so:

xlsPath = "C:\BOM Calculator\Test.xlsx"


If Dir(xlsPath) <> "" Then

'define Excel Application object
Try
	'try to find an already running instance of the Excel Application
	excelApp = GetObject("Test.xlsx").Application
	MessageBox.Show("Found!", "Title")

	Catch
	'it wasn't found open, so create an instance of it (start the application)
	excelApp = CreateObject("Test.xlsx").Application
	excelWorkbook = excelApp.Workbooks.Open(xlsPath)	
	MessageBox.Show("Not Found :(", "Title")
End Try

Else
excelWorkbook = excelApp.Workbooks.Add	
excelWorkbook = excelApp.Workbooks.Open(xlsPath)
	
End If

 But I keep getting an AcitveX error.

Could you show me how I can impliment this? Using a function seems to screw everything up. 

[WCrihfield]

Sure.  I have attached the code in a text file, since it's fairly long.

One think I should mention though...once you include those 'AddReference' and 'Imports' lines, it will start to understand and recognize those API objects that are unique to Excel's API, so you will start to see the 'intellisense' pop-up hints for all that stuff that was not available before, but that can also complicate things for some folks, when they are not used to it.  It works great for some folks, and some processes, while seemingly making it more difficult or impossible for other folks/processes.  So, if you need to, you could just change my use of the 'Excel.Application' object Type, and use Object in its place, in those two locations of my custom function.  Or you could simply move my Try...Catch block of code out and put it directly into your code, then get rid of my function altogether.  If you do that, you may have to change the variable 'oXL' to your variable.

[C_Haines_ENG]

I am getting more errors with this section

	'define the file to create/open
	xlsPath = "C:\BOM Calculator\Test.xlsx"
	'define Excel Application object, and set it's Visibility to True
	excelApp = GetExcel(True)
	'suppress prompts (such as the compatibility checker)
	excelApp.DisplayAlerts = False
	excelWorkbook = excelApp.Workbooks.Open(xlsPath)
	ExcelSheet = excelWorkbook.Worksheets(1)

The problem being that  Workbook.Open is forcing it to always open the document instead of just selecting the opened excel file.

Aswell, doesnt the "Try" just select any excel application thats open at that moment?

[WCrihfield]

Hi @C_Haines_ENG.  If there are multiple instances of the Excel application running, this custom function (or that Try...Catch block) process, will simply get a random (or most likely the last initiated) instance of it.  That is different from having multiple Excel documents open.  You can have multiple Excel documents open within one instance of Excel, or you can have one or more documents open within multiple instances of the Excel application.  It can get pretty complicated when multiple instance of the main application may be involved.

If the document you want is already open within the instance of Excel you get, you can use something like the following to try to get that document:

excelWorkbook = excelApp.Workbooks.Item(xlsPath)

...then if it is not found that way, you can try opening it, if needed.  Those two attempts could be in a Try...Catch block too, in that order, but I would probably have a second Catch, for when it doesn't find either one (specified file can't be found).

PS.  I have actually developed my own custom functions for each step of that process (getting or creating an instance of the application, getting or creating the document, & getting or creating the sheet), but they are all set up for when the Excel API is being referenced, and are seemingly rather long.  I only use them for the most dynamic of situations, otherwise it is more efficient to do without them.

[C_Haines_ENG]

Error

Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX

Im astouded at how complicated this integration is. Riddled with bugs. 

Did I put it in correclty?

	'define the file to create/open
	xlsPath = "C:\BOM Calculator\Test.xlsx"
	'define Excel Application object, and set it's Visibility to True
	excelApp = GetExcel(True)
	'suppress prompts (such as the compatibility checker)
	excelApp.DisplayAlerts = False
	excelWorkbook = excelApp.Workbooks.Item(xlsPath)
	ExcelSheet = excelWorkbook.Worksheets(1)

[WCrihfield]

Hi @C_Haines_ENG.  Sorry for all the confusion.  Debugging someone else's code that is accessing Excel, when you don't have the files on hand to test with can be fairly challenging, especially when I've got several different things going on.  I have attempted once again to slightly rewrite your code, but this time I have also included my other two custom functions, just so you can see what all is going on within them, for some ideas.  As I mentioned before, you can do without them, once you've got a good handle on most of the related possibilities, and debugging methods.  I did notice two variables that were slightly different from each other...one named "RowNum", and the other "RowNUM".  Although the spelling is the same, capitalization is important in variable names.  Also, I found out why it was showing the message 'Range is ReadOnly'.  Your two lines of code that include calls to the iProperties.Value() method did not include the ".Value" after the Range specification, so it thought you were trying to overwrite the Range object itself, instead of the Range's Value.

I attached my updated code again as a text file, to keep the post short.  Hopefully this version will work better for you.  I'm about to leave for the day, so if more changes are needed, I can look at it again tomorrow, if I have time.  Or one of the others here on the forum can give it a try.

[andreas.jonssonF98YC]

Hi I have tried your code and as in previous answers from others I got error on Getobject(,"Excel.Application"). Error

as follows : 

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Element not found. (Exception from HRESULT: 0x8002802B (TYPE_E_ELEMENTNOTFOUND)).

[C_Haines_ENG]

It seems that if you just LOOK at the excel file without touching it, the command line will work. However if you EDIT it at all, the system will not register it as the same excel file and seems to store it in a temp file somewhere.

I gave up on solving this one eons ago, it just doesnt work. You can write a try command telling you to close the excel file before running it.

[C_Haines_ENG · Accepted solution]

This seems to be the best solution I could come up with, sometimes it fails but it will pretty consistently grab the right application, open or not, and display it to the user.

Imports Microsoft.Office.Interop 'To use excel
Imports Microsoft.Office.Interop.Excel 'To use excel
AddReference "Microsoft.Office.Interop.Excel" 'To use excel 

Sub Main

	ExcelPath = "PATH TO EXCEL FILE"
	
	'GET THE WORKBOOK, THIS WILL EITHER GET THE OPEN WORKBOOK OR OPEN
	'AS A BACKGROUND TASK??
	Dim WB As Excel.Workbook = GetObject(ExcelPath)
	
	'DEFINE THE WORKSHEET, BETTER TO DO IT THIS WAY THEN USE THE APPLICATION
	Dim WS As Excel.Worksheet = WB.Worksheets("Sheet1")
	
	'IF EXCEL OPENED AS A BACKGROUND TASK, MAKE ITS WINDOW VISIBLE, 
    'IF YOU MINIMIZE THE APPLICATION IT WONT HAVE A WINDOW???
    If WB.Windows.Count <> 0
	    If WB.Windows.Item(1).Visible = False Then WB.Windows.Item(1).Visible = True
	End If
	
	'BRING EXCEL TO FRONT
	WB.Application.WindowState = WB.Application.WindowState
    AppActivate(WB.Application.Caption)
	
	'WRITE A RANDOM NUMBER INTO THE CELL "A1"
	WS.Range("A1").Value = CInt(Math.Ceiling(Rnd() * 50)) + 1

End Sub