add data to new column in excel file

[ransher]

Hi,

 i want to add the data to new row everytime in the worksheets whenever the vi is run instead of replacing the old data .Your help will be really appreciated.

i have attached the vi for reference.

Thanks,

Ransher

[Bob_Schor]

---

@ransher wrote:

 i want to add the data to new row ...

---

Your title says "add data to new column ...".  If we don't know whether you mean "row" or "column", or maybe both, or maybe either, it is difficult to make a useful suggestion.

How much experience do you have with the Report Generation Toolkit?  Have you looked at the Examples?  Have you searched this Forum for any "Revised Examples"?  [If you do a search on the Forum, just look for "Revised"].

Your attached VI suggests that you have almost no experience with the RGT (you do realize you are writing one row on Worksheet 1, then the next row on Worksheet 2, then a row on Worksheet 3, etc., don't you?  I'm 90% certain this is not what you want to do ...].

Bob Schor

[ransher]

Hi Bob ,

Your attached VI suggests that you have almost no experience with the RGT (you do realize you are writing one row on Worksheet 1, then the next row on Worksheet 2, then a row on Worksheet 3, etc., don't you?  I'm 90% certain this is not what you want to do ...].

---

i don't have much experience on report generation toolkit.

but i want exactly the same u mentioned above. For every iteration i want seperate worksheets and add the data in every worksheet instead of replacing it .

Thanks.

[Bob_Schor]

---

@ransher wrote:

Hi Bob ,

Your attached VI suggests that you have almost no experience with the RGT (you do realize you are writing one row on Worksheet 1, then the next row on Worksheet 2, then a row on Worksheet 3, etc., don't you?  I'm 90% certain this is not what you want to do ...].

---

i don't have much experience on report generation toolkit.

but i want exactly the same u mentioned above. For every iteration i want seperate worksheets and add the data in every worksheet instead of replacing it .

---

This still doesn't make sense.  Forget about LabVIEW for a moment, and let's think about saving data in Excel.  Let's say we have 6 Instruments, make one reading of each every minute, and want to save the readings for further analysis.

What makes "sense" to me is to put all the readings on a single WorkSheet, with Row 1 being Reading 1, having perhaps 7 columns, being "Time of Reading", "Instrument 1", "Instrument 2", ... "Instrument 6", and Rows 2 .. N being all the other Readings that I took in that session.

If I repeated the study, I might choose to make a new Excel one-WorkSheet file (who knows if the Instruments are the same?), or if it is an exact replicate of the earlier study, I might use the same format on a new WorkSheet.

What does not make sense to me is to put one reading on each WorkSheet (from the standpoint of Excel).  From an Excel point-of-view, working within a Spreadsheet is much simpler than working across Spreadsheets.  The reason we have Rows and Columns is usually to do exactly what I described at the beginning, take multiple measurements (Rows) using the same "channel" (Column) setup.

Why don't you attach an example of the Excel File you hope to create?  It would help if you "decorate" it by providing Row, Column, and Worksheet "Headers" so it is obvious to us what data goes where.

Bob Schor

P.S. -- did you look at the Examples, as I suggested?  Did you find the 'Revised "Generate Excel Report" Example' in this Forum?  Do you need help adding a row-at-a-time to a WorkSheet?

Bob Schor

[ransher]

Hi Bob,

Let me explain the things suppose we have an instrument that read data from 20 different but the same type of devices and format of data(string) is the same for all devices. Now what I want is to save the data in excel workbook with a separate worksheet for every device. My application should keep on adding the data to the respective worksheets whenever the vi is run or data is read from the devices.

The only problem I am facing now is unable to add the data to respective worksheet or you can say to the next row everytime the vi is run inspite of replacing/recreating the worksheets.

---

P.S. -- did you look at the Examples, as I suggested?  Did you find the 'Revised "Generate Excel Report" Example' in this Forum?  Do you need help adding a row-at-a-time to a WorkSheet?

Bob Schor

---

I have checked 'Revised "Generate Excel Report" Example' but I don't find it helpful.

---

