Results 1 to 10 of 10

macro to select path to collect data from

Threaded View

  1. #1
    Registered User
    Join Date
    09-14-2004
    Posts
    21

    macro to select path to collect data from

    Hi , i have a macro , done with the help of this forum that colects data from seeveral files in one folder that is hard coded in the macro, what i would like now is to have the possibility to select the working folder to work
    attatched the files i have now

    The macro i have is

    
    Sub CollectInfo()
    'Author:    Jerry Beaucaire, ExcelForum.com
    'Date:      10/21/2010
    'Summary:   Collect specific data from all workbooks in a single folder
    Dim fPath As String:        fPath = "C:\2010\Test\"      'where files are found
    Dim fName As String
    Dim wbData As Workbook
    Dim wsDest As Worksheet:    Set wsDest = ThisWorkbook.Sheets("stock")
    Dim NR As Long:             NR = wsDest.Range("B" & Rows.Count).End(xlUp).Row + 1
    Dim LR As Long
    
    Application.ScreenUpdating = False      'speed up macro
    fName = Dir(fPath & "*.xls")            'filter for files to open
    
    Do While Len(fName) > 0
        Set wbData = Workbooks.Open(fPath & fName)  'open found file
        With wbData.Sheets("Resumo")
            .Rows(10).AutoFilter
            .Rows(10).AutoFilter Field:=6, Criteria1:=">0.5"
            LR = .Range("A" & .Rows.Count).End(xlUp).Row
            If LR > 10 Then
                wsDest.Range("A" & NR).Value = .[A5]
                wsDest.Range("E" & NR).Value = .[D2]
                .Range("A11:A" & LR & ",F11:F" & LR & ",K11:K" & LR).Copy
                wsDest.Range("B" & NR).PasteSpecial xlPasteValuesAndNumberFormats
                wsDest.Range("F" & NR).Value = .[C*E]
                
            End If
        End With
        wbData.Close False
        NR = Range("B" & Rows.Count).End(xlUp).Row + 1
        fName = Dir
    Loop
    
    LR = Range("B" & Rows.Count).End(xlUp).Row
    With Range("A1:E" & LR)
        .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    
    Run [teorico()]
    Run [real()]
    
    Columns.AutoFit
    Application.ScreenUpdating = True
    
    
    
    End Sub
    Attached Files Attached Files
    Last edited by clixo; 10-26-2010 at 11:44 AM.

Thread Information

Users Browsing this Thread

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

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