+ Reply to Thread
Results 1 to 30 of 30

Updating values in an excel file with data from multiple excel files

Hybrid View

  1. #1
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Updating values in an excel file with data from multiple excel files

    Hi all,

    i'm new here, and i hope someone can help me. The problem is that i have a file with thousands of rows, and a couple of dozens of columns, that needs to be updated everyday with data from 2-5 other excel files. is there a macro to do this update using the Date (today date) as criterion? i attached some test files, to see what i'm talking about. thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Hi ScarfaceLex

    Welcome to the Forum!

    Are all files in the same folder? Are there any other files in the folder other than the Master File and these other 2-5 Data Files?
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    all the files are in the same folder and there are not any other files in the folder.

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Hi ScarfaceLex

    Try this code in a General Module...let me know of issues.
    Option Explicit
    
    Sub Merge_Me()
        Dim wk1 As Workbook
        Dim wk2 As Workbook
        Dim ws1 As Worksheet
        Dim Rng As Range
        Dim lr1 As Long
        Dim lr2 As Long
        Dim lc2 As Long
        Dim x As Long
        Dim myPath As String
        Dim MyFile As String
    
        Set wk1 = ThisWorkbook
        Set ws1 = wk1.Sheets("Sheet1")
        myPath = wk1.Path & "\"
        MyFile = Dir(myPath)
    
        Application.ScreenUpdating = False
        Do While MyFile <> ""
            If MyFile <> wk1.Name Then
                With ws1
                    lr1 = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row + 1
                End With
    
                Workbooks.Open myPath & MyFile
                Set wk2 = ActiveWorkbook
                With Sheets(1)
                    lc2 = Cells.Find(What:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
                    lr2 = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row
                    If Not .AutoFilterMode Then
                        .Range("A1").AutoFilter
                    End If
                    .Range(.Cells(1, 1), .Cells(lr2, lc2)).AutoFilter Field:=7, Criteria1:= _
                            xlFilterToday, Operator:=xlFilterDynamic
                    Set Rng = .AutoFilter.Range
                    x = Rng.Columns(1). _
                            SpecialCells(xlCellTypeVisible).Count - 1
                    If x >= 1 Then
                        .AutoFilter.Range.Offset(1, 0).Copy
                        ws1.Range("A" & lr1).PasteSpecial
                    End If
                End With
                Application.CutCopyMode = False
                wk2.Close False
            End If
            MyFile = Dir
        Loop
        Application.ScreenUpdating = True
    End Sub

  5. #5
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    hello, thanks for working at my problem.
    your code works good...it does what it should do, but it still doesn't solve my problem. let me explain it better. files 1 and 2 have the exact same data as the master file, but in some cases some data is updated manually (newentry/newinfo) . well, now these new entries must replace the old data in the master file. i hope i explained better this time.

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Hi ScarfaceLex
    What's the key between FILE 1 and MASTER FILE? What's the key between FILE 2 and MASTER FILE? I'm missing something...I don't see the link among the files.

  7. #7
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    i uploaded new files to see exactly what it's all about. each day, a copy of master file is given to some operators. they call at different companies in the database (master file) and they collect data ( eg : columns B, H, i, J). i marked with red the updated data in files 1 and 2. then i have to replace the old data in the master file (i receive a clean copy before they start calling companies) with the red marked data in files 1 and 2 . the new file (master file - updated)becomes master file for the next day, and so on.
    each day, i filter the data in the files 1 and 2 after the Date (col. G), and then i have to copy-paste manually hundreds of entries in the master file.
    Attached Files Attached Files

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Hi ScarfaceLex
    Try this code...gotta warn you...may take some time to run with thousands of rows. Let me know of issues.
    Option Explicit
    
    Sub Merge_Me()
        Dim wk1 As Workbook
        Dim wk2 As Workbook
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim fRng As Range
        Dim Rng As Range
        Dim cel As Range
        Dim lr1 As Long
        Dim lr2 As Long
        Dim lc1 As Long
        Dim lc2 As Long
        Dim x As Long
        Dim myPath As String
        Dim MyFile As String
        Dim FindString As String
    
        Set wk1 = ThisWorkbook
        Set ws1 = wk1.Sheets("Sheet1")
        myPath = wk1.Path & "\"
        MyFile = Dir(myPath)
    
        Application.ScreenUpdating = False
        Do While MyFile <> ""
            If MyFile <> wk1.Name Then
                With ws1
                    lr1 = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row + 1
                    lc1 = Cells.Find(What:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
                End With
    
                Workbooks.Open myPath & MyFile
                Set wk2 = ActiveWorkbook
                Set ws2 = wk2.Sheets("Sheet1")
                With ws2
                    lc2 = Cells.Find(What:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
                    lr2 = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row
                    If Not .AutoFilterMode Then
                        .Range("A1").AutoFilter
                    End If
                    .Range(.Cells(1, 1), .Cells(lr2, lc2)).AutoFilter Field:=7, Criteria1:= _
                            xlFilterToday, Operator:=xlFilterDynamic
                    Set Rng = .AutoFilter.Range
                    x = Rng.Columns(1). _
                            SpecialCells(xlCellTypeVisible).Count - 1
                    If x >= 1 Then
                        Set Rng = .Range(.Cells(2, 1), .Cells(lr2, 1)).SpecialCells(xlCellTypeVisible)
                        For Each cel In Rng
                            FindString = cel.Value
                            With ws1.Columns("A:A")
                                Set fRng = .Find(What:=FindString, _
                                        After:=.Cells(.Cells.Count), _
                                        LookIn:=xlValues, _
                                        LookAt:=xlWhole, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=False)
                                If Not fRng Is Nothing Then
                                    ws2.Range(Cells(fRng.Row, fRng.Column), Cells(fRng.Row, lc1)).Copy
                                    ws1.Cells(cel.Row, cel.Column).PasteSpecial
                                End If
                            End With
                        Next cel
                    End If
                End With
                Application.CutCopyMode = False
                wk2.Close False
            End If
            MyFile = Dir
        Loop
        Application.ScreenUpdating = True
    End Sub

  9. #9
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Hi jaslake,
    i don't see any changes in the master file after running the code. i should do anything else than just running the code?

    i thought at something else...you think that it's easier to do this in microsoft access? i tried to make a db in access but all i succeded was to merge the files, not to replace the old data with new ones.

    thanks again for working at this...i already owe you a couple of beers
    Last edited by ScarfaceLex; 07-16-2012 at 03:05 AM.

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Hi ScarfaceLex
    If your files don't contain any records with today's date you won't see any change to the Master File.

  11. #11
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    hi jaslake,
    you are right i tested your code on real files, and also on test files, and in both cases i get this error : Run-time error 1004, Application-defined or object-defined error.

  12. #12
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Hi ScarfaceLex

    Hmm...I just reran the code and didn't get any errors. What line of code?
    Attached Files Attached Files
    Last edited by jaslake; 07-16-2012 at 01:43 PM. Reason: Attach Files

  13. #13
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    when i try to run from ln 66 to ln 67 i get that error.

  14. #14
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    How about posting a screen shot of the debug screen so I can see the actual line.

  15. #15
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    i attached some images. i hope it helps.
    Attached Images Attached Images

  16. #16
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Place the three files I attached into a separate folder, open Master File and click the button...let me know if you get the error. I can't duplicate the error on these files.

  17. #17
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    You're welcome...glad it works for you. Did you get it to work on your live files?

    how can i repay you for working at this
    Click on the star at the lower left of one of my posts will be thanks enough...also, if this satisfies your need please mark your Thread as SOLVED.
    Last edited by jaslake; 07-16-2012 at 04:32 PM.

  18. #18
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    oh my god...everything works fabulous on your files. this is exactly what i needed. damn...you are a genius, man.
    i don't know why it works on your files but it's absolutely fantastic. you have just saved me from hours of assiduous work. i owe you big time, tell me how can i repay you for working at this.

  19. #19
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    unfortunately, when i run the code on my real files, i get an error : "subscript out of range", at line 38.

  20. #20
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Hi ScarfaceLex

    I don't have line numbers in my copy of the code...attach an image of the debug screen and I'll look at it. That error message typically means a non-existent object has been called.

  21. #21
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    hi jaslake,
    i solved that problem, the code runs without errors but nothing happens in the end. can i send you my real files in private, to take a look at them?

  22. #22
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Sure...I'll PM the email address.

  23. #23
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files - PLEASE HELP

    Hi ScarfaceLex

    In your files attached to this Thread the key among the files is Column A. In the files you emailed me the key is not Column A. Which Column has unique (not duplicated) data?

  24. #24
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files

    Column C is the one with unique data. what should be changed in the code?

  25. #25
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files

    There are four lines of code that require change:

    If x >= 1 Then
                        
                        Set Rng = .Range(.Cells(4, 3), .Cells(lr2, 3)).SpecialCells(xlCellTypeVisible) <--------
    '                    Set Rng = .Range(.Cells(2, 1), .Cells(lr2, 1)).SpecialCells(xlCellTypeVisible)
                        
                        For Each cel In Rng
                            FindString = cel.Value
                            
                            With ws1.Columns("C:C") <---------
    '                        With ws1.Columns("A:A")
    
                                Set fRng = .Find(What:=FindString, _
                                        After:=.Cells(.Cells.Count), _
                                        LookIn:=xlValues, _
                                        LookAt:=xlWhole, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=False)
                                If Not fRng Is Nothing Then
                                    
                                    ws2.Range(Cells(fRng.Row, 1), Cells(fRng.Row, lc1)).Copy <----------
                                    ws1.Cells(cel.Row, 1).PasteSpecial                       <---------
                                    
    '                                ws2.Range(Cells(fRng.Row, fRng.Column), Cells(fRng.Row, lc1)).Copy
    '                                ws1.Cells(cel.Row, cel.Column).PasteSpecial
                                End If
                            End With
                        Next cel
                    End If
    I ran the revised code but can't tell if changes have been applied (too much data). I know it's doing a copy/paste operation. Here's the complete revised code...test it and let me know of issues.
    Option Explicit
    
    Sub Merge_Me()
        Dim wk1 As Workbook
        Dim wk2 As Workbook
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim fRng As Range
        Dim Rng As Range
        Dim cel As Range
        Dim lr1 As Long
        Dim lr2 As Long
        Dim lc1 As Long
        Dim lc2 As Long
        Dim x As Long
        Dim myPath As String
        Dim MyFile As String
        Dim FindString As String
    
        Set wk1 = ThisWorkbook
        Set ws1 = wk1.Sheets("Sheet1")
        myPath = wk1.Path & "\"
        MyFile = Dir(myPath)
    
        Application.ScreenUpdating = False
        Do While MyFile <> ""
            If MyFile <> wk1.Name Then
                With ws1
                    lr1 = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row + 1
                    lc1 = Cells.Find(What:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
                End With
    
                Workbooks.Open myPath & MyFile
                Set wk2 = ActiveWorkbook
                Set ws2 = wk2.Sheets("Sheet1")
                With ws2
                    lc2 = Cells.Find(What:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
                    lr2 = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row
                    If Not .AutoFilterMode Then
                        .Range("A1").AutoFilter
                    End If
                    .Range(.Cells(1, 1), .Cells(lr2, lc2)).AutoFilter Field:=17, Criteria1:= _
                            xlFilterToday, Operator:=xlFilterDynamic
                    Set Rng = .AutoFilter.Range
                    x = Rng.Columns(1). _
                            SpecialCells(xlCellTypeVisible).Count - 1
                    If x >= 1 Then
                        
                        Set Rng = .Range(.Cells(4, 3), .Cells(lr2, 3)).SpecialCells(xlCellTypeVisible)
    '                    Set Rng = .Range(.Cells(2, 1), .Cells(lr2, 1)).SpecialCells(xlCellTypeVisible)
                        
                        For Each cel In Rng
                            FindString = cel.Value
                            
                            With ws1.Columns("C:C")
    '                        With ws1.Columns("A:A")
    
                                Set fRng = .Find(What:=FindString, _
                                        After:=.Cells(.Cells.Count), _
                                        LookIn:=xlValues, _
                                        LookAt:=xlWhole, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=False)
                                If Not fRng Is Nothing Then
                                    
                                    ws2.Range(Cells(fRng.Row, 1), Cells(fRng.Row, lc1)).Copy
                                    ws1.Cells(cel.Row, 1).PasteSpecial
                                    
    '                                ws2.Range(Cells(fRng.Row, fRng.Column), Cells(fRng.Row, lc1)).Copy
    '                                ws1.Cells(cel.Row, cel.Column).PasteSpecial
                                End If
                            End With
                        Next cel
                    End If
                End With
                Application.CutCopyMode = False
                wk2.Close False
            End If
            MyFile = Dir
        Loop
        Application.ScreenUpdating = True
    End Sub

  26. #26
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files

    it works perfectly. so, basically, if i want to adjust the code for another database i should change those four lines, yeah?
    Last edited by ScarfaceLex; 07-17-2012 at 02:22 PM.

  27. #27
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files

    I can't say with certainty...too many unknowns...but probably, quite possibly. Best I can suggest is try it...you may like the results. If you don't come back to the Forum.

  28. #28
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files

    ok, i'll do that thank you again for losing your time with me you are a great man.

  29. #29
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Updating values in an excel file with data from multiple excel files

    You're welcome...glad to be of help.

    One thing you should be aware of...your files contain a duplicate some where in Column C. Column C contains 3185 records. When you do an Advanced Filter for unique records the count is 3184 records.

    If this satisfies your need please mark your Thread as SOLVED.

  30. #30
    Registered User
    Join Date
    07-15-2012
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Updating values in an excel file with data from multiple excel files

    ok, thanks for telling me. i'll have a look at it.
    i'll mark the thread as solved.

+ Reply to Thread

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