Macro to add Data From Multiple Workbooks to One Master

[PenLawLo]

Hello,
I was wondering if anyone could help me figure out this code. I have been struggling to figure out what is wrong with my code. My goal is for the macro to read through all the different workbooks in a folder I have. The code seems to work, but then when it loops, it instead decides to just loop the same workbook. Here is the code below and I am also a beginner so any help is appreciated. Thanks for the help!

Sub TransferData()
Dim wbFile As Workbook, wsData As Worksheet, wbDataBase As Workbook, wsDB As Worksheet,
Dim strPath As String, strFile As String
Dim DBPath As String, wbDB As String, DBFile As String
Dim DataPath As String, DBDataPath As String
Dim lngIdx As Long, lngLastRow As Long, lngLastCol As Long, lngLastRowDB As Long, lngLastColDB As Long
Dim lngDstLastRow As Long
Dim rngCopy As Range, rngPaste As Range
Dim colFileNames As Collection
Set colFileNames = New Collection
strPath = "H:\*FolderName*"
strFile = Dir(strPath & "\*xlsm")
DBPath = "H:\*FolderName2*"
DBFile = Dir(DBPath & "\*xlsm")

Do While Len(strFile) > 0
colFileNames.Add (strFile)
strFile = Dir
Loop


For lngIdx = 1 To colFileNames.Count
DataPath = strPath & "" & colFileNames(lngIdx)


Set wbFile = Workbooks.Open(DataPath)

Set wsData = wbFile.Worksheets("Data")

lngLastRow = LastRow(wsData)
lngLastCol = LastRow(wsData)

With wsData
Set rngCopy = .Range(.Cells(3, 2), .Cells(lngLastRow, 6))
End With

DBDataPath = DBPath & DBFile

Set wbDataBase = Workbooks.Open(DBDataPath)

Set wsDB = wbDataBase.Worksheets("DataBase")
lngLastRowDB = LastRow(wsDB)
lngLastColDB = LastCol(wsDB)

If lngIdx > 0 Then
lngDstLastRow = LastRow(wsDB)
Set rngPaste = wsDB.Cells(lngLastRowDB + 1, 2)
End If

rngCopy.Copy
wsDB.Cells(lngLastRowDB + 1, 2).Select
ActiveCell.PasteSpecial
ActiveWorkbook.Close

wsData.Select

ActiveWorkbook.Close
Next

End Sub

 

[JLGWhiz]

Is this correct?

