Automating EXCEL from ACCESS 2002

[bryan]

Does anybody have an algorithm that will allow me to go
to a given folder and open all of the EXCEL workbooks in
that folder (the number will vary), one at a time,
retrieve data, and close each workbook, all from within
an ACCESS 2002 database?
Thanks.

 

[Bogdan Zamfir]

Hi,

Try with the following code:

' retrive all XLS from a given directory

Dim aXLS() As String
Dim nSize As Integer
Dim cXlsName As String
' fill the array with all XLS file
Const cRootPath = ....... ' your root path goes here
' actually you might need to use a variable, for the path (to let user
enters it, or get it from an application setting)

nSize = 0
cXlsName = Dir(cRootPath & "*.XLS")
Do While cXlsName <> ""
nSize = nSize + 1
ReDim Preserve aXLS(nSize) As String
aXLS(nSize) = cXlsName
cXlsName = Dir()
Loop

' now its time to start Excel

Dim oExcelObj as new Excel.Application ' you have to set up a
reference to Excel
Dim oExWs As Excel.Worksheet

' start procesing all XLS

dim i as integer
dim MyVar
for i = 1 to nSize
' open excel workbook
oExcelObj.Workbooks.Open cRootPath & aXLS(i)
' get reference to first worksheet
Set oExWs = oExcelObj.Worksheets(1)
' you can retrive data from excel using several methods
' one of them is using Cells property with row and column index
MyVar = oExWs.Cells(2,4) ' row 2, col 4
' ........... do whatever you need here

'clear reference to worksheet
set oExWs = nothing
'close the workbook
oExcel.ActiveWorkbook.Close
next

' close excel
oExcelObj.Quit
set oExcelObj = nothing


HTH,
Bogdan