how to iteratively loop through files in vba and print specific rows into another sheet one after the other

Question

I have several data files that i need to process. My code uses a macro to go through each of the data files - run another macro to do some calculations - out put that result on the top row of the data file. Now I want specific column data from the top row from each file to be sent to another file - called summary so that i can have a dashboard looking sheet with relevant information about all the files in the folder.

My code is below. I am having difficulties with adding the data to the new file inside the for loop while the data is being manipulated the first time.

Sub LoopThroughFiles(dirName)
    Dim currWorkBook As String
    'Dim i As Integer ' for going to next row in mastersheet

    currWorkBook = ActiveWorkbook.Name

    FolderName = dirName ' enter the folder where all the files are located
    If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator

    'make sure you change the file extension here appropriately - *.xls or *.xlsm

    Fname = Dir(FolderName & "*.xl*")

    'loop through the files
    Do While Len(Fname)
        'For i = 4 To 6 ' to advance rows in the master sheet where the data is collected
            With Workbooks.Open(FolderName & Fname)

                Call IceNucleationTempCalc

                ' this is where i want the data from the sheet to be moved to the present workbook and sheet1
                ' and put it in different rows.. right now, the i cant march through the rows and the data keeps
                ' copied over and over on B4-K4. i know it is doing exactly what i am asking to do, but how to loop thru
                ' columns inside this while loop? if i use the i=4-6 is there a way to say .Range(B4, i)?

                Sheets("IceNuclTemp").Range("F1:O1").Copy Destination:=Workbooks(currWorkBook).Sheets("Sheet1").Range("B4")

            End With
            Fname = Dir  ' go to the next file in the folder
        'Next i
    Loop

End Sub

I feel like it is a simple thing i need to just change the way i am accessing those cells, but i cant figure it out.. Any help will be appreciated. thanks, Suresh.

Comintern · Accepted Answer · 2015-06-05 20:12:51Z

You need to grab some references to the workbooks and worksheets, then use them to copy your range. All you need to do is build a range on your target sheet using your row counter. Something like this:

Sub LoopThroughFiles(dirName)
    Dim currWorkBook As Workbook
    Dim target As Worksheet

    Set currWorkBook = ActiveWorkbook
    Set target = currWorkBook.Sheets("Sheet1")

    FolderName = dirName
    If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
    Fname = Dir(FolderName & "*.xl*")

    Dim foundWorkBook As Workbook
    Dim i As Long
    'Start at row 4
    i = 4
    Do While Len(Fname)
        Set foundWorkBook = Workbooks.Open(FolderName & Fname)
        With foundWorkBook
            'Modify this to take a workbook reference instead of using the ActiveWorkbook.
            IceNucleationTempCalc foundWorkBook
            target.Range("B" & i & ":" & "K" & i).Value = _
                .Sheets("IceNuclTemp").Range("F1:O1").Value
            i = i + i
        End With
        Fname = Dir
    Loop

End Sub

Note that you'll have to also modify IceNucleationTempCalc to take a Workbook as a passed parameter or provide some sort of wrapper that does - it's apparent from your code that it is also using the Active* objects.

Collectives™ on Stack Overflow

how to iteratively loop through files in vba and print specific rows into another sheet one after the other

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related