Can excel macros be converted into diadem

[Stagsden]

From the standard csv format which I can get from Diadem, I would like to change headers and put in another column of data.

I can do this within Excel easily enough mannually or by using a macro, but I would like to be able to import the macro VB script from Excel into Diadem.

In which case I would not need to open Excel and I my edited csv file is ready to use.

The attached file just contains the first few data points

Regards

Stagsden

[Brad_Turpin]

Hi stagsden,

I have looked at your two CSV files, and I see the difference in both columns and header rows.  What I'm not clear on is whether these CSV files should be read into DIAdem or exported from DIAdem.  If you want to read the CSV files into DIAdem, then you need a new DataPlugin to do that.  If you want to export the CSV file from DIAdem, then where iin the Data Poral is the new information to be stored in the new header rows?  In properties?  In channel values?  Can you send me a TDM and TDX file of the data as it appears in your Data Portal prior to the export?  Also, by what formula should the new column(s) be calculated?

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[Stagsden]

Dear Brad,

I am getting closer to what I need.

I would like to export data from Diadem and put it into a csv format with the header file attached and the extra columns. I do not need to bring data into Diadem.

I have got the following VB script which opens a template file in Excel, but I cannot transfer the data from the Data Portal into the Excel sheet.

There must be a simple command to 'get data' or 'copy channels'

Attached is the data in TDM format and the VB script is below which I have obtained from a previous disscussion board.

Dim i, j, Excel, ExcelSheet, ExcelFilePath, ExcelWorkbook, ErrNum
Const xlMaximized = &HFFFFEFD7
Const StartDataRow = 22
Const StartDataCol = 4 ' B
ExcelWorkbook = "Excel Template Camera.xls"
ExcelFilePath = "D:\New_PC\Testing\DataonScreen\" & ExcelWorkbook
' Start Excel via OLE, a new Excel instance always starts.
On Error Resume Next
Set Excel = CreateObject("Excel.Application")
ErrNum = Err.Number
On Error Goto 0
IF ErrNum <> 0 THEN Call AutoQuit("Error opening Excel Template File")
Excel.Visible = True ' Show Excel if Excel was hidden up to now
Excel.WindowState = xlMaximized ' Maximize Excel window
' Call WndShow("SHELL", "MINIMIZE") ' Minimize DIAdem
Excel.Workbooks.Open(ExcelFilePath) ' Open Excel File
Set ExcelSheet = Excel.Workbooks(ExcelWorkbook).Sheets("data")
Excel.ScreenUpdating = False ' Disable screen refresh.
ExcelSheet.Cells(4, 22).Select ' Select first cell in the worksheet

'This is where I would like to bring the channel data in.

Excel.ScreenUpdating = True ' Enable screen refresh
ExcelSheet.Parent.Saved = FALSE ' force "save changes?" question
IF 1 = 0 THEN Excel.Quit
Set ExcelSheet = Nothing ' Release the object variable memory
Set Excel = Nothing      ' Release the object variable memory

Thanks

Stagsden

[Brad_Turpin]

Hi stagsden,

Please post the TDX file that belongs to that TDM file-- the data values are stored in the TDX file you didn't post.  Also, it is important to send the values directly to Excel, or could we alternatively create a CSV file that could be loaded into Excel either manually or programmatically?  For larger data sets sending values through ActiveX can be slow, and the ActiveX connection has its own foibles.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[Stagsden]

Tdx file attached

[Brad_Turpin]

Thanks stagsden,

Things are coming into focus now-- I can clearly see the data you have to start with in DIAdem and the exported csv file format you want to end up with.  There are 3 elements in the desired CSV header you sent that I can read from the TDM file:

#Date,,02/07/2008
#Time,,16:37:42
#NumberOfDatas,,10000,,,,

But the remainder of the desired CSV header items do not seem to be anywhere in the TDM file.  Do you want to use the same hard-coded values that you sent me for all exported CSV files?  Will the TDM file have this information in the future?  If so, where will the information reside-- File properties, Group properties, Channel properties, or in some external ASCII file?

#PHOTRON MCDL DATA,,
#SheetType,,A
#CameraType,,FASTCAM-APX RS 250K
#CameraID,,5
#FrameRate(fps),,1000
#SaveStep,,1
#ShutterSpeed(sec),,1/1000000
#ImageWidth,,512
#ImageHeight,,592
#TriggerMode,,End
#NumberOfFrames,,500
#FrameRange,,0 to 500
#UseZeroFrame,,0
#SamplingRate(sps),,20000
#Sloap,,,1,1,1,1
#Intercept,,,0,0,0,0

Also, the CSV file you sent seems to assume there are 19 data points per frame-- will this always be the case, or will this information also be in the TDM file?  This information is required to correctly create all 3 extra columns in the CSV file you sent me.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[Stagsden]

Hello Brad,

Yes, the 19 lines of property data will not be in the original TDM file, they will be 'hard coded' into the newly created csv file, with the exception of date, time and number of points that can be added to the top section.

It would also be useful to put the name of the data into the name of the newly created csv file. The 19 lines of property data can reside in an external ASCII file.

The first 19 points are in one frame, then subsequently there are 20 points in each frame. This will always be the case.

There may be upto 10,000 points.

Regards

Stagsden

[Brad_Turpin]

Hi stagsden,

Here's what I think you want.  You didn't specify where the file name was supposed to go and whether you meant the name of the TDM file or the name of the TDM group, so I included both of them in the header.  As I suggested in my earlier posts, the VBScript creates a *.csv file which loads up in Excel by just double-clicking on the file.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[Stagsden]

Brad,

Thanks, does the job perfectly. 

I would go home for the day if I was you, you have done a days work already.

Stagsden

[Stagsden]

Hello Brad,

There three adjustments that I am trying to make to the programme that don't seem to work.

Your programme (Export to CSV File.VBS) deletes all the data before processing the script using the command DataDelAll. This causes my report template to search for data that is not there as my original data is deleted before the TDM file is imported. However, I would like to keep two groups present.

I have tried to specify that the Export to csv file only operates on group 2 (cameraoutput.TDM) rather than group 1(original data) and I have taken out the DataDelAll command. Somehow it stll writes out a file with Group 1 and Group 2 data in it. Can I keep both groups on show and perform the operation on the TDM file only?

I have changed GroupIdX to 2 in the following locations....but it did not work

Call AddCountChans(2)
FileNamePath = NameSplit(DataFileName, "P") & NameSplit(DataFileName, "N") & ".csv"
HeaderLines = GetHdrLines(2)

Secondly, I would like to write the file to a different file location. I have taken the following command and tried to adapt it to write to the E:/ drive

Call GroupAsciiExport(GroupDefaultGet, FileNamePath, HeaderLines)

 Currently it uses the variable FileParameters

This did not work either.

Thirdly and finally, what is the error code 6290. I did not find it in Help?

Thanks

Stagsden