I am using visual basic to open multiple spreadsheets and copy rows of data into one spreadsheet.

I have done this by using workbooks.open and then specifying the filename,
however, I would like to open all spreadsheets in the folder without having
to add the names of each as these will be changing

Can anyone help?


First Add reference to
1. Microsoft Scripting Runtime
2. Microsoft Excel Library

Dim fso As New FileSystemObject
Dim fldr As Folder
Dim extn As String
Dim flename As File
Dim oxlApp As New Excel.Application
Dim oxlWB As Excel.Workbook

Set fldr = fso.GetFolder("Path of Folder")
oxlApp.Visible = True
For Each flename In fldr.Files
    extn = Right(flename.Name, 4)
    If extn = ".xls" Then
        Set oxlWB = oxlApp.Workbooks.Open(flename)
        oxlWB.Close False
    End If

Next flename

Set oxlApp = Nothing