how to use ActiveX to open/write to Excel template but not to overwrite it

[keng]

Hi there again (it's me...if anyone recognised me...)
I managed to use ActiveX to open Excel and make it visible onto the screen. But I would like to know how can I make Excel open an Excel template and let me write data to the cells without overwriting the original template. I did try to use another program source such as the EXCEL TOOLKIT but I can't still figure out how does it work for my case. Is there a simpler example for me to refer too? Please help.

Thank you,
juni

[Mike_S.]

After creating your workbook template ( just a blank .xls file) , use an Invoke node to use the Open Method on a Workbooks object and provide the path for the blank .xls file; when you're ready to save the file, use an Invoke node with a Workbook object, using the Close Method - you should be able to specify whether to save changes, and provide a new filename. Hope this helps.

[keng]

hi mike,
thank you for answering this problem. If the Excel template is not blank, can I use the same method which you have descibed here to apply to my problem? i would like to write data into certain cells and then save it to different filename without overwriting the original template ....automatically.The filename could be default as PC date or time (eg 12052002_1415...). So that I can viewed the file later....
any idea?

[Mike_S.]

Yes, it should work OK, just prepare a worksheet that is formatted how you want, with row or column headers, formulas etc., and save it with your template filename. Provide the path of that file to the Open method, and update only the cells necessary, and when you use the Close method use a different filename from a front panel control or auto-generate it as you suggested.

There is an example that ships with LabVIEW (should be in the examples section), Write Table to XL.vi - this has a lot of the code you might need, in this case it creates a new workbook in the Open New Workbook sub-VI - instead of the Workbooks.Add method, right click the invoke node and from the methods menu, choose Open, you should then be able to specify the filename for you
r template. Good luck, I hope this helps you out.

[keng]

Hi mike ....thank you for you prompt reply again.
I shall work on it soon. May I request your help again should I got stuck with what I am doing (as usual) ?

Thank you very much...
juni

[keng]

Hi Mike..
I have tried to use the examples provided LabView 6.0. I don't understand how to use the Range value to get the data from my global array and also how to open the template and write data to it.

At the moment, I could only open a saved dialog box and a default string filename is used to save my data into a new Excel but not the formatted excel which I want.

If possible, please help again. Vi attached.

[zilla]

Hi Mike and Juni,

I'm having the same problem. I want to save data to a given sheet in an excel workbook, over write any existing data, and save with user supplied file name. I'm looking at Juni's .vi to see if I can get on board with this and get it figured out.

thanks

[Mike_S.]

I've attached a zip file containing a VI that I think does what you want, also the sub-VIs, some of which are part of the excel.llb that comes with LabVIEW. I'm using Excel 97 - they should work with 2000 I believe.

I'm assuming that you would like your data to go directly to Excel, don't think you need the Write to Spreadsheet VI, it just creates a delimited text file. If you want to auto-generate the new filename with the Time/Date functions, just use that string output instead of the string constant I have used.

Good Luck!

[keng]

Thanks Mike for you example.
Yes, i would like my data go to directly to Excel. But I would also like the data to be written at specific cells in the Excel. Could you tell which part of the program should I work on it? And what are the functions.vi in the function pallete can I used to get those data from the "datarecords"?

I understand the example you have attached here (many thanks) but the the way the data is written into the Excel cells is not the way I want it to be. Any idea?


Oh regarding the Write to Spreadsheet Vi, yes perhaps I don't need it,instead can I used File Dialog.VI so that the program will let the user decide a filename for it (even though it is default with auto-generate filename using Time/Date
functions)??

No luck,
juni

[keng]

Hi Mike...

I have better luck this time. I managed to work out the data manipulation in the Excel. I apologize for the earlier statement, as I did not try harder. But this time, I managed to get that work.

Mike,
could you please help me again with a FILE DIALOG.VI. I would like to put this VI so that the user can choose to save with an auto-generated filename or input his own filename. I tried to use Excel Worksheet - Open Method = Close to solve it ...but it doesn't work. Could you please showed me where to put this FILE DIALOG.VI in my Excel example.

If you are willing to help out here, I am deeply grateful!
Thanks,
juni