+ Reply to Thread
Results 1 to 6 of 6

Use VBA to extract data from multiple files

Hybrid View

JCHC Use VBA to extract data from... 02-14-2013, 06:57 PM
mat.davies Re: Use VBA to extract data... 02-14-2013, 07:15 PM
tigeravatar Re: Use VBA to extract data... 02-14-2013, 07:41 PM
JCHC Re: Use VBA to extract data... 02-15-2013, 12:42 PM
tigeravatar Re: Use VBA to extract data... 02-19-2013, 11:55 AM
JCHC Re: Use VBA to extract data... 02-19-2013, 12:03 PM
  1. #1
    Registered User
    Join Date
    02-14-2013
    Location
    Oregon, US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Use VBA to extract data from multiple files

    Hello everyone,

    So I am stuck and don't know how to do a task I need. I am not very good with VBA so I am hoping to get some help here. This is what I have:

    I have 350 excel files all in one folder some with one worksheet and some with two. They all have the same formatting and look identical except for the variable numbers of course. I need to create a new document which will have only specific cells of the other files. It should look like this:

    A B C
    1 A1 A4 SUM(C7:O54)
    2 A1 A4 SUM(C7:O54)

    Obviously each row is data from a different file.

    Any help? thank you so much!

  2. #2
    Registered User
    Join Date
    01-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2007/10
    Posts
    31

    Re: Use VBA to extract data from multiple files

    Hi

    DIR is your friend for getting the filenames. You'll also need a number of loops. Something like this:

    do while fname <>"" 'loop for all files (from memory, i think dir returns null after the last file, might be wrong
    fname=dir(loc) 'have a look at the DIR help entry to see how to return only excel files
    for each sht in workbooks(fname)
    i=i+1 'counter
    workbooks(<name>).sheets(<name>).cells(i,colNo)=sht.cells(?,?) 'if the sum has already been done, otherwise use a loop or worksheet function to calculate
    next sht
    loop
    that should get you started.

    Cheers

    Mat

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Use VBA to extract data from multiple files

    JCHC,

    Something like this perhaps?
    Sub tgr()
        
        Const strFolderPath As String = "C:\Share\Corvallis Counts 7-20-2012\Edited\48 HOUR Tube Counts\"
        
        Dim lCalc As XlCalculation
        Dim lMacroSec As MsoAutomationSecurity
        Dim ws As Worksheet
        Dim wsDest As Worksheet
        Dim arrData(1 To 65000, 1 To 3) As Variant
        Dim strCurrentFile As String
        Dim DataIndex As Long
        
        strCurrentFile = Dir(strFolderPath & "*.xls*")
        
        If Len(strCurrentFile) = 0 Then
            MsgBox "No Excel files found in path:" & Chr(10) & strFolderPath & Chr(10) & Chr(10) & "Exiting Macro", , "No Files"
            Exit Sub
        End If
        
        With Application
            lCalc = .Calculation
            lMacroSec = .AutomationSecurity
            .Calculation = xlCalculationManual
            .AutomationSecurity = msoAutomationSecurityForceDisable
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        
        On Error GoTo CleanExit
        
        Set wsDest = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count))
        With wsDest.Range("A1:C1")
            .Value = Array("A1", "A4", "Sum(C7:O54)")
            .Font.Bold = True
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
        End With
        
        DataIndex = 0
        Do While Len(strCurrentFile) > 0
            With Workbooks.Open(strFolderPath & strCurrentFile)
                For Each ws In .Sheets
                    DataIndex = DataIndex + 1
                    arrData(DataIndex, 1) = ws.Range("A1").Text
                    arrData(DataIndex, 2) = ws.Range("A4").Text
                    arrData(DataIndex, 3) = WorksheetFunction.Sum(ws.Range("C7:O54"))
                Next ws
                .Close False
            End With
            strCurrentFile = Dir
        Loop
        
        If DataIndex > 0 Then wsDest.Range("A2:C2").Resize(DataIndex).Value = arrData
        
    CleanExit:
        With Application
            .Calculation = lCalc
            .AutomationSecurity = lMacroSec
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        
        If Err.Number <> 0 Then
            MsgBox Err.Description, , "Error: " & Err.Number
            Err.Clear
        End If
        
        Set wsDest = Nothing
        Erase arrData
        
    End Sub
    Last edited by tigeravatar; 02-14-2013 at 07:43 PM.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    02-14-2013
    Location
    Oregon, US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Use VBA to extract data from multiple files

    tigeravatar,

    First of all, you are awesome for taking the time to help me out with all of this code. You are one amazing person!

    I am having a problem with the code though, once I run the VBA and it goes through what seems to be all of the files it gives me the following error and does not write anything on the new file but the headers:

    Error: 1004
    Method 'Open' of object 'Workbooks' failed

    Why do you think that is?

    Once again, thank you so much for all of your help!
    -JCHC

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Use VBA to extract data from multiple files

    That error indicates the code had a problem opening a file that it found. It should only be opening files with extension .xls* (so .xls, .xlsx, .xlsm, etc). Do you have unusual file extensions in that folder, like .xlst or .xlsb files?

  6. #6
    Registered User
    Join Date
    02-14-2013
    Location
    Oregon, US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Use VBA to extract data from multiple files

    tigeravatar,

    Thank you very much, I found out yesterday that one of the files was corrupted. That is now fixed and it ran very smoothly. You have no idea how much help this code has been and how much it will help in the future. You guys are awesome!

    JCHC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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