macro - open all files in a folder

excel excel-2010 vba
Ross McLaughlin picture Ross McLaughlin · Jun 22, 2012 · Viewed 128.5k times · Source

I want to open all files in a specified folder and have the following code

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "\\ILAFILESERVER\Public\Documents\Renewable Energy\FiTs\1 Planning
           Department\Marks Tracker\Quality Control Reports"
MyFile = Dir(MyFolder & "\*.xlsx")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
Loop
End Sub

The problem I have is that it just keeps trying to open the first file in the folder repeatedly and won't move on. Can anybody help, I'm a bit of a novice at VBA and could really do with some assistance. I'm trying to open around 30 reports that are all in .xlsx format. Many thanks in advance.

Answer

Siddharth Rout picture Siddharth Rout · Jun 22, 2012

You have to add this line just before loop

    MyFile = Dir
Loop

Related questions

Excel VBA Run-time error '13' Type mismatch

I created a macro for a file and first it was working fine, but today I've been opening and restarting the file and macro hundreds of times and I'm always getting the following error: Excel VBA Run-time error '13' …

Read More
Copy an entire worksheet to a new worksheet in Excel 2010

I have found similar questions that deal with copying an entire worksheet in one workbook and pasting it to another workbook, but I am interested in simply copying an entire worksheet and pasting it to a new worksheet -- in …

Read More
Copying and pasting data using VBA code

I have a button on a spreadsheet that, when pressed, should allow the user to open a file, then copy columns A-G of the spreadsheet "Data", then paste the data from those columns on the current sheet. I have a …

Read More