read multiple .xls into vbs array

I need to be able to drop an .xls file onto a .vbs file and have it extract all of the data into an internal array, but I'm having trouble with the Excel part.

dim fso, objExcel, objArgs, strArg, srcWorkbook, srcWkSht, lngLastRow, varData(0,0), y, x, alongstring
set fso = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
with objExcel
	.DisplayAlerts = False
	.Application.Visible = False
end with
Set objArgs = Wscript.Arguments
For Each strArg in objArgs
	msgbox strArg
	If fso.FileExists(strArg) Then
		Set srcWorkbook = objExcel.Workbooks.Open(strArg)
		set srcWkSht = srcWorkbook.sheets(1)
		Range("A2", "AC" & srcWkSht.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row).Select
		lngLastRow = Selection.Rows.count
		ReDim varData(lngLastRow, 29)
		for x = 0 to lngLastRow - 1 
			for y = 0 to 28
				varData(x,y) = Selection.Cells(x,y)
			next
		next
		srcWorkbook.Close
	end if
Next
For x = LBound(varData,1) to UBound(varData,1)
	for y = LBound(varData,2) to UBound(varData,2)
		alongstring = alongstring & varData(x,y)
	next
next
msgbox alongstring
objExcel.Quit
msgbox "Finished"
WScript.Quit

Open in new window

RossaminoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
I think from what I see obvious your having a problem with this part

for x = 0 to lngLastRow - 1 
       for y = 0 to 28
	varData(x,y) = Selection.Cells(x,y)
       next
next

Open in new window


cells row and column start at 1 whereas your array index start at 0 so your loop should be

for x = 0 to lngLastRow - 1 
       for y = 0 to 28
	varData(x,y) = Selection.Cells(x+1,y+1)
       next
next

Open in new window


but yet this need to be tested as do not have your data to make sure you capture all ends maybe you should loop till lngLastRow  and not lngLastRow -1 and your 28 to 29 ?? only having the data can confirm this.

gowflow
0
FaustulusCommented:
In place of all of this,
        Dim varData(0,0)
        Set srcWkSht = srcWorkbook.Sheets(1)
        Range("A2", "AC" & srcWkSht.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row).Select
        lngLastRow = Selection.Rows.Count
        ReDim varData(lngLastRow, 29)
        For x = 0 To lngLastRow - 1
            For y = 0 To 28
                varData(x, y) = Selection.Cells(x, y)
            Next
        Next
your might simply use this:-
    Dim varData
    Dim Rng As Range
    With srcWorkbook.Sheets(1)
        Set Rng = .Range("A2", "AC" & .Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row)
    End With
    varData = Rng.Value

Open in new window

varData will be a 3-dimensional array like your own varData but 1-based in place of 0-based.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RossaminoAuthor Commented:
@Faustulus, when running this I was getting "Unable to get the SpecialCells property of the Range class."  So I switched to using "set Rng = .Range("A2", "AC" & .UsedRange.Rows.count)
" and that worked.
dim fso, objExcel, objArgs, strArg, srcWorkbook, srcWkSht, lngLastRow, varData, y, x, alongstring
dim Rng
set fso = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
with objExcel
	.DisplayAlerts = False
	.Application.Visible = False
end with
Set objArgs = Wscript.Arguments
For Each strArg in objArgs
	msgbox strArg
	If fso.FileExists(strArg) Then
		Set srcWorkbook = objExcel.Workbooks.Open(strArg)
		With srcWorkbook.sheets(1)
			set Rng = .Range("A2", "AC" & .UsedRange.Rows.count)
		End With
		varData = Rng.Value
		srcWorkbook.Close
	end if
Next
objExcel.Quit

For x = LBound(varData,1) to UBound(varData,1)
	for y = LBound(varData,2) to UBound(varData,2)
		alongstring = alongstring & varData(x,y) & ", "
	next
	alongstring = mid(alongstring,1,len(alongstring)-1) & vbCrLf
next
msgbox alongstring
WScript.Quit

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.