importing data from many workbooks in different folders

Question

I am looking to import/copy data from many workbooks into a summary workbook. The workbooks are arranged in different sub-folders, I.e

C:\data1\results_2001.xlm
C:\data2\results_2002.xlm
C:\data3\results_2003.xlm

The names are similar but differ slightly to differentiate them. At present, I import the files individually, and I want to automate the process. The results files (above) are amongst other excel files so I cannot target them by file type.

How would I import these files by partial file name?

score 2 · Answer 1 · answered Jun 14 '14 at 10:54

One option is to create an array of the filepaths to your excel sheets and then loop over the array and get the data you want into your summary sheet.

Sub CreateSummary()
    Dim wkbs() As Variant, wkb As Integer, owb As Workbook

    wkbs = Array("C:\data1\results_2001.xlm", "C:\data2\results_2002.xlm", "C:\data3\results_2003.xlm")

    For wkb = 0 To UBound(wkbs)             
         Set owb = Application.Workbooks.Open(wkbs(wkb)) //Open each workbook

         With owb
             //Get the data you want into your summary workbook
            .Close
         End With
    Next wkb
End Sub

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

You can do it in any languages, but for you who is asking this question, i think it's gonna be a little challenging, so here is what you need to do :

create a function that will list files/folders from given path
loop through all items found, if it's a folder , recursive it
if the item fits your target(name, extension, ...) , read it and load the content to the summary

something like this, i believe you will achieve this easily using VBA, look here

Literally, it will be like this, please note that this is not valid code, just something i write down to help you figure it out :

function loopthepath (string pathtoloop)

foreach(dirItem item in pathtoloop.getdirItem)
{
    if (item is folder)
    {
        loopthepath(pathtoloop + item) 
    }
    else 
    {
        if (item fits mydescription)
        {
            load the content to the summary 
        }
    }
}

edited May 23 '17 at 10:27

Community

1
1

answered Jun 14 '14 at 06:35

NeedAnswers

1,411
3
19
43

I posted a link to a topic which can help you on this task, please read it – NeedAnswers Jun 14 '14 at 07:33
When you mean list the files/folders... Do you mean to input them somewhere in the workbook and reference them from there? – user3739365 Jun 14 '14 at 08:31
yes, you can store the folder path in a textbox, a cell, or directly in your code if your path don't change often. i edited and added more detail in my original answer – NeedAnswers Jun 14 '14 at 08:46
Apologies if I sound naive - I am a novice in VBA. I gave some sample text for someone to help in the coding. – user3739365 Jun 14 '14 at 10:17
glad to help, but keep in mind that the real hard part is copying and paste the data to your summary workbook, not the listing folders and files stuff :) – NeedAnswers Jun 14 '14 at 10:58

score 0 · Answer 3 · answered Jun 14 '14 at 06:59

0

Another way, especially if only a one time operation: Go into Cmd.exe, do a Dir for the files you're looking for, and send it to a text file (eg, something like dir c:\results_*.xlm /s /b > c:\myList.txt). Then import the text file to your worksheet, step thru each cell in the list, opening each workbook in turn.

answered Jun 14 '14 at 06:59

DaveU

1,082
2
14
25

Perhaps I should have elaborated more earlier... I am trying to take this one step at a time. The workbooks/sheets have formulas which I need in the results summary. Once I import, I will also need to change the source link to the results summary so that the formulas are functional within the summary workbook. I presume I will need to do this after each workbook is copied. – user3739365 Jun 14 '14 at 07:20

importing data from many workbooks in different folders

3 Answers3