how to save excel without opening it?

[capslock]

Hi, I try to use AHK with excel file and I'm trying to manipulate excel data without open the file following this example.
https://autohotkey.com/board/topic/5698 ... ntry381256
first, when I try to change the data and read it back, it work but when I open the excel file normally the data didn't change.
so I try to save the file using this code.

Code: Select all

FilePath := "G:\AutohotkeyScript\testexcel.xlsx" ; example path
oExcel := ComObjGet(FilePath) ; access Workbook object
;oExcel.Visible := True ; make Excel Application visible
MsgBox % oExcel.Sheets(1).Range("A1").Value ; get value from A1 cell in first sheet
oExcel.Sheets(1).Range("A1").Value := 3 ; change value in A1 cell
MsgBox % oExcel.Sheets(1).Range("A1").Value ; readback the value from A1 cell in first sheet
oExcel.Save()

and the problem is the save work, data changed, when I run the script to read data again the data changed properly.
but I cannot open the file normally anymore, it became some kind of a blank file.

how can I fix this?

ps.I try to make it Visible(the commented line) but I get compile error: 0x80020006 - Unknow name.

[tmplinshi]

FilePath = C:\Book1.xlsx
xlBook := ComObjGet(FilePath)
xlBook.Application.Windows(xlBook.Name).Visible := True
xlBook.ActiveSheet.Range("A1").value := "this text is set by AHK script"
xlBook.save
xlBook.close

[FanaticGuru]

The idea of working with an Excel file without opening it is an illusion.
ComObjGet starts Excel and opens a file as a hidden Workbook. You can go to View/Windows/Unhide and see hidden workbooks.
Then when you save it, it is saved as a hidden workbook.

Much better to start Excel with ComObjCreate("Excel.Application") and just not make it visible with Xl.Visible. Now you can work with workbooks as normal all invisibly because the parent Excel is invisible but without making the individual workbooks have the hidden property.

Code: Select all

Xl := ComObjCreate("Excel.Application")
;~ Xl.Visible := True ; make Excel Application visible
Wb := Xl.Workbooks.Open(A_Desktop "\Test\Test.xlsx")
MsgBox % Wb.Sheets(1).Range("A1").Value ; get value from A1 cell in first sheet
Wb.Sheets(1).Range("A1").Value := 3 ; change value in A1 cell
MsgBox % Wb.Sheets(1).Range("A1").Value ; readback the value from A1 cell in first sheet
Wb.Save()
Xl.Quit()

FG

[FanaticGuru]

FilePath = C:\Book1.xlsx
xlBook := ComObjGet(FilePath)
xlBook.Application.Windows(xlBook.Name).Visible := True
xlBook.ActiveSheet.Range("A1").value := "this text is set by AHK script"
xlBook.save
xlBook.close

That is a round about fix that will sorta work but I imagine you are going to see the Window for a moment before it is closed which kind of defeats the point of keeping everything out of view.

My first instinct would have been xlBook.Parent.Windows but pretty much same difference of jumping through hoops to avoid using ComObjCreate("Excel.Application").

FG

[tmplinshi]

but I imagine you are going to see the Window for a moment before it is closed

I thought so, but I tested and didn't see anything.

[capslock]

they both work for me but I will stick with the second method then, thanks!!

[FanaticGuru]

but I imagine you are going to see the Window for a moment before it is closed

I thought so, but I tested and didn't see anything.

I just tested it and for me I see the Workbook for a moment then it closes and then a blank Excel with no workbooks is left open and visible.

FG

[burque505]

BUMP
Actually, you can work with an Excel file without opening it (in Excel, that is ).
If you look at this thread there are examples of using EPPlus.dll to open, modify and save an Excel .xlsx file, completely without Excel.
Tables, charts, pictures, etc.
Regards,
burque505