database - Excel - VBA Question. Need to access data from all excel files in a directory without opening the files

Question

So I have a "master" excel file that I need to populate with data from excel files in a directory. I just need to access each file and copy one line from the second sheet in each workbook and paste that into my master file without opening the excel files.

I'm not an expert at this but I can handle some intermediate macros. The most important thing I need is to be able to access each file one by one without opening them. I really need this so any help is appreciated! Thanks!

Edit...

So I've been trying to use the dir function to run through the directory with a loop, but I don't know how to move on from the first file. I saw this on a site, but for me the loop won't stop and it only accesses the first file in the directory.

Folder = "\Drcs8570168shasadTest"
    wbname = Dir(Folder & "" & "*.xls")

    Do While wbname <> ""

i = i + 1
ReDim Preserve wblist(1 To i)
wblist(i) = wbname
wbname = Dir(FolderName & "" & "*.xls")

How does wbname move down the list of files?

Answer 1

You dont have to open the files (ADO may be an option, as is creating links with code, or using ExecuteExcel4Macro) but typically opening files with code is the most flexible and easiest approach.

1. Copy a range from a closed workbook (ADO)
2. ExecuteExcel4Macro
3. Links method

But why don't you want to open the files - is this really a hard constraint?

My code in Macro to loop through all sheets that are placed between two named sheets and copy their data to a consolidated file pulls all data from all sheets in each workbook in a folder together (by opening the files in the background).

It could easily be tailored to just row X of sheet 2 if you are happy with this process