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.)

' Script: weather.vbs

' Usage: weather.vbs /Folder:<datafolder>

' Process command line arguments

numArgs = WScript.Arguments.Count

Set namedArgs = WScript.Arguments.Named

If Not namedArgs.Exists("Folder") Or numArgs <> 1 Then

WScript.Echo "Usage: weather.vbs /Folder:<foldername>"

WScript.Quit

End If

' Get the folder object

folderName = namedArgs.Item("Folder")

Set fileSystem = CreateObject("Scripting.FileSystemObject")

If fileSystem.FolderExists(folderName) Then

Set folder = fileSystem.GetFolder(folderName)

Else

WScript.Echo "Folder does not exist"

WScript.Quit

End If

' Launch Excel

Set excel = CreateObject("Excel.Application")

excel.DisplayAlerts = False

excel.Visible = True

' Perform calculations on each file in the folder

Set files = folder.Files

For Each file in files

WScript.Echo file.Name

With excel

.Workbooks.OpenText(file.Path)

Call Calc(excel)

.ActiveWorkbook.Save

.Workbooks.Close

End With

Next

excel.Quit

' Calculations

Sub Calc(app)

Dim ss

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

' Copy spreadsheet to array

' type mismatch happens on the following line if Dim ss() or Dim ss(25202,10)

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

For i = 1 To 25202

'type mismatch happens on the following line if Dim ss

If ss(i, 1) = 1 Then

ss(i, 3) = ss(i, 3) - 1.234

ss(i, 4) = ss(i, 4) - 1.314

ss(i, 6) = ss(i, 6) * 0.984

ss(i, 9) = 0.0

End If

Next

' Copy modified array back to spreadsheet

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

End Sub

Answer 3

What is  objExcel  set to?

---

Rik_UK Please mark the message or messages that answer your question as the "Answer" or vote if a reply has been helpful.

Answer 4

Ed,

Can you show a more of your code ?  Something we could test without guessing exactly how you've written it.

Tim

Answer 5

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()

I excluded the parentheses. I also tried excluding the Dim statement altogether. Both approaches returned the same type mismatch error.