Time Stamp incorrectly displayed in XLS file when Milliseconds is used

[Bob_Schor]

Seeing how complicated it was to write out Time information into Excel with ActiveX, I tried seeing what happened when you wrote Time using the Report Generation Toolkit alone.  I generated the current Date and Time with the Get Date/Time String function and created an array of the Date string, the Time string, and a concatenated string of <Time><space><Date>, deliberately putting Time first.  I used the Excel Easy Table function, which wants a 2D array, so I passed my 1D array through Build Array and wired it in.  I then Saved and Disposed my Test.xlsx file.

The file had the following contents:

3/27/2015

1:16 PM

3/27/2015 13:16

Note that these columns (as they are the only things in the columns) have been auto-formatted by Excel in various Date/Time formats, with the Time-Date string being shown in Date-Time order, as that's Excel's default for that column.  Although I wrote String data into these cells with LabVIEW, Excel recognized them as Dates and Times, and converted them appropriately.

Here is the code:

As you can see, this is very simple ...

Bob Schor

[chembo]

---

@Bob_Schor wrote:

Seeing how complicated it was to write out Time information into Excel with ActiveX, I tried seeing what happened when you wrote Time using the Report Generation Toolkit alone.  As you can see, this is very simple ...........

---

Bob,

The initial question in this thread was about changing automatically the cell format so that the milliseconds and the hours are visible. Excel shows only the minutes, seconds and milliseconds (mm:ss.000) by default. In my second post in this thread I included a code snipped which does exactly that and uses the Report Generation Toolkit.

I agree that it is easier to use the Report Generation Toolkit instead of ActiveX automation, but it is included in LabVIEW Professional and upwards. It doesn't need to be included in every LabVIEW version, but as a matter of fact, not everyone can use it.

I didn't really use the toolkit until now either, but I checked briefly the included VIs. It is not possible to hide the application, or at least I cannot find how to do it. The application windows can be minimized, but not hidden. Things like 'Conflict Resolution' are probably not available either. Maybe this is the also a reason why the application visible - the user can act on prompts. Prompts generate an error when Excel is hidden.

ActiveX automation requires more work, but provides also more flexibility. I think both methods are good, it all depends on the requirements and time constrains.

[chembo]

---

@Mitch_Peplow wrote:

Out of interest, where did you find the ring constant for the FielFormat when the input is a variant?..........

---

Workbook Object  ->  Workbook.SaveAs Method  ->  XlFileFormat Enumeration

[Bob_Schor]

Points well taken, Chembo.  I went back and looked at the original problem and the original post -- the "true" reason for the poster's problem is that he isn't writing to Excel, but is using Write to Spreadsheet (which, as has been said here over and over, is not the same as writing to Excel).

Second point -- the poster didn't understand Time formats.  He tried to use %X%3u, which for me produces 5:20:33 PM.514 (which is 100% correct -- %X is Locale-specific Time, followed by milliseconds).  If, instead, he used the format %I:%M%:S%3u %p, the result would have been 05:20:33:514 PM.  OK, so quibble about the leading 0 on the Hour ...

I'm uncertain what you mean by "hiding the application", but given that the original post didn't have anything to do with Excel, this may be a moot point.

Finally, if you do write actual (properly-formatted) time strings into Excel and Excel doesn't format them as you wish (for example, leaving off the milliseconds), I see three solutions:

1. Write to Excel ignoring the format, and if you need to see that level of detail, click on the column (I'm assuming all the times are in their own columns) and set the format as you wish.
2. Use a "Template" that has the columns pre-formatted.
3. Use the minimum ActiveX call(s) to just set the format on the selected column(s).

I do apologize for jumping in without carefully reading the entire string.  

Bob Schor

[chembo]

Bob,

What I mean by 'hiding the application' is that you can open the Excel application reference and set the 'Visible' property to FALSE. In this case the Excel window is not just minimized, it is not visible at all. The user doesn't notice that your application starts Excel in background.

In order to fully hide Excel you need to set also the 'DisplayAlerts' property to FALSE, which will suppress any potential prompts. Any action which requires user interaction, such as A file named 'my file ......' already exists in this location. Do you want to replace it?, needs to be resolved automatically by your application. In the 'file replacement' case, you need to set 'ConflictResolution' to 1 in the _Workbook/SaveAs method in order to override the file. Your application has to take care of potential prompts, in order to avoid errors.

I know that displaying the alerts is much easier to handle, but sometimes the project requirements force you to work more 🙂

---------------------------------

Mitch,

You need to set the 'DisplayAlerts' property at the very beginning, when you open the Excel reference, not at the end, in order to suppress the prompts.

[Bob_Schor]

Thanks, Chembo, I hadn't played with this Property.  I suspect (though I haven't tried it) that you could also do this if you were using the Report Generation Toolkit, something like this ...

Here I'm taking advantage of the Excel Get ActiveX References to get the Application Reference to Excel, while leaving the other References in the Class Wire.  I'm assuming (but I could be wrong, I haven't tested this) that it's OK to immediately close the reference, but if not, simply don't do it!  Note that the final function, Write Table, is just an example of "Now continue doing whatever you need to do with Excel".

I have a number of LabVIEW routines that do various things with Excel Workbooks, some of which are "evolving" things (I use a Workbook to control an experiment -- each row is another trial, and the columns are the hundred parameters that characterise the particular trial (or sometimes they hold some of the results of the trial), for which I keep the Workbook open so I can watch the experiment progress.  Other routines transiently open and close Excel -- I basically observe a "blur" as the code flashes through the rows and columns.  I never thought of trying to suppress the visualization (the second or so that Excel was open didn't bother me), but now I wonder about these "invisibility" tweaks.

I'm not sure I'd want to turn off "Display Alerts", however.  I do take some care to make sure that I won't get prompts that say "Do you want to overwrite that file?" by deciding, in advance, if I want to overwrite, and then "pre-deleting" an existing file, if necessary (I usually don't delete it, but rather I rename it by tacking on a unique numeric extension).  But it's always good to get new ideas ...

Bob Schor

[Mitch_Peplow]

Evening all,

Apologies once again for the seeming lack of response but as many of you will be able to appreciate people seem to be under the impression that I have the arm count of an Octopus that does not require sleep to function at 100% efficiency. If only that were true.

Many thanks to all the posts, really do appreciate your time and I can confirm that they have fully resolved all my issues so I'll reply to the posts now as I'm sure we can still clear a few of the niggling bits up.

RavensFan, this is a very valid point and it is something I have questioned before, however the answer I got initially some time ago sort of inferred that as long as you have obtained the reference, it doesn't matter if the prior node reference is destroyed straight away as it is only destroying the handle, not the actual reference to the property if that makes sense, i.e. as long as something else doesn't modify the reference that was just destroyed it will still work. In no way am I saying that's correct, I'm just relaying what was inferred so please don't gun me down . . . . .

I would however like some clarification on this so I may ping a SR to NI as it's something I want to ensure I'm doing correctly both for the sake of style and functionality. Unless anybody on the forums can clear this up?

chembo, your posts have been a godsend and have fully resolved all my issues. The link to the MS Excel properties was also another golden nugget. Finally the position of the DisplayAlerts; this solved the issue of Excel still asking me if I wanted to overwrite even after you told me that I needed to enforce a value of 1 to ConflictResolution property and that was the final piece in the puzzle.

Now I can take any .txt or .xls file and it will format the time into whatever value I request and save without prompting or warning. Yes I realise that can be somewhat risky, but for this application is fine and as pointed out in one post I could just append an incrementing numeric at the end for safety.

Bob_Schor, many thanks for your posts on the Generation Toolkit. I realise these functions save a lot of hassle and appear to have been designed just for this reason.

Your examples do appear to work from a quick try although I've not had chance to run them in anger, however I'm confident using the same properties and methods will yield the same result as using ActiveX.

Indeed, your posts and some of the others have made me more aware of the fact that writing to file directly (or via the write to spreadsheet function) is indeed not a native call to Excel but a standard delimited file command; this is something that I've never really pondered over as I've not ran into an issue such as this. 

One thing in your post though about using %X%3u. As far as I'm aware, this has been a standard time formatting option with LabVIEW for some time (possible from birth) and for me produces the exact result I need with no AM/PM (can't remember on the proceeding 0 for hours) which is why I wrote the string directly to file. I'm wondering if another property from the local machine time preferences is having an affect because why else would we get different results. That said, what property could change this?

I'm off work tomorrow so won't have chance to check any posts or reply till Wednesday but as usual cheers in advance.

Cheers

Mitch

[RavensFan]

---

@Mitch_Peplow wrote:

RavensFan, this is a very valid point and it is something I have questioned before, however the answer I got initially some time ago sort of inferred that as long as you have obtained the reference, it doesn't matter if the prior node reference is destroyed straight away as it is only destroying the handle, not the actual reference to the property if that makes sense, i.e. as long as something else doesn't modify the reference that was just destroyed it will still work. In no way am I saying that's correct, I'm just relaying what was inferred so please don't gun me down . . . . .

I would however like some clarification on this so I may ping a SR to NI as it's something I want to ensure I'm doing correctly both for the sake of style and functionality. Unless anybody on the forums can clear this up?

Cheers

Mitch

---

I won't. 

The order of references being closed is something I think I learned, and something I've tried to follow.  So seeing something that deviates from that is makes me think it is a potential cause of problems.  But maybe that isn't necessary.  Please update us with what you find out when you get this clarified.

[aputman]

---

@chembo wrote:

---

@Bob_Schor wrote:

You already found that doing Dispose Report closes the Excel Window.  I think the reason the Window is open in the first place is that when you do New Report, the first parameter is Window State, which, if unwired, defaults to "Normal".  I haven't tried this, but I suspect that wiring "Minimized" to this Control will also prevent the Excel window from opening.  Try it and let us know ...

BS

---

"Minimized" opens Excel window too, it is just minimized.

It is not the same as opening the Excel application via ActiveX property node with FALSE wired to the 'Visible" property. In this case it is good to wire FALSE also to the 'Display Alerts' property.

---

Setting the window state as "No Change" does just that.  You don't see anything happening. 

[Mitch_Peplow]

Hi all,

Right I pinged an SR to NI ding the correct procedure to closing off references and this is their reply:

"Typically you would close the references in the reverse order you created them but I believe you are right in saying that destroying a reference does not destroy the actual object it is referencing and hence you should be able to use methods that were invoked from that object. I have had a discussion with several CLA's in the office and can confirm that for most functions you will be able to access the sub methods even if the parent class has had its reference destroyed"

Hope this helps

Cheers

Mitch