Data ingest from multiple workbooks into a master workbook

[rpolasky]

Hey all, I have multiple workbooks ( Survey_RP.xlsm, Survey_CH.xlsm, etc.. They all start with "Survey" and have a "_" and then the individuals initials next to them ) in 1 file location. Inside each workbook ( They are all the same ) there is a Sheet labeled "results". In that sheet there is only a little data (A1:C7).

I would like to run a query inside the master workbook where it will open all the other excel Survey workbooks and transfer the data from Sheet "results" in Cells A1:C7 and place that data into the master workbook. So in the master workbook there would be only three columns of data A-C and it would keep filling up the rows downward with the data from the other workbooks.

I hope that makes sense.

I have tried youtube videos on the VBA coding, but I must not be doing it correctly.

Thanks for the help.

RP

 

[JLGWhiz]

The procdure assumes all workbooks, including the master, are in the same directory. The PasteSpecial method is used in case there are formulas in the source document.

Sub Survey()
Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "Survey*.xl*")
If fName = "" Then Exit Sub
    Do
        If fName <> ThisWorbook.Name Then
            On Error GoTo SKIP:
            Set wb = Workbooks.Open(fPath & fName)
            wb.Sheets("Results").Range("A1:C7").Copy
            ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
            wb.Close False
SKIP:
            If Err.Number > 0 Then
                MsgBox Err.Number & ":  " & Err.Description & vbCrLf & "To continue, click OK"
                Err.Clear
            End If
        End If
        fName = Dir
    Loop While fNamm <> ""
End Sub

The error trap is built in so that if an anomally occurs in one workbook, you can continue with the procedure and fix the anomally later.

 

[rpolasky]

I put the code in. I changed the fname = Dir(fpath & "Survey*.xl*") to fname = dir(fpath & "results.xlsm") [that is the name of the master workbook]

debug error came up highlighted yellow over the command "If fname <> THisworkbook.name then"

was I supposed to change something else?

Edit: I also tried it as is, assuming the "Survey*.xl*" was intended in reference to the other workbooks.. same error.

RP

 

[JLGWhiz]

I put the code in. I changed the fname = Dir(fpath & "Survey*.xl*") to fname = dir(fpath & "results.xlsm") [that is the name of the master workbook]

debug error came up highlighted yellow over the command "If fname <> THisworkbook.name then"

was I supposed to change something else?

Edit: I also tried it as is, assuming the "Survey*.xl*" was intended in reference to the other workbooks.. same error.

RP

You were not supposed to change anything. fName is a variable that captures the names of the workbooks generated by the DIR function. Correct the spelling of workbook int that statement and it should work OK.

 

[rpolasky]

I didn't even think to check spelling. I changed it and it works, but it only pulls the data from the first workbook.

 

[rpolasky]

nevermind. code works great I just found at the end fnamm should have been fname

thanks a TON!

RP

 

[JLGWhiz]

Just shows how testing these things can eliminate a lot of errors.
Glad you got it going.
Regards, JLG