Assigning Excel range to array in VBScript yields type mismatch error

Question

HI,

I'm trying to automate the processing of some Excel 2010 spreadsheets by running a VBScript on the command line (WSH) under Windows 7.

I declare an array with

Dim ss(25202,10)

and assign to it with

ss = objExcel.Range("A1:K25203").Value

However, I get a "type mismatch" (error code 800A000D) on the assignment statement.

Can someone tell me what I'm doing wrong.

(For what it's worth, the statement objExcel.Range("A25204:K29952").Delete does seem to work.)

Thanks

Ed

Answer 1

First of all, thanks to everyone who took the time to think about the problem and post replies.

I finally found the key to the solution in OssieMac's first post "ranges assigned to a variant produce a 1 based array". I assumed it was a 0 based array. I got a type mismatch error at runtime because the first row of each of my spreadsheets contains text column headings and I was inadvertently comparing that to numerical constants. After I adjusted the limits on the For loop and shifted the indices on all array references, the script worked. No other changes were required.

Thanks again.

Ed

Answer 2

Couple of afterthoughts on the code above. I am not sure that the following line withOpenText is correct:-

Set wb = .Workbooks.OpenText(file.Path) 'Set workbook variable

I thought it should be more like    Set wb = .Workbooks.Open(full path and filename)     If file.Path returns the full path and file name then OK but not sure about the Text in the previous line.

Also you might like to consider the folowing alternative code.

'Two Alternative code options in lieu of specifying worksheet name.

Set ws = wb.ActiveSheet     'Set worksheet variable to ActiveSheet

Set ws = wb.Worksheets(1)   'Set worksheet variable to First sheet

---

Regards, OssieMac

Answer 3

I think you need to more specific about what and where the code is referring to. Note I have used ExcelApp as the variable; I would avoid Excel as a variable in case of conflicts with reserved words.

Also ranges assigned to a variant produce a 1 based array by default so the first element in both dimensions is 1.

Sub MyTest()

Dim ExcelApp As Excel.Application

Dim wb As Workbook

Dim ws As Worksheet

Set ExcelApp = CreateObject("Excel.Application")

ExcelApp.DisplayAlerts = False

ExcelApp.Visible = True

With ExcelApp

  Set wb = .Workbooks.OpenText(file.Path) 'Set workbook variable

  Set ws = wb.Worksheets("Sheet1")  'Set worksheet variable

  Call Calc(ws) 'Pass worksheet as parameter

  wb.Save

  wb.Close

End With

End Sub

Sub Calc(wsToCalc As Worksheet)

Dim ss

ss = wsToCalc.Range("A1:K25203").Value

'You other code here.

End Sub

---

Regards, OssieMac

Answer 4

Thanks Tim. Unfortunately, no joy. Still the same behaviour.

Ed

Answer 5

I always try to use a worksheet qualifier for any Range object, so maybe it's the Application.Range().Value that's tripping it up.

Try

Call Calc(excel.ActiveSheet)

instead (so you're passing in a worksheet and not the app itself)

Tim