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.
Solved! Go to Solution.
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.
DIAdem Product Support Engineer
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
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.
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
DIAdem Product Support Engineer
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.
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?