LabVIEW
How to write data to a protected Excel worksheet (with unlocked cells)?
Solved!
Use the Get ActiveX References VI and wire up the workbook reference to an Invoke Node with the Unprotect function selected. After the changes are made, connect it up to a Protect function with the same password. The "magic" is quite simple. This is for the workbook object, which is the entire Excel file. There is also a Protect Worksheet node that works in a similar fashion but you have to keep in mind that there can be more than 1 worksheet in a workbook.
@mcduff wrote:
I know this is an old topic, but unsure how you use the Get ActiveX reference to unlock an excel sheet. If I open a new report with the password protected excel file as a template, then a dialog appears asking for the password. Am I doing something wrong?
Hello mcduff,
The method to unlock a sheet is not the same as what you are trying to do, if I understand you correctly. The password that you are talking about is a password to prevent the file from being opened. It is entered in the SaveAs dialog box.
To open a password protected file using ActiveX, the Workbooks.Open function has a place to input a password. However this parameter is not exposed with the RGT. You can drill down in the New Report VI and find where the workbooks.open function is called but I think you're out of luck to use it unless you are willing to modify the RGT code (do so at your own peril 
). I tested it and it does work.
@mcduff wrote:
@BobSchor
Your knowledge of the Report Generation toolkit is unparalleled, is there any way to save data to excel without opening and displaying the application window? Me thinkls not.
Wrong on two counts -- I'm sure there are "parallels", and you "sort-of" can save data without obviously (or, maybe, "flauntingly", if that's a word) opening Excel. You do, in fact, need to open Excel as it is the program doing the work, but when you do the New Report, you can set the Window State to "minimized", and Excel will (most of the time) not "pop up".
Do you notice the hand-waving language in the previous paragraph? I have a routine that reads some data (text) files, processes these data into a series of Excel Worksheets (named for the Station from which the data comes, e.g. Station 01, Station 23, etc.), creates a nice table, and (if that's all I did) never shows that Excel is running. However, I just had to "gild the lily" -- I wanted to create two Graphs plotting some of the data, which necessitated reading from the just-written Worksheet and then adding an Excel Graph -- this process caused an image of the Excel page to "flash" (appear for maybe a few tenths of a second) for each Worksheet processed. The "positive spin" on this behavior is it acted as a Progress Bar -- the program swallowed a bunch of data, chewed on it for a few seconds (may 10-20), "silently" opened Excel and wrote the Worksheets, then made a second pass where it read the Worksheets, made the graphs, and "flashed" a glimpse of the Workbook, taking maybe a second per Worksheet.
Here's an example of the Excel output:
Bob Schor
Your knowledge of the Report Generation toolkit is unparalleled, is there any way to save data to excel without opening and displaying the application window? Me thinkls not.
Cheers,
mcduff
It is possible to do it directly with ActiveX but the Application.visible property is not made accessible with the RGT Create Report.vi. As an alternative you can open your template with the application window minimized and then immediatly after make the window not visible as in the following example.
Ben64
