macro - open all files in a folder

Question

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  amp      xlsx   Do While MyFile  lt  gt     Workbooks Open Filename  MyFolder  amp       amp  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

User · Answer

Try the below code   Sub opendfiles    Dim myfile As Variant Dim counter As Integer Dim path As String  myfolder    D  temp   ChDir myfolder myfile   Application GetOpenFilename         True  counter   1 If IsNumeric myfile    True Then     MsgBox  No files selected  End If While counter  lt   UBound myfile      path   myfile counter      Workbooks Open path     counter   counter   1 Wend  End Sub

User · Answer

You have to add this line just before loop      MyFile   Dir Loop

User · Answer

You can use Len StrFile   gt  0 in loop check statement    Sub openMyfile        Dim Source As String     Dim StrFile As String       do not forget last backslash in source directory      Source    E  Planning 03       StrFile   Dir Source       Do While Len StrFile   gt  0                                 Workbooks Open Filename  Source  amp  StrFile         StrFile   Dir       Loop End Sub

[excel] macro - open all files in a folder

Examples related to excel

Examples related to excel-2010

Examples related to vba