Time Stamp incorrectly displayed in XLS file when Milliseconds is used

[chembo]

---

@Mitch_Peplow wrote:

The ActiveX properties may achieve the same outcome without opening the file but I need to have a play around. Unless somebody in the community has already managed to achieve this to save me some time perusing the endless list of properties available?

 

---

I compiled long time ago an Excel automation library from different postings, examples, etc... and posted it recently on the community pages. It has also an example so that you can see how to use it. It was working for me in many cases. It is far from perfect, but you can see at least the ActiveX calls which can be used to add open, close files, add content and some formatting.

https://decibel.ni.com/content/docs/DOC-39826

[Mitch_Peplow]

Morning chembo,

I had a go at using using both the Report Generation Toolkit functions and the ActiveX nodes. As per aputman's and your suggestion the report generation functions do indeed work and are very simple to assert. Using the dispose report function at the end allows you to inhibit the file opening which I was experiencing.

I also managed to perform the same operation via the ActiveX nodes before you had posted the link to the library but I've downloaded it and this is a really neat and functional library you have made. Even if it isn't perfect it (I've not ran it in anger to find out whats wrong with it), it clearly shows how  the  calls to Excel can be made for various operations.

I'm just glad that there is indeed an easy way to automate this, even if it means doing it once at the end before the file is finally closed of as my assumption is the act of formatting a cell on the fly is somewhat time consuming, however that's just a guess so I'm all ears if somebody can assure me it takes no longer to format a cell as it does write to one.

Cheers all

Mitch

[Bob_Schor]

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

[chembo]

---

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

[Bob_Schor]

You are absolutely correct.  "Dispose Report" has the code that actually closes Excel.  [Personally, the name of this function, "Dispose Report", seems like a misnomer to me.  Indeed, its icon shows a trashcan, as though you were really disposing of your report!  What you are really doing is simply stopping the "helper routine" that was doing all of the work of creating your report -- maybe a better name for this function would be "Dismiss Helper" ...]

Bob Schor

[Mitch_Peplow]

Morning all,

Ok, this one isn't completely cleared up and I need a bit more help.

I have got the cells formatting as desired and then attempt to save and quit, however whilst it seems to save without any errors, when you open the workbook again the saved formatting options have either not been saved/applied and appear to revert back to default. The question is why and how can I stop this?

I have attached the VI that performs the operation and also the .xls file. As you will see the excel file is very simple and is purely time against a thermocouple reading. The VI is currently set to make Excel visible and not quit to ensure the formatting is actually taking place which it appears to do without any issues.

Interestingly, once the Vi has finished running and the formatted excel file is open, if you do a manual 'Save As' and choose 'Excel 97-2003 Workbook' as the file type, it saves the file and retains the custom time formatting that's just been applied? Ive tried using the 'SaveAs' ActiveX property but this also did not appear to work, however if hoping its just that I've not set up the node correct i.e. I need to set one of the other parameters apart from Filename

Any ideas or miracle cures?

Cheers

Mitch

[chembo]

Your file is in plain ASCII format and Excel saves it in the same format again, so no formatting at all.

You need to save it in Excel format first and then your VI will work. Or use the SaveAs method, instead of just Save  and specify the format (see attached VI). It works on my PC with Excel 2007.

[Mitch_Peplow]

Afternoon chembo,

This is great news, I knew the SaveAs would be the answer, I just didn't know what portion was wrong. 

Out of interest, where did you find the ring constant for the FielFormat when the input is a variant? Also, currently excel still asks me if I want to overwrite the original file; is there an input to accept this automatically without warning/prompt just like the DisplayAlerts property node?

Cheers

Mitch

[RavensFan]

In your VI, you get a reference to a sheet, then close it right afterwards.  Same for the range, and the workbook and so on.  Maybe that works,  But my understanding is that you should close your references in the reverse order that you created them.  For instance, closing the workbook right after you've acquired the worksheet, but before you've really used it can cut off your access to the worksheet reference.

I'm wondering if closing the references in the wrong order might be the source of your problem.

[chembo]

---

@Mitch_Peplow wrote:
.......

Out of interest, where did you find the ring constant for the FielFormat when the input is a variant? Also, currently excel still asks me if I want to overwrite the original file; is there an input to accept this automatically without warning/prompt just like the DisplayAlerts property node?

 .........

---

I found the information on a Microsoft website about Excel automation, but I cannot find the link right now. I will post it later today.

I believe that I created the ring constant myself using information from the website.

You mentioned, that you downloaded already the Excel automation library which I posted a couple of months ago. Look at the SaveAsXL_Workbook.vi. You need to wire 1 to the 'ConflictResolution' property in order to avoid additional prompts.