DIAdem : Writing Data to Excel

[himanshu25]

Hi,

I did some calculations in DIAdem and are stored in separate channels(Arithmetic Mean, Standard Deviation, etc...).I want these values to be in Excel in a particular format. I tried to use Excel plugin but it is not working and I am not able to write data in the channels created by Excel plugin.

Please convey.

[Brad_Turpin]

Hi himanshu25,

If you're in an interactive session, you can copy columns from a DIAdem VIEW table with <Ctrl-C> and paste them into an open Excel worksheet with <Ctrl-V>.

You can save the selected channels to a temporary *.TDM or *.TDMS data file, then use the Excel TDM Addin to load the *.TDM/S data file into Excel.

You can save the selected channels to a temporary *.csv or *.txt data file, then use the Excel ASCII file importing functions to load that data into Excel.

You can create a VBScript that communicates with Excel using ActiveX and sends the data channels to an Excel worksheet programmatically.

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[himanshu25]

Hello Brad,

Thanks!!!!

I have a *.TDMS file and after performing some calculations I have some new channels. I want to export the calculated data to an Excel file. The last option looks good but i don't know how to do that and I can't provide the data.

However, I have attached a similar excel file which I need as an end product.

It will be a great help for me if I can get a VB Script on how to write to an excel file.

Have A Great Day

Himanshu Sharma 

[gsklyr]

Here is a script I used to use when I needed to verify some test type before loading it into DIAdem.  

The reading/ writing into excel function is at the top, and I use it at the bottom of the script.  It used to be only for reading a single cell value (that is why it's name getSingleCellVal) but I added writing for this purpose.

'-- Comment: Function to return a value off a single cell in and excel file (or csv) and replace it with another value
'-------------------------------------------------------------------------------
Option Explicit  'Forces the explicit declaration of all the variables in a script.

DIM SUB_NAME, FSO
Set FSO = CreateObject("Scripting.FileSystemObject")

' READ A VALUE OF AN EXCEL / CSV SHEET
' ARG 1: The path of the file to be read
' ARG 2: the row containing the value of interest
' ARG 3: The column containing the value of interest
' ARG 4: A1 range string
' ARG 5: The new value to place in the A1 cell
' RETURN: Returns the old value of the cell
' NOTES: n/a
Function getSingleCellVal(filePath, row, col, A1range, newVal)
  SUB_NAME = "getSingleCellVal" & ": "
  Call LogFileWrite(SUB_NAME & "Started exeution")
  ' START
  If TypeName(filePath) = "String" And (TypeName(row) = "Integer" Or TypeName(row) = "Long") And (TypeName(col) = "Integer" Or TypeName(col) = "Long") Then
    If FSO.FileExists(filePath) Then ' If the file in question exists
      If LCase(FSO.GetExtensionName(filePath)) = "csv" Or LCase(FSO.GetExtensionName(filePath)) = "xlsx" Then ' Check if the file is excell or csv
        If row > 0 And col > 0 Then
          Dim Excel ' Open the excell sheet
          Set Excel = CreateObject("Excel.Application")
          Excel.Visible = TRUE
          Call Excel.Workbooks.Open(filePath) 
          getSingleCellVal = CStr(Excel.ActiveSheet.Cells(row, col).Value) ' READ THE OLD VALUE
          Excel.ActiveSheet.Range("A1").Value = newVal ' WRITE VALUE
          Call Excel.ActiveSheet.Parent.Save() ' SAVE
          ' Close Excel
          Excel.DisplayAlerts = False
          Excel.Quit
          Set Excel = Nothing
        Else
          Call LogFileWrite(SUB_NAME & "row and column values must be greater than zero... Aborting")
        End If
      Else
        Call LogFileWrite(SUB_NAME & "must be xlsx or csv file extensions... Aborting")
      End If
    Else
      Call LogFileWrite(SUB_NAME & "file does not exist... Aborting")
    End If
  Else
    Call LogFileWrite(SUB_NAME & "argument types must be 1-String, 2-Integer, 3-Integer... Aborting")
  End If
  ' END
  Call LogFileWrite(SUB_NAME & "Ended exeution")
End Function


' USING THE FUNCTION
Dim oldVal, newVal
newVal = "I wrote that"
oldVal = getSingleCellVal("C:\Users\111660\Desktop\new.xlsx", 1, 1, "A1", newVal)
Call LogFileWrite(oldVal)

Let me know if I can clarify 

There is a ton of documentation on Excel and VBS here: https://docs.microsoft.com/en-us/office/vba/api/overview/excel  With all the object methods and properties under the object model tab.  I prefer TDMS usually but a lot of people don't have DIAdem or can't use it so I use this stuff sometimes.

[Brad_Turpin · Accepted solution]

Hi Himanshu,

Here's an example script that exports a given group from the Data Portal in the format you requested.

  Set RawGroup = Data.Root.ChannelGroups(2)
  Set RawChannels = RawGroup.Channels
  Set myExcelApp = CreateObject("Excel.Application")
  myExcelApp.Visible = TRUE
  myExcelApp.Workbooks.Add()
  Set myWorksheet = myExcelApp.Activesheet
  myWorksheet.Cells(1,1) = "Excel-Example"
  FOR j = 1 TO RawChannels.Count
    myWorksheet.Cells(2,j) = RawChannels(j).Name
    myWorksheet.Cells(3,j) = ChnDim(RawChannels(j))
  NExt ' j
  Set myRange = myWorksheet.Range("a4")
  Set myRange = myRange.Resize(RawChannels(1).Size, RawChannels.Count)
  myRange.Value = ChnToVariant(RawChannels, "TransposedMatrix")
  Set myExcelApp = Nothing

Brad Turpin

DIAdem Product Support Engineer

National Instruments

Brad

[himanshu25]

Hi Brad,

Exactly!!!! what I needed.

Thanks a lot.

Have A Great Day

Himanshu Sharma

[himanshu25]

Hi Brad,

The script is used to write only one channel group. What if I want to write the next group?

[himanshu25]

Hi Brad,

The script is used to write only one channel group. What if I want to write the next group?

And the channel has only one value.

[JeffLo]

Hi Brad,

I don't very understand last few lines from the code (from set myRange ...to the end)

Can you shed some light?

Instead of change excel name after running script, can I directly assign a file name to excel in script step?