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 = "\\Drcs8570168\shasad\Test"
    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?

Do you mean without opening the file *in Excel*, or without opening the file *at all*? I don't think the latter makes any sense in the world of computing (correct me anyone). If the former, then why do you not want to open the files in Excel, and why is it preferable to open them in a non-Excel way? — Jean-François Corbett, Sep 23 '11 at 07:04
I need to go through a few hundred files so I don't want them opening and closing. — user960358, Sep 23 '11 at 14:59
I'm not sure why this was moved to a new question when it was still in progress here ??? Also, the links method in this post you accepted was part of a post (point3) that I had raised earlier so I think in future you might want to be a little more careful when closing solutions :) — brettdj, Sep 23 '11 at 22:32
Hi, I don't think I closed anything myself but the only reason I moved something to a new question is because I couldn't enter the code I was wondering about in the comment section and I could post a new answers either. — user960358, Sep 26 '11 at 17:56

score 5 · Answer 1 · edited May 23 '17 at 12:06

5

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.

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

edited May 23 '17 at 12:06

Community

1
1

answered Sep 23 '11 at 04:27

brettdj

54,857
16
114
177

3

Remember - ADO "opens" the file too :) – paulsm4 Sep 23 '11 at 05:20
3

They *all* open the file, on some level! – Jean-François Corbett Sep 23 '11 at 07:07
1

I have roughly 750 files that I need to pull data from so I don't want them opening up and closing while i'm running the macro – user960358 Sep 23 '11 at 14:56
1

Look - there are efficient ways to read the file, and less efficient ways. I even gave you a link to a function called "GetInfoFromClosedFile()". BUT... to READ the file, you've got to OPEN the file. Implicitly, or explicitly. Efficiently, or less efficiently. But you're FOOLING yourself if you think you can get away without even an "implicit open". – paulsm4 Sep 23 '11 at 16:58
1

I'm not sure about the different ways a file technically opens when you're pulling data from it, but I just meant that I don't want a window to open while the info is being taken. – user960358 Sep 23 '11 at 18:16
Exactly - there is a clear difference between opening a file in Excel, versus accessing it. I thought your question was very clear – brettdj Sep 23 '11 at 22:21

score 2 · Accepted Answer · answered Sep 23 '11 at 07:28

2

I just want to point out: You don't strictly need VBA to get values from a closed workbook. You can use a formula such as:

='C:\MyPath\[MyBook.xls]Sheet1'!$A$3

You can implement this approach in VBA as well:

Dim rngDestinationCell As Range
Dim rngSourceCell As Range
Dim xlsPath As String
Dim xlsFilename As String
Dim sourceSheetName As String

Set rngDestinationCell = Cells(3,1) ' or Range("A3")
Set rngSourceCell = Cells(3,1)
xlsPath = "C:\MyPath"
xlsFilename = "MyBook.xls"
sourceSheetName = "Sheet1"

rngDestinationCell.Formula = "=" _
    & "'" & xlsPath & "\[" & xlsFilename & "]" & sourceSheetName & "'!" _
    & rngSourceCell.Address

The other answers present fine solutions as well, perhaps more elegant than this.

answered Sep 23 '11 at 07:28

Jean-François Corbett

37,420
30
139
188

Hey, I like the simplicity of this code since I'm not an expert but I was wondering how this can be applied to all files within that directory and have the information pasted in separate rows. Could you also explain that last line a little more thoroughly? I just don't understand this part very well "'!" & _ rngSourceCell.Address Thanks – user960358 Sep 23 '11 at 14:48
That whole statement is just me trying to construct the string `'C:\MyPath\[MyBook.xls]Sheet1'!$A$3`... – Jean-François Corbett Sep 23 '11 at 15:32
I've never done any code like this before so I'm having a lot of trouble just getting started on it. I imagine I'd have to put "*.xls" as the filename and somehow make that into a loop but I don't know how. Sorry about asking for so much but I'm struggling here. – user960358 Sep 23 '11 at 16:00
Too long to answer in a comment. Please post a new question. – Jean-François Corbett Sep 23 '11 at 16:04
http://stackoverflow.com/questions/7531526/need-to-pull-data-from-all-files-in-a-directory thanks – user960358 Sep 23 '11 at 17:15

score 0 · Answer 3 · answered May 03 '13 at 09:10

Some bits from my class-wrapper for Excel:

Dim wb As Excel.Workbook
Dim xlApp As Excel.Application

Set xlApp = New Excel.Application
xlApp.DisplayAlerts = False  ''# prevents dialog boxes
xlApp.ScreenUpdating = False ''# prevents showing up
xlApp.EnableEvents = False   ''# prevents all internal events even being fired

''# start your "reading from the files"-loop here
    Set wb = xlApp.Workbooks.Add(sFilename) '' better than open, because it can read from files that are in use

    ''# read the cells you need...
    ''# [....]

    wb.Close SaveChanges:=False     ''# clean up workbook
''# end your "reading from the files"-loop here

''# after your're done with all files, properly clean up:
xlApp.Quit
Set xlApp = Nothing

Good luck!

score 0 · Answer 4 · edited May 23 '17 at 12:06

0

brettdj and paulsm4 answers are giving much information but I still wanted to add my 2 cents.

As iDevlop answered in this thread ( Copy data from another Workbook through VBA ), you can also use GetInfoFromClosedFile().

edited May 23 '17 at 12:06

Community

1
1

answered Sep 23 '11 at 06:14

JMax

26,109
12
69
88

score -1 · Answer 5 · edited Jan 20 '12 at 19:19

-1

At the start of your macro add

Application.ScreenUpdating = false

then at the end

Application.ScreenUpdating = True

and you won't see any files open as the macro performs its function.

edited Jan 20 '12 at 19:19

Elmo

6,409
16
72
140

answered Jan 20 '12 at 19:05

Bevans

1

While an interesting workaround, might not be exactly what the OP was looking for since the file still needs opened in Excel. – psubsee2003 Oct 26 '12 at 14:18

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

5 Answers5

Linked

Related