B4J Question [SOLVED]How to close excel programmatically?

[Zvi]

Hi,
I can successfully write an excel file once with a particular name:

    'save the workbook
    Dim f As String = Workbook.SaveAs(File.DirApp, "Checkers_Subscribers.xlsx", True)
    Wait For (xl.OpenExcel(f)) Complete (Success As Boolean)

When I rerun the code (without manually closing the created workbook) the program automatically saves to:
Checkers_Subscribers (1).xlsx .... then Checkers_Subscribers (2).xlsx ... then Checkers_Subscribers (3).xlsx etc.

If I try to delete or copy the file using file.delete or file.copy I get an error because "the file is in use by another program".

How can I close "Checkers_Subscribers.xlsx" with code so that I can delete it and rerun the program to produce an updated excel file with the same name?

Any help would be much appreciated.

I have noticed that if I don't use the line:

Wait For (xl.OpenExcel(f)) Complete (Success As Boolean)

then the excel is still created but not shown and can be deleted and created again with the same name.

So possibly the solution would be to go down this path and make a separate data file to hold updated data (the data file can be read and updated) and just use the excel file to show the updated data when it is opened manually.

 

[Daestrum]

Could you not just use 'Save' instead of a 'SaveAs', thus overwriting the original file.

 

[Zvi]

Workbook.Save(... is not recognized.
Anyway, it wouldn't solve the problem.
The problem is how to close an excel file with code.

 

[Zvi]

I solved it by not opening the excel file (and so not having to close it!) by deleting the line:

Wait For (xl.OpenExcel(f)) Complete (Success As Boolean)

and just leaving the line:

Workbook.SaveAs(File.DirApp, "Checkers_Subscribers.xlsx", True)

This saves data to the excel without opening it, so the data is refreshed under the same file name.

I use a map file to keep track of the data in the excel file.

This isn't the most elegant solution, I would prefer to read and write data directly to the excel file, but it has the advantage of keeping a copy of the data both in a data file and in the excel file.
Also, I can move the excel file to another location e.g. for backup, and the program will build another excel file with the same name containing all the data saved in the data file.