Linking to Multiple Excel Worbooks

[Genevieve123]

Hi,

I have about 150 excel workbooks. Each is essentially a summary input document about a project. They are all exactly the same format - thus, for example, they all have the total project cost in cell C1 and all have the project manager in cell E5. The name of each workbook is also the unique project number.

I want to make one summary sheet that reads certain data from each of the 150 sheets.

I tried having a column in my summary sheet that had all the filenames (and a column next to it with the path) - and then used the INDIRECT function to pull certain information from the project sheets into other columns (reading the path and filename from the first two columns). This all works OKish (!) - but requires the 150 project sheets to be open - which is not really practical.

Does anyone know a way to set up my summary sheet without having to have the sheets open ? (I know I could do this manually by clicking into each file or writing the full path and filename into each formula - but again these are not highly practical or flexible). I also know you will be thinking - why isn't this all in a database ? - well we're getting there - but I work in a council - it's a slow process !

Thanks for any advice on this. The perfect answer would be a formula based solution, but I suppose a macro could do it too.

G

 

[xlbo]

Think your best bet will be a macro to be honest - please post in Forum707 - this has been covered many times before so you should be able to get some good help. As a starter for 10 you should probably use the FileSystemObject to reference the base folder....the following is a basic setup to access a folder and loop through opening each workbook in turn:

Sub ListFilesInFolder(SourceFolderName As String)

Dim FSO As Scripting.FileSystemObject

Dim Fldr As Scripting.Folder

Dim theFile As Scripting.File

Dim wb As Workbook
    
    Set FSO = New Scripting.FileSystemObject
    
    Set Fldr = FSO.GetFolder(SourceFolderName)
           
    For Each theFile In Fldr.Files
        
        Set wb = Workbooks.Open(SourceFolderName & theFile.Name)
        
        'process data using wb to reference the open workbook and move data from it to "thisworkbook"            
        
        wb.Close (False)
        
        Set wb = Nothing
  
    Next theFile
      
    Set theFile = Nothing
    Set Fldr = Nothing
    Set FSO = Nothing

End Sub

Sub runner()
ListFilesInFolder ("Folder Path Goes Here")
End Sub

Only other thing you will need to do is set a reference to the "Scripting Runtime" library

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Genevieve123]

Thanks - I'll do that

 

[alessiopennisi]

Hi,
I was wondering if Genevieve123 had solved her problem and how. I have the same problem (>500 sheets to be pasted in one without having to open them all) but must be too stupid or lazy not to find a solution for it.

Thanks a lot for all your help !

A

 

[SkipVought]

A,

Please post your question in Forum707, as it will require VBA code to address.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Salut39]

You can also try create list of Workbook Name on your summary sheet and use INDIRECT function to bring the information you want. You might need to open all workbooks once to calculate.

Yuri