Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VBA process files in directory recursively

Posted on 2013-01-29
3
Medium Priority
?
747 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

604 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