Opening and Looping through files via VBA

[Joe4]

I would like to create a macro that does the following that will combine an undetermined amount of data files into one file. I envision it work something like this:

1. Allows the user to browse a directory and select/open as many files in the directory as they wish (using CTRL to select multiple files). They could select any number of files.

2. Loop through all the files, copy and paste their data to one file, and close all the files (other than new "combined file"). The trick here is that I only want to loop through the files that were just opened. If the user had any Excel files open on their computer prior to running this macro, I want to ignore those files.


I know how to browse and open ONE file, and I know how to copy and paste the data via VBA, I just need to find out how to open multiple files, and loop through those files just opened. Can anyone help?

Thanks.

 

[tactps]

You will have to play with this a little, but a starting point is to count the workbooks using:

Test = Workbooks.Count

Once you know how many are currently open, you can then use something like:

Test = Workbooks.Count
Chdir .... (indicate which directory to get the files from)
Workbooks.open (ie open up the "Open" box for them to choose files)

Do until workbooks.count = Test
Workbooks(Test).select
Insert macro here to copy and paste

loop
end sub

 

[Joe4]

tactps,

Thanks for the reply. Though I don't think the solution would help due to the following conditions I laid out in the original question:

The trick here is that I only want to loop through the files that were just opened. If the user had any Excel files open on their computer prior to running this macro, I want to ignore those files.

I imagine that this macro that I am writing will be contained in a separate Excel file, and I want to account for the fact that the user may have other Excel files open on their computer that I wish to ignore. I imagine that I can use an IF statement to exclude the macro Excel file, but am not sure how to account for any other open Excel files.

Basically, I want the macro to run ONLY on the files that they select in the beginning of the macro, not every single open Excel file.

 

[Tom Urtis]

I know how to copy and paste the data via VBA, I just need to find out how to open multiple files, and loop through those files just opened.

One way:

Sub Test1()
Dim var As Variant, i As Integer
var = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
On Error GoTo ERRORHANDLER
For i = 1 To UBound(var)
Workbooks.Open (var(i))

MsgBox "Your copy and paste code goes here."

ActiveWorkbook.Close False
Next i
Exit Sub
ERRORHANDLER:
MsgBox "No files were selected, action cancelled."
End Sub

 

[Richie(UK)]

Hi j,

How about:

Sub SelectOpenCopy()
    Dim vaFiles As Variant
    Dim i As Long
    Dim wbkToCopy As Workbook
    
    vaFiles = Application.GetOpenFilename("Excel Files (*.xls), *.xls", _
              Title:="Select files", MultiSelect:=True)
    
    If IsArray(vaFiles) Then
        For i = LBound(vaFiles) To UBound(vaFiles)
            Set wbkToCopy = Workbooks.Open(FileName:=vaFiles(i))
            '
            'copy, paste, whatever
            '
            wbkToCopy.Close savechanges:=False
        Next i
    End If

End Sub

HTH

EDIT : Tom was TDQ!

 

[Joe4]

Tom & Rich,

Thanks. That was what I was looking for!

 

[tylerz]

I have a slightly similar issue. I need to loop through two separate directories, open the first files from each, do some copy and paste work with them and then close both files. Then this process needs to loop so the second files in each folder open and so on. This is where I'm having my issue. I'm calling the Dir function twice for each separate folder, but apparently this doesn't work. Basically right now, I have one of the folders looping correctly but the other one is either stagnant on the same file or returning a run time error. Please help!