Information building?

[Greg B]

I want to have my the spreadsheet I open automatically open all workbooks in
a directory. And copy the information from cell b2 into the current
workbook. The only problem is that this directory will be added to and also
client deleted as they come and go.

currently we have 40 patients

so that will be 40 workbooks in the directory c:\idsc\clients\

The sheet name for all books is called "hidden info"

and the workbook it will be pasted into is called chart database

Thanks if anyone can help with this

Greg

 

[Bob Phillips]

Here is some code as a starter

Dim aryFiles
Dim oFSO

Sub LoopFolders()
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr
Dim iRow as Long

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

i= 1
For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Open Filename:=file.Path
Workbooks.("chart
database.xls").Worksheets("data").Cells(i,"A").Value = _
Workbooks(Activeworkbook.Worksheets("hidden
info").Range("B2").Value
i = i + 1
End If
Next file

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

OPENING a long list of files could be time consuming. As an alternative you
might want to explore linking cells in your "master" sheet to the "hidden"
sheets

or looping thru each with Data> Get External Data which does not require
opening the workbooks.