How to check if an Excel Workbook is already opened ?

Question

question

Pierre G asked Mar 15, '23 Joerg Vogel commented Mar 17, '23

How to check if an Excel Workbook is already opened ?

Hi,

When I do an export (with the Excel Interface or by code with excelmultitableexport()), I need to manually close the destination Excel Workbook before the export. If I don't do it, the export will seem to work (no error message and I can see the export progress bar) but the data won't be "exported" (or maybe they are instantly "crushed" when I close and re-open the Excel workbook to watch my data).

I imagine that FlexSim won't be able to close the Excel Workbook. But is there a way to check if the destination Excel Workbook is already opened before to start my export (with excelmultitableexport()) ? I want to warn the user and ask him to manually close the Excel Woorkbook if it's already open.

I tried to use excelopen(), excelclose(), ... but it doesn't seem to work. Or maybe I'm not using it the right way.

I'm using Office 365, Excel version 2302, build 16130.20218.

Thanks for your help.

Software Version:

FlexSim 23.0.4

excel excel export excelclose excelopen

· 1

Joerg Vogel commented · Mar 17, 2023 at 06:48 AM

@Pierre G, FlexSim is a tool, but not an SDK. If you need core functions of a programming language you get access to them by calling a dll from within FlexSim. FlexSim Basic programming language is c++. Dealing with MS Programs you should consider to use Languages introduced by MS like c#.

1 ·

Answer 1 · 2023-03-15T17:44:57Z

Kavika F answered Mar 15, '23 Kavika F commented Mar 16, '23

Hey @Pierre G, there's no built-in FlexSim way to check if an Excel program is open (that I know of). However, you can make a simple Python function that prompts you to close every Excel file that's open.

# closeExcels.py
import subprocess

def promptCloseExcels():
    subprocess.call(["taskkill", "/T","/IM",EXCEL_PROCESS])

In FlexSim, you can make a User Command that calls this script. Then you can run this script before you try to export to your tables.

(If you need help hooking up a Python Script, you can check out this post for guidance).

I'll attach the files I used so you can play around with it.

test-export.fsm

Testing.xlsx

closeExcels.py

1678902136369.png (38.5 KiB)

test-export.fsm (68.1 KiB)

testing.xlsx (8.1 KiB)

closeexcels.py (374 B)

· 2

Pierre G commented · Mar 16, 2023 at 04:15 PM

Hi,

Thanks for your answer.

It can help me, but my model need to be used easily on my customer computer. It will be more difficult to ask him to install python and to configure FlexSim to use it.

I don't necessarily need to close the Excel workbooks. If I'm able to warn the user with a message, it would be enough for me. There is no possibility to check if an Excel workbook is already opened ?

If it's not possible, this could be an idea. I think that it's a bit "strange" that I can run an export even if the export won't work at the end (if my Excel workbook is opened).

Thanks for your help.

0 ·

Kavika F ♦ Pierre G commented · Mar 16, 2023 at 09:19 PM

Thank you for your suggestion; I'll bring it up to the dev team.

Excel blocks write access from external programs to files currently open in Excel to protect data integrity of that file.

Another possible solution would be to have your Excel file and FlexSim program read from and write to a common file/SQL Database. For example, if you have a workbook that you never open (i.e., create a temporary workbook "temp_book.xlsx"), you can export your FlexSim data to that. Then your actual workbook can pull data from that.

Whenever you export data and update your temporary workbook, you can refresh the query/connection to it from the original and it will update the cells accordingly.

0 ·

1679001570993.png (24.6 KiB)

question