Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Information building?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Information building?
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").Val ue = _ 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) "Greg B" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Information building?
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. "Greg B" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table cutting information of a part of the information | Excel Discussion (Misc queries) | |||
Building a TOC | Excel Discussion (Misc queries) | |||
building a web site | Charts and Charting in Excel | |||
building xsl for an xml | Excel Programming | |||
Building xll | Excel Programming |