DIAdem

cancel
Showing results for 
Search instead for 
Did you mean: 

DIAdem : Writing Data to Excel

Solved!
Go to solution
Highlighted

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.

0 Kudos
Message 1 of 9
(1,242 Views)

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

0 Kudos
Message 2 of 9
(1,165 Views)

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 

0 Kudos
Message 3 of 9
(1,160 Views)

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.

0 Kudos
Message 4 of 9
(1,151 Views)
Solution
Accepted by topic author himanshu25

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

Message 5 of 9
(1,143 Views)

Hi Brad,

 

Exactly!!!! what I needed.

Thanks a lot.

 

Have A Great Day

Himanshu Sharma

0 Kudos
Message 6 of 9
(1,131 Views)

Hi Brad,

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

0 Kudos
Message 7 of 9
(1,046 Views)

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.

0 Kudos
Message 8 of 9
(1,041 Views)

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?

0 Kudos
Message 9 of 9
(976 Views)