importing data from multiple excel spreadsheets

[Geuis]

I need assistance in creating code that will open excel spreadsheets
import data into an array, then close the file. I'm not sure how to d
the opening and importing part and I need to see some examples.

We have a program from Avaya called CMS that lets us monitor differen
bits of data for the techs at hour tech support help desk.
When we want to run reports on our teams, we have scripts tha
interface with CMS and generate Excel spreadsheets of each technician'
current stats.
We then have to open all of the spreadsheets at once, then open anothe
spreadsheet that reads the data, performs the calculations needed t
average the data, then displays it all in a nice little layout tha
shows all the info properly sorted.

The main issue is that this is a pain in the @ss. Its cumbersome an
slow.

What I would like to do is add in some VB code that will open eac
spreadsheet being generated by CMS, import the important data, the
close the spreadsheet. Once all the info is stored in a couple o
arrays, the VB code will then do its normal calculations and displa
the info.

Thanks for any help

 

[Tom Ogilvy]

How are the workbooks to open determined?

Are they all in one directory and all files in that directory should be
opened?
Assume the above
Dim sPath as String, sName as String
Dim i as Long, j as Long, sStr as String
Dim wkbk as Workbook
Dim vArr as Variant

sPath = "C:\MyFiles\
sName = Dir(sPath & "*.xls")
do while sName <> ""
sStr = sName & vbNewLine & vbNewLine
set wkbk = workbooks.Open(sPath & sName)
vArr = wkbk.Worksheets(1).Range("A1:B20").Value
for i = 1 to 2
for j = 1 to 20
sStr = sStr & varr(i,j) & ","
next j
sStr = sStr & vbNewLine
Next i
msgbox sStr
wkbk.Close Savechanges:=False
sName = dir()
Loop

untested (may contain typos), but represents an approach