importing data from multiple excel spreadsheets
 

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

--
Message posted from http://www.ExcelForum.com

importing data from multiple excel spreadsheets
 

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

--
Regards,
Tom Ogilvy

"Geuis " wrote in message
...
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 do
the opening and importing part and I need to see some examples.

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

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

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

Thanks for any help.


---
Message posted from http://www.ExcelForum.com/