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

Here is a stripped-down version of the code I'm trying to run. Note that the type mismatch error is raised on different lines, depending on whether ss is Dim'ed with brackets or not. The error occurs regardless. (By the way, thanks to everyone for taking an interest.)

IMHO it's recommend to specify the argment DataType and other arguments when using the OpenText method. If you did not specify any argument a text file is read just line by line.

I.E. if your text file contains a line "1;2;3;4" the hole line is stored in Range("A1") and any other cells are empty.

And that's why the type mismatch occurs, you can not do calculations with strings or empty values. So for the first step use IsNumeric to test if the value can be used for calculations.

For the second step you should improve your code to be sure that the files are read correctly.

Andreas.

Sub Calc(app)

  app.Range("A25204:K29952").Delete

  ' Copy spreadsheet to array

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

  For i = 1 To UBound(ss)

    If ss(i, 1) = 1 Then

      If IsNumeric(ss(i, 3)) Then ss(i, 3) = ss(i, 3) - 1.234

      If IsNumeric(ss(i, 4)) Then ss(i, 4) = ss(i, 4) - 1.314

      If IsNumeric(ss(i, 6)) Then ss(i, 6) = ss(i, 6) * 0.984

      ss(i, 9) = 0

    End If

  Next

  ' Copy modified array back to spreadsheet

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

End Sub