Why don't you attach an example of the Excel File you hope to create?  It would help if you "decorate" it by providing Row, Column, and Worksheet "Headers" so it is obvious to us what data goes where.

---

I don't have excel file but the format of data is the same for every count.

Thanks,

Ransher 

[RavensFan]

Why would you have a hard time with this?  Write down the steps.

1.  Open the workbook.

2.  Figure out which sheet you want to write to.

3.  Get a reference to that sheet and open that sheet.

4.  Figure out what is the last row in the sheet.

5.  Add data to the row after that.

6.  Save and close workbook

[ransher · Accepted solution]

Hi Bob, 

I have created the Vi I wanted to make. Hope, this will help someone searching for the same thing. 

Thanks for the help.

Thanks,

Ransher

[Bob_Schor]

Hello, Ransher.

     RavensFan gave you the Algorithm that you need to use, but some of the Steps are a little bit obscure (the Documentation for the Excel RGT is not as clear at it could be).  Let's take his steps one at a time:

1. Open the workbook.  You basically "know" how to do this, but you want to add to an existing Excel Workbook, which means that you need to wire the WorkBook to the "Template" input of New Report.  NI does not explicitly tell you that this is how you do an "append", but there it is ...
2. Figure out which sheet you want to write to.  First, you need to know a little about LabVIEW and Excel.  When LabVIEW opens a new Excel Report, by default it has three WorkSheets, named (imaginatively) Sheet 1, 2, and 3.  You will probably want to come up with names of your own, possibly based on the name of the Instrument whose data will be placed on the Sheet (or any other unique identifier).  So figure out how you really want to name the Sheets.  You might need to do something "special" when you first open a new WorkBook ..
3. Get a reference to that sheet and open that sheet.  Following the logic in Step 2, you know the name of the WorkSheet you want.  Excel Workbook Properties will give you a Count of the number of WorkSheets in the current WorkBook, and Excel Get Worksheet will get you a specific WorkSheet, with Excel Workbook Properties returning the current Worksheet Name.  You can thus loop through the entire Workbook looking for the Worksheet you want, and if not found, use Excel Add Worksheet to add it to the Workbook, followed by Excel Rename Worksheet to set its new Name.
4. Figure out what is the last row in the sheet.  Use Excel Get Last Row.  You'll need to experiment (I never remember, so I always just "try it out") if this gives you the last filled row or the first blank row.  You also need to juggle Excel/LabVIEW indexing (Cell C1 is to LabVIEW Row 0, Column 2).
5. Add data to the row after that.  OK, RavensFan implies it is the last filled row, but I (uncharacteristically) follow Ronald Reagan's advice -- "Trust, but Verify".
6. Save and close workbook.  Use the name File Name for Save Report to File that you used as the Template Name for Save Report.  Don't forget to do a Dispose Report (to close Excel).

To which I will add that if you want to immediately create another Report, put a small delay (a few tenths of a second) between the Dispose Report and the next New Report.

Bob Schor

[RavensFan]

---

@Bob_Schor wrote:

1. Figure out what is the last row in the sheet.  Use Excel Get Last Row.  You'll need to experiment (I never remember, so I always just "try it out") if this gives you the last filled row or the first blank row.  You also need to juggle Excel/LabVIEW indexing (Cell C1 is to LabVIEW Row 0, Column 2).
2. Add data to the row after that.  OK, RavensFan implies it is the last filled row, but I (uncharacteristically) follow Ronald Reagan's advice -- "Trust, but Verify".

---

Yes.  I was going by a truly English written description of what to do, not necessarily exactly how the functions work in Excel.  In logical English, the last row would mean the last filled row.  Who knows what Microsoft does unless you try it out.  I do believe it would be the last filled row because when you do a Ctrl-End in Excel, it gives you the last filled cell in the spreadsheet.  However, what can throw that off is if you've gone and deleted some rows, doing a Ctrl-End takes you to what used to be the last filled row in the sheet.  At least until you happen to save and reopen the spreadsheet.  So I don't know how the RGT/Excel scripting would handle that in terms of "last row".