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?

Claire

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


oxlApp.Quit
Set oxlApp = Nothing
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.