Export data into MSExcel

Archived Topics
1

I have an application where the user enters the data in a window and I would like to log the user data in an Excel application. I would like to log each data point in a specific cell in the Excel spreadsheet. Is this can be done from FPMI and if so how?

2

There are several ways you can do it. For starters, check out these threads:

http://www.inductiveautomation.com/forum/viewtopic.php?f=23&t=3132&start=0&st=0&sk=t&sd=a&view=print
https://www.inductiveautomation.com/forum/viewtopic.php?f=23&t=3165
https://www.inductiveautomation.com/forum/viewtopic.php?f=23&t=2341

3

Is there any way we can specify cell in the spreadsheet to which the data should be logged to from FPMI?

4

I just have to say that this doesn’t exactly like a very good idea. Is there some compelling motivation for this? Sounds like you need a DDE logger or something…

5

It sounds like you want to write the data in an XLS format file. You can try to do it but it’s non-trivial, to say the least. Here’s a link to a document that describes the many XLS formats:

http://sc.openoffice.org/excelfileformat.pdf

I think a simpler approach would be to develop a VBA macro in Excel that reads the CSV file and formats the data as you desire.

6

I’ve done a little digging on the Internet and have discovered an even simpler approach. It’s harder to describe than to do but here’s a go…

  1. Export the data to a CSV file as originally suggested.
  2. Create an Excel workbook with the particular cells of interest containing VLOOKUP functions that reference cells in the CSV file.

The cell formulas could be similar to this:

=VLOOKUP(3,‘C:\Documents and Settings\Username\Folder[exported.csv]exported’!$A$1:$E$10,2)

In this example, the result would be the value in the second column of the range A1:E10 of the CSV file and from the row with 3 in the A column. (Refer to the Excel’s help on the VLOOKUP function for the specifics.) Note this will only work correctly if the values in the 1st column are in ascending order.

You don’t even have to use the VLOOKUP function. You can just reference the cell in the CSV file. For example:

=‘C:\Documents and Settings\Username\Folder[exported.csv]exported’!$A$5

which references the value in cell A5 of the CSV file.

When setting up the Excel workbook, it’s best to have the CSV file open so you can point and click to enter the cells and ranges. When the CSV file is open, the formula will not contain the name of the file (i.e., “C:\Documents and Settings\Username\Folder[exported.csv]”). But once you close the CSV file, Excel will automatically add the filename to the formula.

When you open the Excel workbook without opening the CSV file, Excel will burp, telling you it can’t update external references, and will prompt you to use the Edit->Links menu option to open the CSV file. You can follow their suggestion to open the CSV file but my experiments showed that the workbook cells are updated anyway - whether the CSV file is open or not.

Give it a try and see if it works for you.