LabVIEW
Please suggest how to speed up data write into Excel report from template
Hello Bob,
thanks for sharing this detailed example and explanation.
There is a function, though, I wasn't able to find (this filtered increment for column/row only):
What is the name of this one?
If I unerstand it right (or am I totally not following?) you are advocating an idea of creating report from a blank Excel workbook. Well, that's probably a good way in most cases but I still think that in my case it's not the most handy solution.
I tried to describe the situation in my first post in this thread, maybe I couldn't make myself clear enough (sorry, English is not my first).
I'll give it another try. There is a reason I prefer to use Excel template prepared and formatted before I feed it to Labview to append user-filled in data, calculated and measurements data. Preparation of my Template includes assignment of Cell names (I mean user defined names, not standard A1 or C5 names) like here:
The good thing I see about this user cell names is that I can move those cells with pre-defined names around in my template and it wouldn't require to change LV code to put data in the right places. And it really happens that I need to add a constant line or a constant picture in my Template. To do this I just have to open report template manually, make nesessary corections and save it. In case of code change it takes much more effort because stand-alone applications (.EXE) are used on several lab PCs.
This is the reason why I find it more convinient to use prior prepared Template with the constant core and auto-append variables only.
And using those pre-difined cell names also serves good in later processing of reports to get the required data for a differrent tasks.
Hello, Alexander.
The "strange function" you mention is actually a "Structure" called an "In Place Element" (you can find it on the Structure Palette). What I'm doing here is taking a Cluster representing the Excel "address" (Row, Column), unbundling it to get the "Row" (or "Column"), incrementing it (to the next Row/Column), then bundling it back into the Cluster.
If you look at my code, you'll see that in Step 7, I write a 2D section of the Report, and want to write a second 2D section just below it. I basically want to take the Row/Column cluster "next cell bottom-left" of the first block, add one to the row (move down one row) and use the new Row/Column cluster in Step 11, wiring it in to "start (0, 0)". Here is the Unbundle/operate/re-bundle operation the "old way" and the "In-Place" way:
Thank you for explaining your idea of creating a Template and using "Cell names" to place items "where you want them" in the Template.
I've not worked with Cell Names the way you are doing it. I've built Excel Workbooks to handle Data Arrays, and have built on a second WorkSheet something probably similar to your Cell Names, row/column addresses on the real Data WorkSheets where I'll load the data. I also usually build row/column headers at Run-Time based on the size(s) of the data going into the WorkSheets.
The bottom line is I design the entire WorkSheet (and WorkBook) first, and, at run-time, rather than read a Template, I use the information from the Data I'm trying to save to design the Report.
But that's one of the virtues of LabVIEW -- two people can solve a task ("Write a Report") in two different ways, based on their own experience and requirements, and LabVIEW provides the tools.
Bob Schor
Hello David,
I ran the code for registry update you attahced, got the message of successful update, then reboot my system.
Unfortunately it didn't help in my situation. I still have no control of ActiveX property and method nodes.
@AlexanderU wrote:
Hello David,
I ran the code for registry update you attahced, got the message of successful update, then reboot my system.
Unfortunately it didn't help in my situation. I still have no control of ActiveX property and method nodes.
Interesting, up to now I was sure that the bittness of the Office and LabVIEW/Report Gen must be exactly the same, but this trick with registry works for me, initially I have had the same missing properties, but after patch they are available also in 32-bit environment.
The very first thing which you should check is where the Excel is installed on your machine. By defulat it should be in "%ProgramFiles%\Microsoft Office\Root\Office16\EXCEL.EXE", but may be you have a different location, then this patch will not work, because the path is hard coded, you will need to correct it or enter this key and values manually in RegEdit.
Well, in my case after changing registry Labview can't even open Excel workbook anymore and keeps giving Error -41106.
Repairing of MS Office package didn't help. Now I'm thinking of Windows sytem restore.
I suppose MS Office is installed in default folder:
An ActiveX Automation server can be invoked inside the calling process or outside the calling process. But the ActiveX server library has to explicitly support those. The MS Office ActiveX servers in principle do but the mentioned ClickToRun installers usually do not activate that option and only enable the ActiveX Server interface for the bitness the installer package is.
When both the ActiveX server and client are the same bitness the ActiveX server component can be invoked in-process (similar to a DLL function call) and all is well. When the bitness doesn't match, ActiveX invocation logic allows to instantiate the ActiveX server in a proxy process which then communicates with the ActiveX client interface through Interprocess Communication. This works amazingly well and almost seemless, if the ActiveX server was properly registered for out of process invocation. But the ClickToRun installer doesn't do that.
Of course out-of process invocation is slower, as every single method and property access needs to be marshalled and transferred through the Interprocess Communication link and then unmarshalled on the other side and then all back again!
The difficulty is that only the creator of an ActiveX Automation server component really knows for sure if it is out of process instantiable, and the fact that Microsoft did choose to not enable that feature can indicate that there are actually possible problems with it. Or it is just their hidden agenda of discouraging 32-bit applications altogether, who knows? In the past, doing a Repair Install after using the ClickToRun installer, magically seemed to fix the registry to enable the MS Office ActiveX Server for both 32-bit and 64-bit. But there seems to be a chance that Microsoft "fixed" that in recent office installations.
The necessary registry entries are also not trivial and of course need to match the actual installation perfectly, both in terms of what paths it registers as well as what GUID's it uses.
The path the registry patch uses is: "C:\Program Files\Microsoft Office\Root\Office16\EXCEL.EXE"
The path your office seems to be installed (and where mine used to be usually) is: "C:\Program Files\Microsoft Office\Office16\EXCEL.EXE"
@AlexanderU wrote:
Well, in my case after changing registry Labview can't even open Excel workbook anymore and keeps giving Error -41106.
Repairing of MS Office package didn't help. Now I'm thinking of Windows sytem restore.
I suppose MS Office is installed in default folder:
I don't think you need to perform System Restore, just revert paths back in the registry according to your Excel installation, that is.
It is always good idea to save registry branch before any manipulation.
