why does report generator open all my excel files?

[mark-san]

My application uses vi's from the report generator toolkit to continually log data to an excel spreadsheet (actually, to several worksheets in the same file). This works fine, however the problem comes when we try to open/view any of the previously created XL log files (or _any_ other XL file, for that matter), because it is essentially impossible to read the file due to the constant blinking/flashing as XL writes to the log file. If you make the mistake of closing any of the open files, the app quits logging (may be able to trap this error, but haven't figured it out yet). Also, as you start closing files in XL, it seems that it has opened all (or very nearly all) of the files in at least the log folder, but in (some) other locations as well. The bottom line is that the only way we have found to view/process any of the XL log files is to halt the application - which is not really acceptable.

I'm running LabView 2011, in both development mode (Win7 OS) and distributed executable (Win Server 2008 R2 OS), and see the same thing. I have searched the internet and NI support site hi/lo looking for clues, but so for none of them have worked (XLSTARTUP, etc).

I have several other applications which use log files, and in all cases we can open/close active log files (read only) with no problems. Does anyone have any idea what could be going on here, and how to fix it?

Thanks,

Mark-san

[Bob_Schor]

Are you trying to read an Excel file while LabVIEW is busy writing to Excel?  If so, that's your problem.  The LabVIEW Report Generation Toolkit uses "hooks" into Excel to get Excel to do most of the work.  If we assume that there is only one copy of the Excel program in memory, then the same Excel code is being periodically called by LabVIEW to do writes while you are trying to use it to do reads (of other files).

[mark-san]

OK, so there are two Excel files which I have opened and am actively writing to, plus a template for each one, for a total of four files "in use", shall we say. But in that same folder, there are many other files which my LV app did _not_ open - from previous days logging, etc. I can understand why the files I am actively writing to would behave this way...but what about the other dozen (or 50...or more?) older XL files which I did not open? In fact there are even XL files in another folder (one level up) which are also being opened. I know this because if you open any XL file in either folder, and start closing, you have to go through a long session of closing many XL files, only four of which where opened by my app.

Or are you saying that ithis s the expected behavior, based on the 'hooks'...?

Thanks _very_ much for these comments.

[CharlieM]

Mark-San, 

I like to think of the report generation toolkit as another person taking over your computer and running Excel. That person doing what they want (in some cases, very frequently). This makes it tricky to be a second person trying to use Excel. 

Could you maybe log slower? For example, create a queue and every time you get data to "Log" just put that data in the queue. Then, you have another loop running and once a minute (or hour…) you do the report generation stuff that actually puts the data in the file. Once per minute… you empty the queue and do a bunch at once and the rest of your application can run at the speed it does with no changes. 

This might be a quick fix. I'm just afraid that even if we did find a low level ActiveX fix, it would not work on the next Windows OS or XL version.

Post back what you think!

[mark-san]

Hi Charlie,

I'm collecting roughly 250 bytes per second, so yes it is conceivable that this could be buffered for one minute or even ten (~150k bytes) without too much difficulty. But based on what I'm seeing now, as soon as I perform any RGT operation (not sure exactly which RGT VI is causing this to happen, but...) then all the XL files are opened, and here we go again with the operator having to deal with the issues described in the original posting. Or are you saying that in the intervening time between data dumps (Writes to XL file), there would be a way to completely close Excel and make it freely available (controllable) to the operator? But then if he lingered/viewed the file too long, until the next data dump, the conflict would occur (?).

At this point, I guess I just need to know if this is really going to be "life" with the RGT (and Excel), and that there is really no simple way around it. I am looking at a TDMS solution, as I believe I mentioned to you earlier, but that will entail a fair amount of new coding effort, and I just wanted to make sure that was the only/best alternative solution at this point.

Thanks,

Mark

[CharlieM]

Mark,

That makes sense.

What about logging directly to .csv, tdms or anything other than Excel. In general. The report generation toolkit is for one-shot report generation and not continuous logging. You're going against the grain here and while I'm only somewhat sure we could find a fix, I'm afraid a stiff breeze would cause a work around to fail. 

Perhaps using the "Write to Spreadsheet" for your logging (not 100% efficient but hopefully and easy drop in replacement). Then, have a button the user can push that causes the spreadsheet to get turned into a report .xls and is left open for the user to view. The program will go back to logging to the spreadsheet file and not interact with Excel. 

I left some details out of course but that's the high level idea anyway.

[mark-san · Accepted solution]

Charlie,

Well, I thought about using the Write to SS...but I didn't see an easy way to handle multiple worksheets. Would I need a separate file for each worksheet?

Although I like the .csv format since it is basically text format (readable), I think the TDMS format may be better suited because of the channel structure - thinking I can use one channel for each worksheet (?). But can I open a TDMS file (as read-only) while it is being written to, and then create the desired Excel output (snapshot) from there?

Mark

[Bob_Schor]

Mark,

     You "marked" your previous reply as the "Solution", but I don't see how you "fixed" your problem!  However, as I was reading the recent correspondence and Charlie's excellent analysis (which is consistent with what I wrote), an idea came to me that, I think, actually solves your problem.

     I took some code that I wrote that opens an Excel Workbook, reads rows and writes them back (adding some extra information).  While this is running, if you click on another Excel file to open it, you will definitely see "flashing Spreadsheets" as the LabVIEW Report Generator does its work.

     However, if you instead simply start the Excel program (from, say, the Windows 7 Start Menu), you will, indeed, get a second copy of Excel in memory, one which does not seem to be "interrupted" by the Report Generator.  Is this the functionality that you needed?

Bob Schor

[mark-san]

Hi Bob,

You are correct - I gave up too soon! I had accepted abandoning the RGT as a 'solution', and in fact already started down the path of using TDMS file format.

I very much like your idea - which we all should have thought of - and will try it this morning, and get back to you. And yes, it is exactly the functionality/solution I was looking for.

Many, _many_ Thanks! (and a Kudo if I can figure out how to do that - I don't use the forum very often)

Mark

[mark-san]

Bob,

Yes - your suggestion of opening the 2nd instance of Excel appears to work, on both machines (my Win7 and customers WinServer2008).

Great idea, thanks again!

Mark