How to export data to an excel file using VBScript

[g8vngy]

I'm trying to create a VBScript in DIAdem (V9.1) which automatically saves all the current channels in the data portal to an excel file. I've tried using 'Call ExcelExport' and specify a full file path but the file is never created. What am I doing wrong?

Thanks,

Steve

[Ingo_Schumacher]

Hello Steve,

if you are doing the Excel export manually, you are using a wizard to configure which channels and properties are going to be exported. In a script, you will usually prefer that the wizard does not come up during the datasaving. That is why you should run the wizard once and save a configuration file (*.STP) that contains all information about how to export the data. Using the Excelexport command you have the ability to call a STP file to configure the export.
Try this syntax and replace the files with the names and paths of your files:

Call ExcelExport("f:\test.xls", "", 0, "f:\xlsexp.stp")

The 0 parameter of the command will inhibit the dialog, if you want the import wizard dialog to show up, use a 1 instead.

[g8vngy]

Ingo,
That's great thanks for the answer. It now works and creates an excel file with the correct data but I now have a new problem!

In my application, there will always be a different number of channels in the data portal when I run the script - is there any way to make the configuration file automatically select all channels in the data portal to save to excel every time the script is run? Currently when I use the configuration file (because I don't want a dialog to appear) to save to excel, only the channels included when i created the configuration file are recorded to excel i.e. if new channels have been added in the meantime, these are not included in the excel file.

Thanks again,

Steve

[Ingo_Schumacher]

Hi Steve,

I understand the problem an did some further tests - unfortunately without finding a proper solution yet.
The stp file must fit to the number of channels in the dataportal. If you configure a bunch of channel that does not actually exist in the portal, teh function will write a xls file which cannot be opened afterwards.
Programatically editing the STP file is not a good idea either.

There are two workarounds I can think of at the moment:

• Create multiple stp files - one for each channel count and select them programatically with the GlobUsedChn variable. Disadvantage is that you need to create and maintain a lot of STP files.


• Export the files in spreadsheet ASCII format, which you can inport to Excel later. You can do this by saving them as DAT and configuring the DAT file parameters. The disadvantage is that you cannot create one file cpontaining data and metadata such as channelnames and properties at the same time.

I'll give that feedback to R&D so maybe we will have the possibility to create STP files that are capable to export all existent channels in future.

[Brad_Turpin]

Hi Steve,

There are a couple of other things you can try. If you don't like Ingo's suggestion about multiple Excel STP files, your only real option is to create an ASCII file. Note that you can give the ASCII file an extension of *.CSV or even *.XLS, and Excel will read it right in.

So the question boils down to how to format the ASCII export correctly and quickly.

You can use the DAT file approach Ingo mentioned and even re-write the file with VBS to include a few header lines of your own choosing. Note that this approach does NOT support text channels. There is an example of this in the zip file attached below.

You can alternatively use the CSV export function in DIAdem 9.1 to output an ASCII file with the name of each channel as the single header row above all the values. There is an example of this in the zip file attached below.

You can also just read the values from the DIAdem channels and write them out to an ASCII file cell by cell, managing everything with VBScript calls. There is an example of this in the zip file attached below.

I hope that helps,
Brad Turpin
DIAdem Product Support Engineer
National Indstruments

[bohm]

I've go a related problem but the workarounds given here are not contenting.
I'd like to export all channels of a certain group, but the order of groups in database vary. (The number of channels in group is constant.)

The limitation of ExcelExport -Command of using static stp-files with static channel-numbers undermines the concept of addressing channels by Group and index/name and should overcome.

Martin

[bohm]

Tryed to sort groups with GroupMove - command first. But it's no solution. Channel numbers stay unchanged. It's a pity.
Can I set cannel numbers directly?

[winner]

Hi Martin

You can sort the channel numbers in the order of the Data Portal using the command ChnRenumber(). You cannot change the channel number of a single channel. So first use the ommand Groupmove or Chnmove and then ChnRenumber()

Winfried

[bohm]

Thanks, that works.

Martin

[kade]

You script works to export the data into a specified format (i.e. csv), but it only exports the first group, how can one export all the groups in the data portal with thier associated channels.