How can I use a relative path for Excel output?

[craig_dickson]

[ FlexSim 16.0.1 ]

I am using Excel for both input and output from my model (which is completely process flow).

When I use MTEI to read in my input table, I am able to use a relative path, e.g. " ..\Data\Inputs.xlsx". But for MTEE and custom output, it seems to insist on the full path. Am I missing something? Thx

[adrian_haws · Accepted solution]

Relative paths do work for MTEE, as long as the exporter is set up correctly. However, for custom export when you call

excelopen() 

it needs the absolute path. If you want to use a relative path you can enter

excelopen(concat(modeldir(), “..\\Data\\Inputs.xlsx”))

Notice that there are two backslashes before and after “Data”.

[craig_dickson]

Taanks,

I'm not sure what I did before, but I did get the MTEE to use the relative path now. Is there a command I can call from code to run the MTEE at the end of run?

But I still am having problems with the custom export. When I run it I get a form that says:

"Could not open file c:\Projects\QVC\Solution\Model\..\Data\Outputs.xlsx". Seems like I may need another step to handle backing up a level? Note that the model is in:

c:\Projects\QVC\Solution\Model\*.fsm

while the Excel files are in

c:\Projects\QVC\Solution\Data\*.xlsx

Thanks,

Craig

[adrian_haws]

@Craig DIckson we've tested this in FlexSim 2016 with the ..\\ notation and it worked. When you export make sure it's to a file that has already been created, otherwise it won't work. Also, make sure to not have the spreadsheet open when you try to export to it.

[craig_dickson]

Adrian,

Can you look at this and see what's wrong (code below). It still doesn't seem to be working for me? I confirmed that the file and the sheet are both there.

excelopen(concat(modeldir(), "..\\Data\\Outputs.xlsx"));

// Set sheet to write to excelsetsheet("Orders");

// Write results

// Number of orders created excelwritenum(8,2,vOrderNumber);

excelexporttable("Output_Orders_OrderProfile",8,3,1,10);

excelclose(1);

[craig_dickson]

Adrian

I do actually have a return before "excelsetsheet("Orders"), it got lost in the paste somehow.

----------

The errors I get are (in order):

"Error closing the excel workbook (form title is "Excel Read Error"). When I click "OK", it goes to:

"Could not open file c:\projects\QVC\Solution\Model\..\Data\Outputs.xlsx" (form title also "Excel Read Error")

(Note that I want to write to c:\projects\QVC\Solution\Data\Outputs.xlsx.

the model itself in in

c:\projects\QVC\Solution\Model\QVC_Shuttle_Sorter.fsm)

when I click OK on that one, I get:

"Could not set sheet to orders" (also titled "Excel read error")

And then: "error Closing the excel workbook"

[logan_gold]

@Craig DIckson, I am attaching pathsexample.zip as a simple example of how I would use relative paths with the Excel Import/Export functions using the Import tab as well as the Custom Export in the Custom tab. The model is kept in a folder called model and both the input and output Excel files are kept in a folder called data. As long as both of those folders are kept in the same directory, the model should work.

It sounds like you've gotten the Excel Import/Export tabs to work with relative paths, but in the model, the Imports tab is using a relative path. If you click on the Import Tables button, the Inputs Global Table should be populated with the data from Inputs.xlsx.

In the Custom Export section of the Custom tab, there is sample code (which should also open with the model) that is using the concat() command, the modeldir() command, and a string with the relative path to Outputs.xlsx that set up the filename variable which is immediately used with the excelopen() command. The excelexporttable() command is then used to export the Outputs Global Table to Outputs.xlsx.

I am interested to know if this model runs fine on your computer or not. And if it does run fine, then the issue with your model might be how the Custom Export code is set up or perhaps something with Excel that is causing issues.

The first error message in your last reply seems to indicate there is already an opened Excel workbook and FlexSim tried to close the workbook before your call to

excelopen(concat(modeldir(), "..\\Data\\Outputs.xlsx"));

But, there was an error when trying to close it. Is Excel closed while you are trying to run the custom export code? Is there other code before excelopen() is called? Or have you already opened an Excel workbook somewhere else in the model without closing it?