How to add some value in an Excel spreadsheet without opening and closing it using com object in autohotkey?

I am trying to use this code to add some values in a cell without opening an Excel spreadsheet and closing it.

f1::
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open("c:\Users\Manoj Aggarwal\Desktop\DATA.xlsx")
Xl.Visible := False
Xl.Range("b1").Value:= "jaisiaramji"
Workbooks.Save()
Workbooks.Quit()
Workbooks:=""
Return

They are working, but when I manually open that Excel sheet it pops up messages that:

DATA.xlsx is already open. Reopening will cause any changes you made to be discarded.
Do you want to reopen DATA.xlsx.

What modification do I have to make in the code?

2 Answers

Not sure how to get from your code to something that works, but you need to have a reference to the workbook and then use the Workbook.Close Method with the first parameter set to true in order to save the file when it closes.

See the middle block of code in the post here for getting a reference to the active sheet and workbook, which is not what you're doing,but it may give you some things to try and test. I would be interested in seeing a working solution if you arrive at one.

Of interest when working with the Excel objects is that you can have references to three different objects (application, workbook and worksheet), which have their own respective methods for operating on them. A bit opaque at first unless you read through the MSDN documentation.

I've tried the below 2 lines and it has worked for me:

xl.ActiveWorkbook.save() ;saving file

Xl.ActiveWorkbook.close() ;closing file

So no more error message "DATA.xlsx is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen DATA.xlsx"