Workbooks.Open to open multiple files in turn

Question

I have 6 Excel files in "Final Folder". I need to open each in turn, put in some data from the workbook where code is running (I am using ThisWorkbook for source).

The code opens all 6 files together rather than opening them in turn.

It seems when running Workbooks.Open within the loop, all the files open by default.

Sub PopulateFinalFile()

    Dim filpath As String
    Dim fso As Scripting.FileSystemObject
    Dim fil As Scripting.File
    Dim fldr As Scripting.Folder

    filpath = "C:\Users\deepak.ved\Desktop\Reports\Final Reports"
    Set fso = New Scripting.FileSystemObject
    Set fldr = fso.GetFolder(filpath)

    For Each fil In fldr.Files
        Application.Workbooks.Open (fso.GetFile(fil.Path))
    Next fil

End Sub

Unrelated question: How can I point to a workbook using FSO. Like if I use getfile or folder.files, it returns a path. When I am passing the path to a workbook, I need to have workbook.open(path) but all I want is to point to that workbook instead of opening it.

If you open multiple files, in order, you need to use MultiSelect in your workbook.open code: `Application.Workbooks.Open(fso.GetFile(fil.path), MultiSelect:=True)`. Additionally, when you use MultiSelect, the order of the files is no longer preserved and so you need to provide a looping. Try this post for help [link] (http://stackoverflow.com/questions/25180061/excel-vba-getopenfilename-error-on-multiselect-true) — mojo3340, Nov 14 '16 at 12:01
Hey Jiri, thank you for quick response. I will check this and let you know if it works :) — Deepak Ved, Nov 14 '16 at 12:15
Create a workbook variable, say `wb`, then in the main loop have `Set wb = Application.Workbooks.Open (fso.GetFile(fil.Path))`. Still in the body of that loop, process `wb` and then close it. That way the workbooks in the directory are processed sequentially, which is what you seem to want. This question might help: http://stackoverflow.com/q/5851531/4996248 — John Coleman, Nov 14 '16 at 12:19
Hey, I tried the WB variable method in my earlier attempts to resolve the issue but it seems to return the same error. — Deepak Ved, Nov 14 '16 at 12:30
Also for Application.workbooks.open syntax, I am not getting the multiselect option. It is reflecting as syntax error (highlighted in red). I am running windows 8 office 2013 — Deepak Ved, Nov 14 '16 at 12:31
HI Limak, even before i get to next line of code, it opens all the excel file at the same time — Deepak Ved, Nov 14 '16 at 12:49
it works when you take it out of the for loop, I will investigate why and get back to you :) — mojo3340, Nov 14 '16 at 13:26

score 0 · Accepted Answer · answered Nov 14 '16 at 13:04

0

Try this:

Option Explicit

Sub PopulateFinalFile()

    Dim filpath As String
    Dim fso As Scripting.FileSystemObject
    Dim fil As Scripting.File
    Dim fldr As Scripting.Folder

    filpath = "C:\Users\user\Desktop\TEST"
    Set fso = New Scripting.FileSystemObject
    Set fldr = fso.GetFolder(filpath)

    For Each fil In fldr.Files
        Application.Workbooks.Open (fil.Path)
    Next fil


End Sub

It should start openning them one by one.

answered Nov 14 '16 at 13:04

Vityata

42,633
8
55
100

1

HI Vityata, Thank you for the help. the code ran beautifully. Just one question, do you think "updatelink" property might be pushing the workbook.open method to open all files at the same time. Because when i executed your code first, it opened all the files, but then i modified it slightly to set updatelinks off, i worked. But i would sincerely thank you for the time you gave to comment and help me. :) – Deepak Ved Nov 14 '16 at 16:45
Well, Idk. I think it was because of this `(fso.GetFile(fil.Path))` – Vityata Nov 14 '16 at 16:47
Ok. Also could you share some light on my other question. I repeat it here. I have fso.getfile generated a path. Now i want this file to be assigned to a declared WB object which will in turn be used as a workbook only. the Workbooks.open(fil.path) does point to that workbook but instead of opening it i would want to just point to it as a workbook only instead of a fso file. – Deepak Ved Nov 14 '16 at 16:52
Also, i don't know if i would be pushing my luck too much with another question. I have a loop counter generating a number now if i want to use it to refer a workook on a given path. for example folder x has 6 excel workbooks i want to refer to the Nth (decided by loop counter) file, this does not work application.workbooks.open(loopcounter) because the source path is not mentioned – Deepak Ved Nov 14 '16 at 16:58
I really have to stand up now, but why don't you ask it in StackOverflow separately? As far as you describe it well, you would most probably get a good answer to it. – Vityata Nov 14 '16 at 16:59
Ok. Sure will check SO separately. Again Thank you Vityata for the help. Really appreciate it. – Deepak Ved Nov 14 '16 at 17:02
:) Welcome, @DeepakVed :) – Vityata Nov 14 '16 at 17:06

score 0 · Answer 2 · answered Jun 17 '19 at 10:02

Added to Vityata to cover the second part of the question:

Option Explicit

Sub PopulateFinalFile()

    Dim filpath As String
    Dim fso As Scripting.FileSystemObject
    Dim fil As Scripting.File
    Dim fldr As Scripting.Folder
    Dim wb as workbook

    filpath = "C:\Users\user\Desktop\TEST"
    Set fso = New Scripting.FileSystemObject
    Set fldr = fso.GetFolder(filpath)

    For Each fil In fldr.Files
        set wb = Application.Workbooks.Open(fil.Path)  'no space after open !
        'do some stuff with wb
        wb.close    'close wb
    Next fil


End Sub

Workbooks.Open to open multiple files in turn

2 Answers2