Fri 31 Oct 2008, 15:42Delegate Rory said...
Rory has attended:
Excel VBA Intro Intermediate course
Extracting Data from many worksheets
How do you code VBA so that within a particular folder (say C:mydocuments/Excel), open every document in turn and perform the same task on each document. For example if data need to be extracted from many returned forms and all such forms are saved in the same location.
Sun 2 Nov 2008, 21:50Trainer Laura said...
RE: Extracting Data from many worksheets
Thank you for your question.
The command you need is Dir which returns filenames. The first time you use it you specify the location and mask and then you just use dir to find the next match. When dir finds no more files it returns an empty string.
Example
Sub ReportFiles()
Dim FoundFile As String
'Find first file
FoundFile = Dir("c:\reports\*.xls")
Do While FoundFile > ""
Workbooks.Open FileName:="c:\reports\" & FoundFile
' Process each file
'
' Get name of next file
FileName = Dir
Loop
End Sub
Please let me know how you get on.
Laura GB
