CODEWITHSUNDEEP
vbaexcel
Digital Lightcraft
Digital Lightcraft

Reputation: 465

Excel macro to open a folder of excel workbooks and copy 1 cell

I have a folder of .xlsx files, each identical in layout.

I need to write a macro in excel to open each file in turn (100+ files) then get the data (a name) from a single cell, and drop it in a new excel worksheet, move on to the next and insert that below the last one etc.

Giving me basically a list of names from data not file names)

Upvotes: 0

Views: 2312

Answers (1)

DeeWBee
DeeWBee

Reputation: 695

Here is (pretty much) exactly what you're trying to do. Next time do a little bit of googling before you ask! :)

http://www.excel-easy.com/vba/examples/files-in-a-directory.html

ROUGH CODE UNSURE IF IT WILL WORK: But here is the basic idea of what you need to modify in the example I sent you. If you look at the example again, it does everything you need and then some. Since you weren't interested in all worksheets, you don't have to loop through all worksheets in a workbook. You can just open it up, read your cell of interest, and then close it. The Do While loop will do this for every Excel file in your directory. AGAIN! Please modify this example accordingly before you use it. 

Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer

Application.ScreenUpdating = False

directory = "c:\test\"
fileName = Dir(directory & "*.xl??")

Do While fileName <> ""

    i = i + 1
    Workbooks.Open (directory & fileName)
    Workbooks("files-in-a-directory.xls").Worksheets(1).Cells(i, 1).Value = Workbooks(fileName).Worksheets(1).Cells(x, y) <-- whatever your cell of interest is
    Workbooks(fileName).Close
    fileName = Dir()

Loop

Application.ScreenUpdating = True

Upvotes: 1

Related Questions