Excel

[SJE]

I need to write continuous data from a measurement device to an Excel Template. The data is coming in at 1/4 of a second and is being sent to the excel sheet. My problem is that it need to start populating the column at row 20 column D and continue 256 times down to row 276.  I saw an example where the person made a VI in Active X that would move a string to any designated single cell. That would work fine except I've got an array of data to move. Does anyone know how I should go about editing this VI to move 256 lines of data to the D column starting at row 20?      

[SJE]

Is there a VI to overwrite a column of data into an excel template? I'm using LV 2019

[GerdW]

Hi SJE,

---

@SJE wrote:

I need to write continuous data from a measurement device to an Excel Template. The data is coming in at 1/4 of a second and is being sent to the excel sheet. My problem is that it need to start populating the column at row 20 column D and continue 256 times down to row 276.  I saw an example where the person made a VI in Active X that would move a string to any designated single cell. That would work fine except I've got an array of data to move. Does anyone know how I should go about editing this VI to move 256 lines of data to the D column starting at row 20?      

---

There are several problems in your VI…

• How do you define the range? In Excel you define ranges like "A1:A256", but you set a range of "A1" and a Cell1 parameter of "A256"!?
• Your FOR loop iterates 10 times, wait for a second in each iteration, but you only use the output of the last iteration? What's the point of the loop at all???
• I don't have MS-Office installed on my computer, so I cannot comment on that last Range->Item method. AFAIK I used different properties/methods to set values in an Excel worksheet… What I know: I was able to send an array of data to Excel to set a range of cells!

[Bob_Schor]

If you have the Report Generation Toolkit installed, the function Excel Easy Table does exactly what you want -- it writes a 2D array at a Cell Location (row, column).  Here is part of the Help:

I put a demo here in the Forum about a decade ago.  I just tried to run it, but it sneered at me that my version of Excel was wrong (I think the IT Department has been "improving" our Office Suites).  But if you find it (on the first page of this Forum, enter "Revised Excel " in the search bar) you should find it (the date should be 2014).  Until a few months ago, this still worked for me ...

Bob Schor 

[Bob_Schor]

I just went to another computer not managed by the IT department (my personal machine), downloaded the Demo from the Forum, and ran it.  Worked fine.  You can find it here.  I just tested it with LabVIEW 2021 and even with the obsolete "New Report" (it has been replaced by "Create Report"), it worked fine.  When you run it, you'll notice there are two three-row, two-column tables called "Date/Time/Operator" and "Test/Humidity/Pressure" (items "1." and "2." in the comments that appear as three-row/two-column entries in the final report, with the second set (as Comment 8 notes) "moved over 1 column".  Of course, instead of "moving over 1 column", you could also say "Put it in Row 0, Column 3" (note -- I'm just making up numbers here ...).

This absolutely does need the Report Generation Toolkit, but with this Toolkit, working with Excel becomes quite doable and a lot of fun.

Bob Schor  

[alexderjuengere]

without the report generation toolkit, manipulating an existing excel file is possible, but not a lot of fun 😉

001# 

- XLSX reader

- no Excel.exe required

- the .xlsx is unzipped, and then the work is done on the unzipped data

- you could use this as a starting point to derivate a .xlsx writer

- https://forums.ni.com/t5/LabVIEW/Darren-s-Occasional-Nugget-09-26-2022/td-p/4257604

002# activeX; requires Excel.exe

- XLSX Writer

If you have Excel install besides LabView, you can use the demo in

"..LabVIEW XXXX\examples\Connectivity\Excel" as a starting point.

003#  .NET; probably requires Excel.exe

- XLSX Writer

https://forums.ni.com/t5/LabVIEW/Excel-add-sheets-to-a-Workbook-using-net/m-p/3306749#M967898

[Michael_Munroe]

You absolutely do NOT need the Report Generation Toolkit.  The code you shared has some problems, but you are very close to a working solution.  I have posted many ActiveX VIs in this forum.

You define a range and clear it, but this range is not the same as the one you specified.  Change the Cell1 and Cell2 inputs to the range you want, clear it and then use the Value Node to write the range (making the clear operation unnecessary). The Range->Item you show is not what you want. If the data array size is different than the range, you will get an error.  

And as mentioned earlier, the for loop outputs only the last value.  Also, the build array and index array will only output the random number once, and then always output zero.  Remove both and build the array with the index and the value. 

The To Variant can also be remove so you can enable indexing and output a 2D array.  The Value Node will take the 2D array directly.

[alexderjuengere · Accepted solution]

---

@SJE wrote:

Does anyone know how I should go about editing this VI to move 256 lines of data to the D column starting at row 20?      

---

#1 open "..LabVIEW XXXX\examples\Connectivity\Excel\Excel - Write Table.vi"

#2 define offsets

#3 run .vi, observe excel

this will open a new .xlsx file, creating a 2d table at the offset defined. you will need to define proper limits for the offsets...

the number of rows of the pink 2d array defines the number of rows written via auto-indexing.

as this is creating a new .xlsx file you still need to figure out how to open an existing file, and getting the offset right each time, to properly append new rows....

Spoiler

Editing Individual Cells in an Excel File (3).vi ‏26 KB