Consulting

Results 1 to 7 of 7

Thread: Loop through a folder and copy data from files to another WB

  1. #1
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    4
    Location

    Loop through a folder and copy data from files to another WB

    Hi I am a beginner in VBA.

    I have a unique requirement to copy and paste data from a folder of excel files to another workbook having reserved (named) tabs for each source files data.

    Explaining the scenario -

    There are many excel files in a specific folder. I want help in VBA code which can loop through the folder and do the following -

    1. Copy data from each file having a single sheet ("Sheet1").
    2. Paste the copied data to a dedicated named sheet of a master workbook.
    3. Erase previous months data in the target sheet before pasting the fresh data.


    There is a catch -

    The master workbook and the source files are at same location.
    The source files need not be opened, Since It will result in 100s of opened file.



    Please help in this, I had been searching this over internet (unsuccessfully)

    I have to do this manually and it is tedious to be honest.

    Thanks

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Option Explicit
    
    
    Sub test()
        Dim ws As Worksheet
        Dim p As String
        Dim wbn As String
        
        Set ws = ActiveSheet
        ws.UsedRange.Offset(1).ClearContents
        
        p = ThisWorkbook.Path & "\"
        wbn = Dir(p & "*.xlsx")
        
        Do While wbn <> ""
            With GetObject(p & wbn).Sheets("sheet1")
                .Cells(1).Offset(1).Copy ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .Parent.Close False
            End With
            wbn = Dir()
        Loop
        
    End Sub

  3. #3
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    4
    Location
    Appreciate your help Mana. many thanks


    Unfortunately, I think I was not able to put my situation in words properly, apologies for this.

    I will try to explain again -

    I have a folder where I have 114 excel files and 1 master workbook.

    The master workbook has 114 worksheets in it, each dedicated for data to be copied from the source excel files.


    I needed a VBA code, which can loop through the folder select each source file at once and copy its data as it is in the dedicated named sheet of the master file.

    Hope I am clear this time.

  4. #4
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Sub test2()
        Dim ws As Worksheet
        Dim p As String
        Dim wbn As String
        
        p = ThisWorkbook.Path & "\"
        
        For Each ws In Worksheets
            ws.UsedRange.Clear
            wbn = Dir(p & ws.Name & ".xlsx")
            If wbn <> "" Then
                With GetObject(p & wbn)
                    .Sheets("sheet1").Cells(1).CurrentRegion.Copy ws.Cells(1)
                    .Close False
                End With
            End If
        Next
    
    
    End Sub

  5. #5
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    4
    Location
    thanks for your try.

    But this solution doesn't solve my problem mentioned.

    However, I can use it for another scenario.

    However, my situation remains unsolved

    This is what actually required -

    I have a folder where I have 114 excel files and 1 master workbook.

    the master workbook has 114 tabs which are already named and they should have data copied from a specific source file.
    For example, if the name of the sheet (Tab) in the master workbook is "West_Data" , than it should be filled with the data from a file which has West_Data in its name, lets say - "Jan_West_Data_NA.xlsx".

    This is the scenario for all 114 files in the folder and all 114 sheets in the master workbook.


    I needed a VBA code, which can loop through the folder select each source file at once and copy its data as it is in the dedicated named sheet of the master file.

    Hope I am making sense this time.

    Plz help me, I am out of options and need help.

    thanks in advance.

  6. #6
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    wbn = Dir(p & "*_" & ws.Name & "_*.xlsx")

  7. #7
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    4
    Location
    Thank you So very much for your reply,

    But the code is not coping the files, though it throws no error.

    Evan, I tried to change the the files name extensions and the names also, But still the data is not getting copied.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •