Martin Fishlock
12/19/2006 1:11:00 AM
Dave
This should do the job you need to modify the selection criteria and the get
data lines.
Sub getdata()
' directory where the data is with the trailing \
Const cszDir As String = "C:\data\"
' prefix for the file name
Const cszFilePrefix As String = "file"
' extension for the file name
Const cszFileExtension As String = ".xls"
Dim wsd As Worksheet
Dim wbc As Workbook
Dim lRowDst As Long
Dim szFileCur As String
Dim szDir As String
Set wsd = ActiveSheet
lRowDst = 2
szFileCur = Dir(cszDir & cszFilePrefix & _
"*" & cszFileExtension)
Do While szFileCur <> ""
Set wbc = Workbooks.Open(szFileCur, , True)
'get data >>>
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("C1")
wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("D1")
'....
wbc.Close False
szFileCur = Dir
lRowDst = lRowDst + 1
Loop
End Sub
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.
"Dave" wrote:
> I need to find a solution to the folowing problem:
>
> There is a network folder with an Exel workbook for every week in the year.
> Each Friday it is updated with one new sheet for that week. All the data
> contained is in the same format. I need to create a single file that when
> one presses a button, will go through all of the workbooks in the network
> drive and pull specific information and populate it into the one file. Is
> this possible? Any help would be appreciated.