Excel table import using user commands

Hello Everyone,
I want to import single excel table to a global table using user commands. The code I wrote is not working. Here are the steps that I followed.

1) created a global table called "table1"
2) The excel table called "ordertable", which has 11 rows and 6 columns.
3) wrote the follwing code in user commands
----------------------------------------------------------
treenodecurrent = ownerobject(c);
/**Custom Code*/
excelopen("C:\Users\rahmaa5\Desktop\ordertable"); // open a link with an Excel workbook
excelsetsheet("Sheet1"); // set the active worksheet - sheet names should contain only alphanumeric characters and underscore (_)
//add your import code here using commands: excelreadnum(), excelreadstr(), excelimporttable()
int row;
int col;
for(row = 1; row <= 11; row++)
{
for(col = 1; col <= 6; col++)
{
int x= excelread(row, col);
excelimporttable("table1",1,1,11,6);
}
}
msg("Excel Import","Import complete",1);
excelclose(0); // close the workbook
-----------------------------------------------------------------------

Now when I am trying to run the user command ordertable_import(), it's giving me the follwoing exception:

exception: Exception caught in flexscript execution of MAIN:/project/model/Tools/UserCommands/ordertable_import/code line 17 instruction 20. Discontinuing execution.

So there is some problem with the command line
excelimporttable("table1",1,1,11,6);

Can anyone help me out please?

Ardodul,

the command you want to use is settablenum. You already read the value from excel into your variable x in the row above and now just need to write it into the global table:

settablenum("table1", row, col, x);

Be aware that you specified x as integer. If you want to read decimals from the Excel file, you would want to declare x as double.

I just noticed that you didn't change the default sheet name in the excelsetsheet command. Make sure it matches the name of the sheet you want to read from in your Excel file.

Good luck!

Ralf
FlexSim USA
FlexSim Germany

Also what Ralf mentioned: If you'll move the Flexsim file to another computer, other users might have a different file structure.

Think about using an "open file" -dialog to point to the file.

string filename = "temp";
string browsedirectory = modeldir();


if(stringlen(browsedirectory) < 3)
browsedirectory = documentsdir();
filename = filebrowse("*.xls","Excelfiles",browsedirectory);
if (stringlen(filename) > 0) // The user pressed OK
{
return filename;
}
else // The user pressed Cancel
{
return 0;
}

Hello,
Thanks a lot for the valuable suggestions. Now I got a new problem.

The X is giving me a weird number. Here are the out for x
row=1col=1x=8950556
row=1col=2x=8950556
row=1col=3x=8950556
row=1col=4x=8950556
row=1col=5x=8950556
row=1col=6x=8950556
row=2col=1x=8950556
............
and so on

any suggestions?
FYI: The excel table has simple integers in it

Ardodul,

please use excelreadnum() instead of excelread(). The later one is depreciated...
That leads me to the question in which FlexSim version you are working? The original template code from your first post is not from the latest FlexSim version and the way we import from Excel has changed in between. Everything Jens and I were suggesting applies to the latest version 6.

Please also take a look into the command help to figure out, what the single commands do. All Excel related commands start with "excel...()".

Good luck!

Ralf
FlexSim USA
FlexSim Germany

I am using flexsim version 5.
When I used excelreadnum(), the values become 0.

OK. I got the solution at last. I need to put double slash(\\) instead of a single slash (\) while mentioning the excel file path in excelopen() command.
excelopen("C:\\Users\\rahmaa5\\Desktop\\ordertable.xls");