Jayme McColgan Posted August 16, 2021 Share Posted August 16, 2021 started playing around with VW2021 (were currently on 2020 at work) and the new excel functions. i cant get it to work. the vs.EXL_NewBook() function claims it worked (by returning a True value) but nothing is actually created... I'm trying to automate the kinda labor intensive process of creating a lighting patch sheet and step 1 is it creating an excel sheet that i can format... Quote Link to comment
JBenghiat Posted August 16, 2021 Share Posted August 16, 2021 Check your path. File paths are usually relative to the script location, unless you use absolute paths (i.e. c://… or \Users\…) Possibly the file won’t actually appear on disk until you call EXL_SaveAndCloseBook() Quote Link to comment
twk Posted August 16, 2021 Share Posted August 16, 2021 I have been using the python external libraries openpyxl and pandas dataframes for data exchange between vectorworks and excel. They have very good documentation. https://openpyxl.readthedocs.io/en/stable/https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html Quote Link to comment
Vectorworks, Inc Employee Ivaylo Stanchev Posted August 16, 2021 Vectorworks, Inc Employee Share Posted August 16, 2021 Hi, Because of the two Excel file formats - XLSX and XLS, there are two different programing codes for each of the formats. To know which one to execute we need to know the extension or the whole name of the file. Assuming that anyway, the user will know the full name ( hardcoded or taken from file chooser) we decided to use EXL_NewBook(filePath) instead of EXL_NewBook(fileExtension). EXL_NewBook(filePath) returns TRUE because the initial XLS or XLSX code was successfully executed. But the real file is created only after calling EXL_SaveAndCloseBook(filePath). Technically, an empty string could be used in EXL_NewBook(''). In this case, the "XLSX" code will be executed by default. But if at the end, the book is saved and closed as "*.XLS", the file extension will differ from the file format. And an error will appear when opening this file using the Excel application. Here is a short script example. I hope you find it useful. PROCEDURE TestExcel; VAR newname : STRING; res : BOOLEAN; shNum : INTEGER; BEGIN newname:= 'C:\\Temp\\Test.xlsx'; res := EXL_NEWBOOK(''); res := EXL_ADDSHEET('NEW_SHEET'); res := EXL_GETSHEETINDEX('NEW_SHEET',shNum); IF ( res = TRUE) THEN BEGIN res := EXL_SETCELLSTRING(shNum,0,0,newname); END; res := EXL_SAVEANDCLOSEBOOK(newname); END; Run(TestExcel); Best regards, Ivaylo. Quote Link to comment
Jayme McColgan Posted August 16, 2021 Author Share Posted August 16, 2021 11 hours ago, twk said: I have been using the python external libraries openpyxl and pandas dataframes for data exchange between vectorworks and excel. They have very good documentation. https://openpyxl.readthedocs.io/en/stable/https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html yeah i ended up just using the xlsxwriter module and it works great. Quote Link to comment
Jayme McColgan Posted August 16, 2021 Author Share Posted August 16, 2021 15 hours ago, JBenghiat said: Check your path. File paths are usually relative to the script location, unless you use absolute paths (i.e. c://… or \Users\…) Possibly the file won’t actually appear on disk until you call EXL_SaveAndCloseBook() the file path was correct and i tried with both vs.EXL_SaveAndCloseBook() and vs.EXL_CloseBook() with no success. i ended up using an external python module to achieve what i wanted since it works with VW2020 as well. Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.