script for importing and merging multiple Excel Worksheets

[RogerC]

I am using a script written by NI Support to import and merge multiple Excel Worksheets. It works when the data to be imported is in the format the script was designed for. The data format has now changed with a new column added on the right. This new column now only imports from the first worksheet but the script still imports all the rest of the data correctly if I move another column across.

How can I import this data from Excel and merge it while keeping it synchronised?

Please find the script attached. Many thanks.

[Brad_Turpin]

Hi Roger,

The VBScript is programmatically calling the Excel Import Wizard and using the *.STP file that the Excel Import Wizard generates to define the expected data arrangement in Excel.  You need to have the VBScript use the old *.STP file for the Excel files with the old data structure, and you need to use a new *.STP file for the Excel files with the new data structure.  You can generate the new *.STP file by interactively loading one of the new-style Excel files with the Excel Import Wizard, which you get from the NAVIGATOR menu "File >> Open.." and then selecting "Excel Files (*.xls)" in the "Files of type" enumerated control at the bottom of that dialog.

Then you will need to edit the logic in your VBScript to use different *.STP files depending on the Excel file type-- here's the place in your code where that *.STP file is called:

Call ExcelImport(DataFile,i,0,MyFolders(0)&"Evopod-Full_page1.stp")

Brad Turpin

DIAdem Product Support Engineer
National Instruments

[RogerC]

Hi Brad,

I've followed your first paragraph of instructions (interactively importing the data from Excel) but I don't know whether this has created an STP file. The data I have imported is also missing it's headings.

Best regards,

Roger

[Brad_Turpin]

H Roger,

If you weren't presented with a file save dialog, then the *.stp file did not get saved.  There's a "Save" checkbox in the upper right corner of the first step of the Excel Import Wizard that you need to check in order to cause the *.stp file to be created.

If your headings are not importing correctly into DIAdem, then you need to change the settings in either step 1 or step 2 of the Excel Import Wizard.  Feel free to post one of these Excel files for me to look at-- I may be able to make suggestions for import settings or identify limitations of the Excel Import Wizard that you are running into.

Brad Turpin
DIAdem Product Support Engineer

National Instruments

[RogerC]

Hi Brad,

Sorry I'm slow to get back to you; I'm working a 2 day week.

I've found the tick box to save STP files now.

I've attached a file showing the format of my data. I've tried to import this file and found that only the first sheet is imported.

Thanks,

Roger

[Brad_Turpin]

Hi Roger,

The Excel Import Wizard only imports one Excel sheet at a time.  You will need to create a separate *.STP file for each Excel sheet that has a different data structure, and you will need to use or call the Excel Import Wizard once for each Excel sheet you want to import.

Brad Turpin
DIAdem Product Support

National Instruments

[RogerC]

Hi Brad,

Ok, I can import the worksheets one at a time.

I now need to merge them together. How do I do that please?

Thanks,

Roger

[Brad_Turpin]

Hi Roger,

Here is the VBScript I use to concatenate data from multiple Excel files.  In your case it would be from multiple Excel Sheets, but that should be a minor tweak.  If you already have the data from each sheet loaded into its own Group in the Data Portal, then you can use the "Concatenate Groups.VBS" script stand-alone to concatenate the Groups together into one.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[RogerC]

Hi Brad,

I'm having difficulty working your Concat script. Please can I have some instructions?

Thanks,

Roger

[Brad_Turpin]

Hi Roger,

The first 80 lines of the "Concatenate Groups.VBS" are a series of detailed descriptions of what the script does, how it works, and how to use it.  The last 6 lines of that description contain commented VBScript code you would use to load multiple data sets and then concatenate them.  Really, all you need is the following:

GroupIdx = ConcatGroups("", "", "", "") '

IF GroupIdx > 0 THEN GroupName(GroupIdx) = "Concatenated Group"

By default the script assumes that the first channel in each Group is the ordering channel (usually Time), but you can indicate a different ordering channel by name or by index in the first parameter of the function.

Brad Turpin

DIAdem Product Support Engineer
National Instruements