LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

How to write data to a protected Excel worksheet (with unlocked cells)?

Solved!
Go to solution

Hello!

 

I have LabVIEW 8.2 and I often use the "Report Generation Toolkit for Microsoft Office" to save data in Excel worksheets. Up to now I have only used unprotected sheets.

 

But at this moment I have a protected Excel worksheet with some unlocked cells. If I open the file using Excel I can fill these unlocked cells without any problems. However, I'm not being able to fill these unlocked cells using LabVIEW and the toolkit. So I got some questions:

 

1) Is it possible to use LabVIEW to write data to a protected Excel worksheet even if it has unlocked cells?

2) If it is possible, how do I do that?

 

OBS: I noticed that if I unprotect the sheet I can write data to the file. But I want to avoid unprotecting and protecting the sheet, since that the users won't have the password.

 

I hope I have made myself clear. I really appreciate any help you can provide.

0 Kudos
Message 1 of 9
(5,753 Views)

Sigh.  I forgot about what a hassle Excel can be.  Are you writing individual cells, and know you are writing only to unprotected cells?  This may require quite a bit of ActiveX "magic" ...

 

Bob Schor

Message 2 of 9
(5,744 Views)

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.

Capture.PNG

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
Message 3 of 9
(5,710 Views)
Solution
Accepted by WKAriyoshi

Thanks for the replies.

Using ActiveX I was able to write cells individually. By doing that I was able to to write in the unprotected cells without any problems. \o/

 

Probably the toolkit was activating somehow the overall protection of the worksheet and Excel wasn't enabling the writing.

 

I had never used ActiveX before, so I had to learn it from zero. I was looking for some tutorials over the internet but I didn't find much easy information. What helped me most was an example that comes with LabVIEW named "Excel - Write Table.vi". After some tests, I was able to open my protected worksheet and fill data in the unlocked cells.

 

Thanks guys!

Message 4 of 9
(5,667 Views)

@aputman

 

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?

 

@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.

 

Cheers,

mcduff

0 Kudos
Message 5 of 9
(4,710 Views)

@mcduff wrote:

@aputman

 

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 Smiley Very Happy).  I tested it and it does work.  

Capture.PNG

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
Message 6 of 9
(4,705 Views)

@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:

Excel Output.png

 

Bob Schor

Message 7 of 9
(4,699 Views)

Check out the solution in this post to address the annoying flash of Excel when creating a graph.  It requires much more effort to create the graphs and charts but they are much nicer looking and Excel does not flash up on the screen (stays minimized).  

 

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
Message 8 of 9
(4,697 Views)

 

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.

 

open Excel not visible.png 

Ben64

Message 9 of 9
(4,692 Views)