How to create a New Excel File programmatically

[Reeves7]

Hi !

I am documenting acquired data continuously in an excel file, what I need to do is to create a new excel file automatically when the size of the previous file exceeds a certain limit lets say 1MB.

I have tried using the simple 'Create/Open File' function and the 'Write to Spreadsheet File' function to create the new excel file. I does create a file but when I open the file it says the format of the file is not what you have opened it with.

Also when the new file is created, three new worksheet are to be added programmatically with pre-defined worksheet name and also Column headers for each of the worksheet are to be added.

I am also using 'Labview Report Generation Toolkit for Office'.

Regards,

Reeves

[Aldo_A]

Hi Reeves,

When creating Spreadsheets and adding worksheets, here is a great tutorial with downloadable code which can help you in your application http://zone.ni.com/devzone/cda/tut/p/id/3179

In specific, make sure you follow the programming examples on creating a new spreadsheet, it sounds from your application that you are able to create the sheet, but when you write data to it, there might be a corruption. If possible can you post the error if the issue continues?

The tutorial has four parts but focus on the first one where it outlines the basics of using the Report Generation Toolkit.

Hope this helps!

[ben64]

If you are using the Office Report Generation Toolkit you can create a workbook template (.xlt) that already contain pre-defined worksheets name and column headers. To know the filesize use the "Get File Size" function (File I/O palette -> Advance File Functions).

Ben64

[Reeves7]

@ Aldo A

Thanks for the reference to tutorial, I didn't know about them. They are useful in general but unfortunately they don't answer the problem of creating an altogether new excel file programmatically.

@ ben64

The template thing simplifies the issue of formatting the new file. But I am still working on the new excel file creation challenge.

Let me put my problem in a different way.

Certain data is being acquired continually and is being written to a certain excel file, when the size of the file exceeds lets say 1MB the program should start saving the data in a NEW FILE instead of the previous file.

I have attached the VI to explain what I am trying to achieve here.

Reeves

[Reeves7]

I have tried using the 'Save Report.vi' in the Office Report Generation Toolkit, but it gives the error, "error 5 File Already Open since I haven't closed the report yet. So this doesn't work either.

What other method can be used to save the report in another file?

[MoReese]

When the report gets to be a certain length, close the file and rename it using the copy function.  You can also use the system32.dll move function, but that's up to you.  I normally append a date/time stamp to the name of the file so I don't have to worry about sequencing the file name.  I have a VI that does this very thing when a file gets too large. I will try and remember to post it tomorrow when I get into work.

[Reeves7 · Accepted solution]

@Reese

Thanks! I would like see the solution of copying the file and renaming it.

P.S.I was able to create a new excel file myself and I have attached the solution in this message.

Reeves

[MoReese]

Here it is.  It is setup to rename the file when it gets to be a certain size.  It's LV10.  Let me know if you need an earlier version.

[1der-er]

IS IT POSSIBLE TO DUPLICATE THE FILE(.xls)? 

OR CREATE SERIES OF FILE(.xls)

[iradovanovic]

@ 1der-er

Do you mean while the one VI is running?