I am working with the following script to import a large amount of data into diadem to the perform analysis and reporting. I cannot use the excel import wizard or the STP configuration files because the sheets will all be different.
The excel file I am working with has 42 worksheets with varying amounts of channels and channel lenghts. When this excel file is created I used Named Ranges in excel to make is easy to find the information I need without having to search through cells.
The sub LoadGroups works perfectly. It will create the correct groups in the data portal quickly.
The problem is in the LoadChannels routine. I get a type mismatch error with this line (TempArray = .Range(NamesRange).Value) highlighted in red below. I have this exact logic working in a VB6 application already. The variable temparray is dimensioned as a variant, so I don't understand how there could be a type mismatch.
I have attached a reduced size excel sheet. If you try to run the code you will have to change the FilePath to be the correct location.
I suspect it will be an easy fix, but I just can't see it.
Thanks for the help.
Wayne
'-------------------------------------------------------------------------------
'-- VBS script file
'-- Created on 02/23/2008 19:56:07
'-- Author: Wayne Yount
'-- Comment: Trial to import excel based results
'-------------------------------------------------------------------------------
Option Explicit 'Forces the explicit declaration of all the variables in a script.
Call DataDelAll 'clear all previous data
Dim FilePath
FilePath = "C:\Documents and Settings\Wayne Yount\Desktop\_path\fw586 results.xls"
call LoadGroups (FilePath)
call LoadChannels (FilePath)
sub LoadGroups (FilePath)
Dim y,x,z,SheetName
call ExcelCountSheets(FilePath)
y = ExcelStat
for x = 1 to ExcelSheetCount
SheetName = ExcelSheets(x)
SheetName = Ltrim(Rtrim(SheetName))
z = instr(1,SheetName,"Sheet")
If len(SheetName) = 6 and z = 0 then
Call GroupCreate(SheetName)
end if
Next 'x
end sub
sub LoadChannels (FilePath)
Dim a,b,c,d,f,grpindex,Frs,Channels,TempArray(),grpname,exRange
Dim i,Excel, ExcelSheet, WorkBook, PropName, ErrNum
Dim SheetNames(),NumSheets,NumChanNames,ChanNames(),NamesRange
call ExcelCountSheets(FilePath)
a = ExcelStat
If a = 0 then
ReDim SheetNames(ExcelSheetCount)
For b = 1 to ExcelSheetCount
SheetNames(b) = ltrim(rtrim(ExcelSheets(b)))
Next 'b
end if
WorkBook = NameSplit(FilePath, "N") & ".XLS"
On Error Resume Next
Set Excel = CreateObject("Excel.Application") ' Start Excel via OLE
ErrNum = Err.Number
On Error Goto 0
IF ErrNum <> 0 THEN Exit Sub
Excel.Visible = true ' Don't hide Excel during debug so you can close it manually
'Excel.ScreenUpdating = FALSE ' Disable screen refresh
Excel.Workbooks.Open(FilePath) ' Open Excel File
for c = 1 to ExcelSheetCount
grpindex = groupindexget(SheetNames(c))
if grpindex <> 0 then
GroupDefaultSet(grpindex)
NamesRange = SheetNames(c) & "!BodyRelativeChannelNames"
Set ExcelSheet = Excel.Workbooks(WorkBook).Sheets(SheetNames(c))
with ExcelSheet
Frs = .Range(NamesRange).Rows.Count
Channels = .Range(NamesRange).Columns.Count
ReDim ChanNames(Channels-1)
ReDim TempArray(Frs-1, Channels-1)
TempArray = .Range(NamesRange).Value
for d = 1 to Channels
ChanNames(d-1) = TempArray(0,d-1)
Next 'd
end with
for f = 1 to Channels
Call ChnAlloc(ChanNames(f),1024)
next 'f
end if
next 'c
'FOR i = 1 TO 9
' Call GroupPropCreate(GroupIdx, "Prop" & i, DataTypeString)
' Call GroupPropSet(GroupIdx, "Prop" & i, ExcelSheet.Cells(Row+i,Col).Value)
'NEXT ' i
Excel.Workbooks(WorkBook).Close
'Set ExcelSheet = Nothing ' Release the object variable memory
Set Excel = Nothing ' Release the object variable memory
end sub
