Results 1 to 8 of 8

VBA extract sheet data from multiple subfolders into master

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    VBA extract sheet data from multiple subfolders into master

    Hello all. I've been searching for several days and trying different techniques with no apparent success, so I'm hoping someone can see what I have and have a better solution. The most comprehensive, succinct, and successful code I've been able to construct with the help of many, is the example below, however, the code doesn't look in subfolders and there are a couple of tweaks that I haven't been able to configure.

    I've attached an example data set. The data sets are ASCII coded (basically you can see all data is read within column 1). All data are space delimited. The header information can remain intact, but once transferred it needs to autofill to lastrow of each relative data set. I've got some code that can delimit out data sets and remove the ASCII headers, so I think I can deal with delimiting it after all the files are transferred, but why isn't it looping through the sub-directories?
    Sub Consolidate()
    
        Dim Fs As Object 'FileSystem
        Dim d As Object 'Folder
        Dim Fx As Object 'Subfolder
        Dim file As Object 'File
        Dim PathName As String
        Dim iRow As Long 'next available row index of destination worksheet
        Dim LastRow As Long 'last row of source worksheet
        Dim wbSource As Workbook, wsMaster As Worksheet
        
        Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet data will be compiled into
    
        With wsMaster 'data destination worksheet
            Set Fs = CreateObject("Scripting.FileSystemObject")
            Set d = Fs.GetFolder("Z:\Operations\Chupacabra\Data\")
            iRow = 2
            
            For Each Fx In d.SubFolders 'loop through subfolders
                For Each file In Fx.Files 'loop through files
                    If file.Name Like "*dq1000d.las*" Then
                        PathName = Fx.Name & "\" & file.Name
                        Workbooks.Open d.Path & "\" & PathName
                        Application.StatusBar = "Processing " & PathName
                        LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1 'Find & copy to last row having data in column A
                        Range("A132:A" & LastRow).Copy .Range("A" & iRow)    'this original data is space delmited
                        Range("A13").Copy .Range("B" & iRow)                 'header data needs to remain intact & autofill
                        Range("A12").Copy .Range("C" & iRow)                 'header data needs to remain intact & autofill
                        Range("A23").Copy .Range("D" & iRow)                 'header data needs to remain intact & autofill
                        iRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1   'Next row
                        ActiveWorkbook.Close savechanges:=False
                    End If
                    iRow = iRow + LastRow
                Next file
            Next Fx
        End With
    End Sub
    Attached Files Attached Files
    Last edited by terriertrip; 09-12-2017 at 08:09 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extract/present data from master sheet
    By DJSorg in forum Excel General
    Replies: 1
    Last Post: 05-24-2016, 11:06 AM
  2. Replies: 3
    Last Post: 05-07-2014, 11:05 AM
  3. Extract data based on variables from multiple subfolders
    By honeybunny in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-28-2013, 09:56 AM
  4. Replies: 4
    Last Post: 03-05-2013, 01:02 PM
  5. Replies: 3
    Last Post: 01-05-2013, 02:20 AM
  6. Extract data into master work sheet
    By kammariarun in forum Excel General
    Replies: 0
    Last Post: 12-11-2011, 03:53 PM
  7. Extract data from multiple workbooks to master
    By unley in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-24-2010, 06:22 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1