Opening multiple documents via 'For Each' structure. VBA

Question

I want a piece of code that will allow me to open 3 documents that are placed within a folder - and each time the documents open I want there to be a message box that displays the files name. I wanted to do this loop via a For Each Next structure.

I know that application.documents.open filename:="link to the documents" is the code I need to use but I'm having trouble putting it together.

Option Explicit

Sub openthrice()

    Application.Documents.Open FileName:="C:\Users\John\Desktop\New folder\Doc1.docx"
End Sub

Read up on the [FileSystemObject](https://msdn.microsoft.com/en-us/library/aa711216%28v=vs.71%29.aspx?f=255&MSPPError=-2147217396). This handy little class exposes the functionality you are looking for. — David Rushton, Nov 02 '16 at 16:25

score 0 · Answer 1 · answered Nov 02 '16 at 15:24

0

Try this as a starter for 10, it will open the file and display the file path in a message box, will get back to you with further simplification...

Dim StrFile As String

StrFile = Application.GetOpenFilename
Workbooks.Open (StrFile), UpdateLinks:=False
MsgBox StrFile

answered Nov 02 '16 at 15:24

mojo3340

534
1
6
27

I'm so lost as to where to even insert that because everytime I run the code I automatically get an error message stating method or data member cannot be found. I need it in a for each next loop and I'm having a ton of difficulty. – John Kibbits Nov 02 '16 at 15:34

David Rushton · Answer 2 · 2016-11-03T09:16:29.227

This method requires a reference to the Windows Script Host Object Model. To add select Tools and then References from the VBA menu. The references are listed alphabetically.

' Requires reference: Windows Script Host Object Model.
Sub FindFile()
    Const ROOT_DIR As String = "C:\MI\Example"  ' Update with your folder.
    Dim fso As FileSystemObject                 ' Used to read from file system.
    Dim fle As File                             ' Used to loop over files.

    ' Ready object variable for use.
    Set fso = New FileSystemObject

    ' Loop over files.
    For Each fle In fso.GetFolder(ROOT_DIR).Files

        If fle.Name Like "*.docx" Then Application.Documents.Open fle.Name
    Next


    ' Release object vars before they leave scope.
    Set fso = Nothing
End Sub

The FileSystemObject is a powerful class, provided by Microsoft. It allows your code to interact with the Windows file system.

EDIT

The error user defined type not defined means the compiler does not recognise one your data types. In this case, it does not know what a FileSystemObject is, so it cannot create the variable fso of that type. To fix it needs the reference, mentioned above. This contains the definition, telling VBA what an fso is, and how it works.

From the menu strip can you click Tools >> References and double check Windows Script Host Object Model is checked (selected references should be at the top, the rest are listed alphabetically).

Thank you for this but I'm getting a error message that states 'user defined type not defined' — John Kibbits, Nov 02 '16 at 16:59
Do you know which line is returning the error? Were you able to add the reference? — David Rushton, Nov 02 '16 at 19:54
Dim fso As FileSystemObject is returning the error. User defined type not defined. I was able to add my reference yes but I can't get passed that. — John Kibbits, Nov 02 '16 at 20:18
That error normally means the reference hasn't been added correctly. I'll edit my answer to include more details... — David Rushton, Nov 03 '16 at 09:07

Opening multiple documents via 'For Each' structure. VBA

2 Answers2