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

Thanks for the suggestion. Unfortunately, it produces a slightly different runtime error, Type mismatch: 'ss(...)'

When you Dim'med the ss variable, did you include parentheses after it? If so, don't. Notice Andreas said...

Dim ss

and not...

Dim ss()

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 3

Thanks for the suggestion. Unfortunately, it produces a slightly different runtime error, Type mismatch: 'ss(...)'

Ed

Answer 4

Don't declare the dimensions of ss. Just use Dim ss (or omit it altogether).

Answer 5

Am 30.11.2010 17:48, schrieb theOtherEd:

I declare an array with

Dim ss(25202,10)

and assign to it with

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

Don't set dimensions if you declare the array.

dim ss

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

Note: ss is set to (1 to 25203, 1 to 11) after that instruction.

Andreas.