lngLastRow = LastRow(wsData)
 lngLastCol = [COLOR=#ff0000]LastRow[/COLOR](wsData)

I would close the wbFile workbook before opening the new one.

With wsData
 Set rngCopy = .Range(.Cells(3, 2), .Cells(lngLastRow, 6))
 End With
[COLOR=#ff0000]'Close the wbFile workbook if no further need for it.  don't know which wb hosts the code.[/COLOR]
 DBDataPath = DBPath & DBFile

 

[PenLawLo]

I think there a problem with the collection. when I try to run through the macro step by step, I noticed that the collection only picks up one of the workbooks that are in the file instead of the total amount
(8 workbooks). It will just continue to run through the first and only workbook over and over again. Thanks for the help.

 

[PenLawLo]

Oh I forgot to include the code area here it is.

Code:
Do While Len(strFile) > 0
colFileNames.Add (strFile)
strFile = Dir
Loop

 

[JLGWhiz]

I think there a problem with the collection. when I try to run through the macro step by step, I noticed that the collection only picks up one of the workbooks that are in the file instead of the total amount
(8 workbooks). It will just continue to run through the first and only workbook over and over again. Thanks for the help.

I assumed that "*FolderName*" and "*FolderName2*" were placeholders for purposes of posting to this thread, If they are not, and you are attempting to use them as variables to complete the path, then you would need to declare the variables somewhere to define the folder path. Else your DIr function will return "". But I think you are correct about the collection. You don't really need to create the collection, you can open the workbooks using the Dir function in a similar manner to how you are attempting to create the collection.

 

[PenLawLo]

Yeah they are named differently on my account and are placeholders for the thread. The problem is it was running fine before but know it only wants to run one of the workbooks through the macro once through while it ignore all the other workbooks in the folder. Is it possibly a bug? Thanks

 

[JLGWhiz]

Yeah they are named differently on my account and are placeholders for the thread. The problem is it was running fine before but know it only wants to run one of the workbooks through the macro once through while it ignore all the other workbooks in the folder. Is it possibly a bug? Thanks

It probably is a bug, but not an application bug. Have you tried stepping through the code using the F8 function key while the vb editor is open to see if the variables are holding their values? You can see the values of the variables in the tool tips display by hovering the mouse pointer over them. You can also open the Immediate Window and it will show the values. If you diminish the editor screen size so you can overlay the Excel screen, you can also monitor what is happening on your worksheets and see if the correct sheets are displayed. Once you identify the area that needs fixing, you can post back for assistance, if required.

 

[PenLawLo]

It probably is a bug, but not an application bug. Have you tried stepping through the code using the F8 function key while the vb editor is open to see if the variables are holding their values? You can see the values of the variables in the tool tips display by hovering the mouse pointer over them. You can also open the Immediate Window and it will show the values. If you diminish the editor screen size so you can overlay the Excel screen, you can also monitor what is happening on your worksheets and see if the correct sheets are displayed. Once you identify the area that needs fixing, you can post back for assistance, if required.

Okay I look at the code and ran through the macro. It only runs through this loop one time. I think the problem is within either this or the variables values within the code. Thanks again for helping.
Code:
Dim colFileNames As Collection
Set colFileNames = New Collection
strPath = "H:\*FolderName*"
strFile = Dir(strPath & "\*xlsm")
DBPath = "H:\*FolderName2*"
DBFile = Dir(DBPath & "\*xlsm")

Do While Len(strFile) > 0
colFileNames.Add (strFile)
strFile = Dir
Loop

 

[bsquad]

Not trying to butt in JLGWhiz is solid and has answered of few questions of mine in the past;

below is a blanket code I use when importing multiple workbooks into one. With this the designation of your folder path is interactive or on the fly - you don't have to specify it in the code; it will open up a dialog box and you choose the files you want to import.

Sub Import()
    Dim eWorkbook, iWorkbook As Workbook
    Dim iWorkbookImportOpen As Variant
    Dim i As Integer
        ChDir eWorkbook.Path
            iWorkbookImportOpen = Application.GetOpenFilename(FileFilter:="Excel Workbooks(*.xlsx; *.xlsm; *.xls; *.xltm), *.xlsx; *.xlsm; *.xls; *.xltm", _
                                    Title:="Select Import Files", MultiSelect:=True)
                                    On Error Resume Next
        For i = LBound(iWorkbookImportOpen) To UBound(iWorkbookImportOpen)
            Set iWorkbook = Workbooks.Open(Filename:=iWorkbookImportOpen(k), ReadOnly:=True)
'****************************** USER CODE   ********************************************************
        Next i
End Sub

 

[JLGWhiz]

Okay I look at the code and ran through the macro. It only runs through this loop one time. I think the problem is within either this or the variables values within the code. Thanks again for helping.
Code:
Dim colFileNames As Collection
Set colFileNames = New Collection
strPath = "H:\*FolderName*"
strFile = Dir(strPath & "\*xlsm")
DBPath = "H:\*FolderName2*"
DBFile = Dir(DBPath & "\*xlsm")

Do While Len(strFile) > 0
colFileNames.Add (strFile)
strFile = Dir
Loop

That is how you have the code written and if the collection is actualy built, then you only need it to run once for that directory. I still question whether your collection is being loaded with more than one workbook. In your strFile initialization statement you are using xlsm as a filter for the Dir function. That means that only files with the xlsm (macro enabled workbooks) will be identifide with the Dir function to be part of the collection. You do not have a collection built for the other directory, nor do you attempt to use the Dir function to select a second file from the second directory, although in your For ... Each loop, you are opening that same workbook for each iteration. Frankly the code is quite erratic as written. If you can clarify exactly what you are attempting to do, I will try to write something that will maybe work. You need to be clear on whether you want to see all excel files in the folder, and if all the workbooks from both folders or all from one and one from the other or what? Only xlsm file extensions or any xls* file extension? I think I can figure out the rest.