Solved

Excel VBA process files in directory recursively

Posted on 2013-01-29
3
719 Views
Last Modified: 2013-02-01
Hi,

I'm looking to borrow some code from someone's extensive Excel macro library.
I just need a skeleton that does the following:

Access a directory (hard coded, say "C:\temp")
For each Excel file in the directory (*.xls, *.xlsx?):
    Open file
    Starting at A1, place the file name.
    On the same row, copy a cell, say Sheet1!A1
    Close file

Thank you in advance.

Regards,
Richard
0
Comment
Question by:cyberkiwi
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 38830729
Use something like this to find all the files
http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/1c51fccb-94f3-4147-9bcc-ad564cf33f87
Where you see the ... do something like this
ThisWorkbook.Worksheets("Sheet1").Range("A"&i).Value = sWrk.Name
ThisWorkbook.Worksheets("Sheet1").Range("B"&i).Value = sWrk.Worksheets("Sheet1").Range("A1")

Open in new window

0
 
LVL 58

Author Comment

by:cyberkiwi
ID: 38830790
Thanks Tommy.

That's what I was looking for, but I wanted to check if anyone has an industrial version of such a script to handle files Excel files, which could fire events (.xls + workbook_open), or that could be password protected for opening, or is already open by another Excel instance or the current one etc.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 38830880
Hah. That's what I get when I don't read the author name before I answer the question.

Password stuff just gets passed to the Open method.
If you set the read-only flag on the Open method, it should handle the "already open somewhere else" case.
If the file is already open in the current Excel instance, you would have to get the data from the currently open one. If a different file with the same name is open in the current instance, then I think you're out of luck without closing it first.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question