Read data from several excel workbooks

[faberk]

I have a folder named c:\wrkbookcollection

I need to read the contents of one cell in all the workbooks in that folder
and write those contents to a table in my database. I have used automation
before, but am not sure how to collect the list of workbook names in that
directory. help help help.

 

[Graham Mandeno]

You can use the Dir() function with a wildcard to return all the files in a
folder, one by one, which match your wildcard spec:

Dim sFilename as string
sFilename = Dir("c:\wrkbookcollection\*.xls")
Do Until sFileName = ""
' do something with sFileName
sFilename = Dir
Loop

At the "do something" point, open the workbook, extract the cell contents,
and close it again. Create the Excel.Application before the Do...Loop and
Quit it after, to reduce the overhead of launching Excel for each file.

 

[faberk]

Graham,

Excellent. Thank you. To thicken to plot: say each workbook contained may
sheets that i needed to extract the contents of a specific cell from...how do
i determine the sheet humbers and names and loop through them?

thank you in advance

You can use the Dir() function with a wildcard to return all the files in a
folder, one by one, which match your wildcard spec:

Dim sFilename as string
sFilename = Dir("c:\wrkbookcollection\*.xls")
Do Until sFileName = ""
' do something with sFileName
sFilename = Dir
Loop

At the "do something" point, open the workbook, extract the cell contents,
and close it again. Create the Excel.Application before the Do...Loop and
Quit it after, to reduce the overhead of launching Excel for each file.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a folder named c:\wrkbookcollection

I need to read the contents of one cell in all the workbooks in that
folder
and write those contents to a table in my database. I have used
automation
before, but am not sure how to collect the list of workbook names in that
directory. help help help.

 

[Graham Mandeno]

You need to enumerate the Worksheets collection of the workbook:

Dim oXL As Excel.Application
Dim oWkb As Excel.Workbook
Dim oSht As Excel.Worksheet
Set oXL = CreateObject("Excel.Application")
.....
Set oWkb = oXL.Workbooks.Open(sFilename, , True)
For Each oSht In oWkb.Worksheets
Debug.Print oSht.Name
Debug.Print oSht.Cells(1,1)
Next
......

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Excellent. Thank you. To thicken to plot: say each workbook contained
may
sheets that i needed to extract the contents of a specific cell from...how
do
i determine the sheet humbers and names and loop through them?

thank you in advance

You can use the Dir() function with a wildcard to return all the files in
a
folder, one by one, which match your wildcard spec:

Dim sFilename as string
sFilename = Dir("c:\wrkbookcollection\*.xls")
Do Until sFileName = ""
' do something with sFileName
sFilename = Dir
Loop

At the "do something" point, open the workbook, extract the cell
contents,
and close it again. Create the Excel.Application before the Do...Loop
and
Quit it after, to reduce the overhead of launching Excel for each file.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a folder named c:\wrkbookcollection

I need to read the contents of one cell in all the workbooks in that
folder
and write those contents to a table in my database. I have used
automation
before, but am not sure how to collect the list of workbook names in
that
directory. help help help.

 

[faberk]

Thanks Graham

You need to enumerate the Worksheets collection of the workbook:

Dim oXL As Excel.Application
Dim oWkb As Excel.Workbook
Dim oSht As Excel.Worksheet
Set oXL = CreateObject("Excel.Application")
.....
Set oWkb = oXL.Workbooks.Open(sFilename, , True)
For Each oSht In oWkb.Worksheets
Debug.Print oSht.Name
Debug.Print oSht.Cells(1,1)
Next
......

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Excellent. Thank you. To thicken to plot: say each workbook contained
may
sheets that i needed to extract the contents of a specific cell from...how
do
i determine the sheet humbers and names and loop through them?

thank you in advance

You can use the Dir() function with a wildcard to return all the files in
a
folder, one by one, which match your wildcard spec:

Dim sFilename as string
sFilename = Dir("c:\wrkbookcollection\*.xls")
Do Until sFileName = ""
' do something with sFileName
sFilename = Dir
Loop

At the "do something" point, open the workbook, extract the cell
contents,
and close it again. Create the Excel.Application before the Do...Loop
and
Quit it after, to reduce the overhead of launching Excel for each file.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a folder named c:\wrkbookcollection

I need to read the contents of one cell in all the workbooks in that
folder
and write those contents to a table in my database. I have used
automation
before, but am not sure how to collect the list of workbook names in
that
directory. help help help.