vbscript variable to excel

[RAYLWARD102]

I've got a vbscript that passes a variable to excel.
It currently and successfully can deliver a single value to excel.
I'm attempting to send an array; not having any luck.
The excel code receives the variable and debug.print confirms the variable is an array, but as soon as you try to access the contents of the array, it errors


Here is the excel receiving macro:

Sub Test(SomeArray As Variant)
    Select Case IsArray(SomeArray)
        Case True
            Debug.Print "yes " & Now
            Debug.Print UBound(SomeArray)
        Case False
            Debug.Print "not " & Now
    End Select
End Sub

Here is the VBscript:

  Dim FileCount() 
 On Error Resume Next
   Err.Clear 
   Set objExcel = GetObject( , "Excel.Application")   
   Set objWorkbook = objExcel.Workbooks("pass vbcript array to excel.xlsm") 
   If Err.Number <> 0 Then
      Set objFSO = Nothing
      WScript.Quit
   End If
   FileCount = Split("1,2,3,4",",")
   objExcel.Application.Run "Module1.Test",  FileCount
   Set objWorkbook = Nothing
   Set objExcel = Nothing

 

[CalcSux78]

Just a shot in the dark....

Try replacing the 3rd to last line with this:

Call Test(FileCount)

 

[LockeGarmin]

I'm guessing arrays are implemented differently between VBA and VBScript so they won't be compatable.

A couple of options.

1. Do the split using the VBA library instead of the VBScript Library:

Dim FileCount
On Error Resume Next
Err.Clear 
Set objExcel = GetObject( , "Excel.Application")   
Set objWorkbook = objExcel.Workbooks("book1.xlsm") 
If Err.Number <> 0 Then
   Set objFSO = Nothing
   WScript.Quit
End If
FileCount = "1,2,3,4"
objExcel.Application.Run "Module1.Test", FileCount
Set objWorkbook = Nothing
Set objExcel = Nothing

Sub Test(SomeString As Variant)

Dim Arr As Variant
    Arr = Split(SomeString, ",")
    Select Case IsArray(Arr)
        Case True
            MsgBox "yes " & Now
            MsgBox UBound(Arr)
        Case False
            MsgBox "not " & Now
    End Select
End Sub

2. Pass a collection that is COM compatible such as the System.Collections.ArrayList in the .NET library.

On Error Resume Next
Err.Clear 
Set objExcel = GetObject( , "Excel.Application")   
Set objWorkbook = objExcel.Workbooks("book1.xlsm") 
If Err.Number <> 0 Then
   Set objFSO = Nothing
   WScript.Quit
End If

Dim FileCount
Set FileCount = CreateArrayList(Array("A","B","C",1,2,3))

objExcel.Application.Run "Module1.Test2",  FileCount
Set objWorkbook = Nothing
Set objExcel = Nothing

Function CreateArrayList(Items)

Dim List
Dim Item

Set List = CreateObject("System.Collections.ArrayList")

   For Each Item in Items
      List.Add(Item)
   Next

   Set CreateArrayList = List
   
End Function

Sub Test2(SomeList As Variant)

Dim Item As Variant

  For Each Item In SomeList
    MsgBox Item
  Next Item
  
  MsgBox SomeList.Contains("B")
  MsgBox SomeList.Contains("D")
    
End Sub

ArrayList Members:
https://msdn.microsoft.com/en-us/library/system.collections.arraylist(v=vs.110).aspx

 

[RAYLWARD102]

working now! thanks

 

[Norie]

How where you trying to access the array in VBA?

What errors did you get when doing that?

Also, how does the array appear in the Locals window?

 

[RAYLWARD102]

from first example, error occurred at Debug.Print UBound(SomeArray)
Isarray was reporting True, but not allowing me to Debug.Print UBound(SomeArray)
I think the other problem I was having was being masked by on error resume next; I removed that and figured problem out.
Thanks for the help

 

[Norie]

This worked fine for me, I think your problem was how you were declaring FileCount.

Dim FileCount


	Set objExcel = GetObject( , "Excel.Application")   
	Set objWorkbook = objExcel.Workbooks("pass vbcript array to excel.xlsm") 

	If Err.Number <> 0 Then
	  Set objFSO = Nothing
	  WScript.Quit
	End If

	FileCount = split("1,2,3,4", ",")

	objExcel.Application.Run "Module1.Test",  FileCount

	Set objWorkbook = Nothing
	Set objExcel = Nothing