Opening multiple files in VBA

malbarghouti1

New Member
Joined
Dec 22, 2016
Messages
2
Hello all,

I have a simple code that allows me to chose multiple files, opens them, and also should allow me to do something to the files one file at a time

Code:
Sub test()
    Dim var As Variant, i As Integer
    Dim x1 As String
    var = Application.GetOpenFilename(FileFilter:="Excel files (*.*), *.*", MultiSelect:=True)
    For i = 1 To UBound(var)
        Workbooks.Open FileName:=var(i)

        ' i need to do something here such as formatting cells and also closing the file before opening the next
        x = var(i)

    Next i
End Sub

The problem is that this code once it gets pass the "Workbooks.Open FileName:=var(i)" it opens all the files selected and stops. Even any line after that don't get executed such as the line x = var(i). I use F8 to step through and I can see that the macro just finishes after the "Workbooks.Open FileName:=var(i)" line.

I want to be able to open the files one at a time, do something, close the file and move on to the next.

Thank you for your help
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Are you doing something manually in each file selected? Are you wanting to program the things to do in each file? If so, place a breakpoint on the For i statement. (Put cursor on that line and press [F9]) When your code executes, it will pause at the For loop and allow you to F8 / Step thru the subroutine.

You might tweak your code a bit to allow it to close the workbook(s) for you. See highlighted lines:

Code:
Sub test()
    Dim var As Variant, i As Integer
    Dim x1 As String
[COLOR=#ff0000]    Dim wb As Workbook[/COLOR]
    
    var = Application.GetOpenFilename(FileFilter:="Excel files (*.*), *.*", MultiSelect:=True)
    For i = LBound(var) To UBound(var)
        [COLOR=#ff0000]Set wb = [/COLOR]Workbooks.Open(Filename:=var(i))
        
        ' i need to do something here such as formatting cells and also closing the file before opening the next
        x[COLOR=#ff0000]1[/COLOR] = var(i)


[COLOR=#ff0000]        wb.Close SaveChanges:=False[/COLOR]
    Next i
End Sub
 
Upvote 0
Thanks Calsux,
For some reason there was something that made the macro open all files all at once rather than cycle through them one by one. When I ran the macro at work I found that it was doing exactly what I wanted to do which is to open the files one after the other.

Yes I will need to add a file.close after I do what I want to do.

Thank you for the help,
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top