Sync data in two different excel files

[Stefano_1990]

Hi guys

I try to outline it as clearly as I can:
File1 and File2 contain 2 columns, part number and description.
File1 has one worksheet while File2 has three.

In File1 there are 2000 components and in File2 there are only about 300.

All I need to do is sync all three worksheets in File2 based on the data in File1 but without adding additional columns. So only update the 300 rows that are in File2. I was trying to figure out how to do that half the day. I have never done any visual basic programming in my life..

I came to you guys because I have the feeling that you guys would solve this in a matter of minutes what takes me hours...

I really appreciate your help.

Kind regards
Stefano

 

[Saagar]

Provide examples of data in each file and and the provide an example of how you want them synced. That will help us understand what needs to be done.

 

[Stefano_1990]

Ok will do:

[B]File1:[/B]
[B]Worksheet 1:[/B]
| part (linked) | description | etc. (like #, price, ...) |
| B1            | foobar      | ..... |
| D2            | foobar 2    | ..... |
 
[B]File2:[/B]
[B]Worksheet 1, 2, 3:[/B]
| part (not linked) | description | etc. |
| D2                | foobar      | ..... |
| C4                | foobar      | ..... |
| A2                | foobar      | ..... |

(So in this example only D2 in file2 would have to be synced with data from file1.)

What needs to be done:
Look up part # from File2 in File1 (for every sheet in File2). Copy all the infos in File1 into File2. If it doesnt exist simply give #N/A or skip.

I kind of managed to implement it with VLOOKUP but its extremly slow on my computer. I was hoping there was a faster way.

Also a nice feature would be that if a row exists in File1&File2 and you edit it in File2 it would get copied over into File1.

Thanks for the help guys. I know nothing about Visual Basic. If anybody has any question about ruby on rails I am more than happy to help though

Regards
Stefano

 

[Saagar]

Here is the solution for the first part. Solution is based on Excel2003. If you have a different version of Excel the code will still work but the menus and options may be different.

In File2, on worksheet1, add a button from the "control toolbox". Double click the button. A VB window will appear and you will see something like this:

Private Sub CommandButton1_Click()

End Sub

Copy paste this code between the above lines:

    thisfile = ThisWorkbook.Name
    shts = Array("Sht1", "Sht2", "Sht3")

    sourcefile = "Excel Macros2.xls"
    sourcesht = "sht123"
    
    j = 0
    startrow = 2
    
    Windows(sourcefile).Activate
    Sheets("Sht123").Range("A:A").Select
    Selection.AutoFilter
    
    For j = 0 To UBound(shts)
        Windows(thisfile).Activate
        lastrow = Sheets(shts(j)).Range("A63756").End(xlUp).Row
        For i = startrow To lastrow
            Windows(thisfile).Activate
            Sheets(shts(j)).Select
            
            filtercriteria = "=" & Sheets(shts(j)).Cells(i, 1)
            
            Windows(sourcefile).Activate
            Sheets("Sht123").Select
           
            Set searchrange = Sheets("Sht123").Range("A:A")
            entrycount = WorksheetFunction.CountIf(searchrange, filtercriteria)
            
            If entrycount > 0 Then
           
                Selection.AutoFilter Field:=1, Criteria1:=filtercriteria, Operator:=xlAnd
                Sheets("Sht123").Range("A1").Select
                getrow = Sheets("Sht123").Range("A1").End(xlDown).Row
                Sheets("Sht123").Range("C" & getrow, "G" & getrow).Select
                Application.CutCopyMode = False
                Selection.Copy
        
                Windows(thisfile).Activate
                Sheets(shts(j)).Cells(i, "C").Select
                ActiveSheet.Paste
            Else
                Windows(thisfile).Activate
                Sheets(shts(j)).Cells(i, "C") = "Not found"
            End If
            
        Next
    Next

Update the red lines above in the code with your details.
- replace "Sht1", "Sht2", "Sht3" with names of the three worksheets in File2
- sourcefile is the name of File1. Replace "Excel Macros2.xls" with your file name.
- replace sht123 with the name of the sheet containing master data in File1

Look at the parameters in blue color in the code above. The code will copy data from columns C to columns G in File1 starting from Col C in File2 in respective sheets. Change values as it fits your needs.

Save and close the VB window. In the "control toolbox" toolbar there should be a button to "exit design mode". Click on it for the button to exit design mode.

File1 should be open for the code to work. When you click on the button the code should run and do what you want.

If you need to modify the code, right click on the tab name and select "view code" option.

Be patient and try to follow all steps. Let me know if you have any issues.

 

[Stefano_1990]

Hi

Thank you for your help.

I think there is a typo in the code because you only highlighted the first 'Sht123'. If I only change it the first time I get an 'out of range' error. If I change it every time and start it the two files start flickering forth and back and all the stuff in File1 gets replaced with 'not found' which is not really what I need haha.

So erm.. I'm kind of stuck.

Thank you for your help.

Kind regards
Stefano

Here is the solution for the first part. Solution is based on Excel2003. If you have a different version of Excel the code will still work but the menus and options may be different.

In File2, on worksheet1, add a button from the "control toolbox". Double click the button. A VB window will appear and you will see something like this:

Private Sub CommandButton1_Click()

End Sub

Copy paste this code between the above lines:

    thisfile = ThisWorkbook.Name
    shts = Array("Sht1", "Sht2", "Sht3")

    sourcefile = "Excel Macros2.xls"
    sourcesht = "sht123"
    
    j = 0
    startrow = 2
    
    Windows(sourcefile).Activate
    Sheets("Sht123").Range("A:A").Select
    Selection.AutoFilter
    
    For j = 0 To UBound(shts)
        Windows(thisfile).Activate
        lastrow = Sheets(shts(j)).Range("A63756").End(xlUp).Row
        For i = startrow To lastrow
            Windows(thisfile).Activate
            Sheets(shts(j)).Select
            
            filtercriteria = "=" & Sheets(shts(j)).Cells(i, 1)
            
            Windows(sourcefile).Activate
            Sheets("Sht123").Select
           
            Set searchrange = Sheets("Sht123").Range("A:A")
            entrycount = WorksheetFunction.CountIf(searchrange, filtercriteria)
            
            If entrycount > 0 Then
           
                Selection.AutoFilter Field:=1, Criteria1:=filtercriteria, Operator:=xlAnd
                Sheets("Sht123").Range("A1").Select
                getrow = Sheets("Sht123").Range("A1").End(xlDown).Row
                Sheets("Sht123").Range("C" & getrow, "G" & getrow).Select
                Application.CutCopyMode = False
                Selection.Copy
        
                Windows(thisfile).Activate
                Sheets(shts(j)).Cells(i, "C").Select
                ActiveSheet.Paste
            Else
                Windows(thisfile).Activate
                Sheets(shts(j)).Cells(i, "C") = "Not found"
            End If
            
        Next
    Next

Update the red lines above in the code with your details.
- replace "Sht1", "Sht2", "Sht3" with names of the three worksheets in File2
- sourcefile is the name of File1. Replace "Excel Macros2.xls" with your file name.
- replace sht123 with the name of the sheet containing master data in File1

Look at the parameters in blue color in the code above. The code will copy data from columns C to columns G in File1 starting from Col C in File2 in respective sheets. Change values as it fits your needs.

Save and close the VB window. In the "control toolbox" toolbar there should be a button to "exit design mode". Click on it for the button to exit design mode.

File1 should be open for the code to work. When you click on the button the code should run and do what you want.

If you need to modify the code, right click on the tab name and select "view code" option.

Be patient and try to follow all steps. Let me know if you have any issues.

 

[Saagar]

I am sorry - you have to change "sht123" at all places. I was planning on using "sourcesht" variable at other places in the code but I missed it.

Did you include the code in file1 or file2? Based on what you said it seems like you put the code in file1. You need to put it in file2.

Check it and let me know.

 

[Stefano_1990]

Thanks Saagar

This actually works
Am I right in thinking that with a little modification I can use the same code in both files?

So basically:
Open file -> Check out (pushing the button) -> Edit -> Save
When editing the other file:
Open file -> Check out -> Edit -> Save

Since excel locks the file on opening it cant happen anyway that the file gets edited by two people at the same time.

 

[Stefano_1990]

Sorry for double post but I couldnt find an edit function

Do you think its possible to compare the two rows before copying them? This way it would only copy the rows if there is a difference between them. It would also speed up the checking I imagine. (I have about 10000 rows in total).

I tried the 'rowDifference' function but just... this visual basic stuff... it hurts my eyes, my brain, everything. I would encourage everybody on this forum to check out ruby on rails to get an idea for how pleasent programming can be compared to VB

Thats why I appreciate your help so much.

Kind regards
Stefano

 

[Saagar]

What are you referring to by "edit function". What are you trying to edit?

With regards to your other question. Yes - with some modifications you can create new code to update data in file1 based on updates in file2. In my mind compare and copy might be slower - you need to extract data from both files, compare all cells, then paste if things changed. Instead I would recommend to just copy, that way you just extract data from one file and paste it in another file.

Since you seem to have coding experience, try to modify the existing code yourselves. I will help you along the way if you get stuck.

 

[Stefano_1990]

Hi

With edit I meant a way to edit my post.

Actually I realized that File1 is shared anyway. And apparently that means that excel does not allow macros to be run in a shared file. So that is kind of out of question.

About comparing and copying I guess you are right. I am not that much of a fan of visual basic that I would love to experiment around with this. As I mentioned before I'm a ruby on rails man.

Thank you for your help.

Kind regards
Stefano