I have an example of Excel calling a Tcl script which creates a csv data file which is then loaded into Excel as external data.The path of the Tcl script and the csv file that the Tcl script is to create are set in the spreadsheet cells.When the 'update' button is clicked the Tcl script is executed then the external data table in the spreadsheet is updated.It's not 100% automated but it's a good start.There are still problems with error handling - e.g when you slect 'cancel' when selecting the csv file for the external data update.I needed this as I have a Tcl script which parses test result log files and creates a csv summary file which I load into an external reference table in Excel and from that update test result status in another worksheet. I have very little patience for repeatedly running the script then updating the Execl external reference so I wanted a single button click update. I haven't quite achieved this as I still need to confirm the opening of the csv file when updating the Excel external reference. I've very little knowledge of VBA so this has been a learning experience.To download go to http://tcl.glennh.com
and save demo.xls and demo.tcl to a local directory.To run:- Open demo.xls
- Change cells B23 and B24 to reflect the path where you stored the files
- Click 'Update'
- The tcl script is run, using the value of B23 as the path for the script and B24 as the name of the output file the script will create
- - A message box is displayed confirming success or failure of the script
- Click OK
- A window appears where you select the file to be used to update the excel external reference
- - Select the file created by the tcl script
- The table in the spreadsheet is updated
- - Note Row 1 is clock seconds so this should change each time
UPDATE:Lauri Ojansivu has kindly sent me an updated excel file which runs the tcl script and updates the spread sheet with a single button press. You can get this from the URL mentioned above 'demo-2.xls'.Glenn Halstead'demo-2.xls'? Where?
Here is modified Excel file & original Tcl file: http://www.xet7.org/tcl
Some notes about modifications:- that csv reading code modified from http://www.vb-helper.com/howto_csv_to_ragged_array.html
- csv code can read larger csv files too - more rows and colums, easiest way is to add new Excel sheet and change code line near the bottom to:
Worksheets("Sheet2").Range(Trim(Chr(Asc("A") + C)) & Trim(Str(R + 2))).Value = _Lauri Ojansivu