Automatic Updating Of Master Worbook From Slave Workbooks

daniel.c · Sep 12th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

It is important to be sure that there are no data in columns A:D in the master workbook. As the 2 slave workbooks don't have the same structure, I can't use a loop and there must be two different treatments.

Code

Private Sub Workbook_Open()
    Dim Plage As Range, c As Range, Fich As String
    Const myPath = "e:\donnees\daniel\mpfe\test\"
    With Sheets("Not Updated Master Workbook")
        Fich = Dir(myPath & "*.xls")
        Do While Fich <> ""
            If Fich = "SlaveBook.xls" Then
                .Range("E1:AA1").AutoFilter
                Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=23, Criteria1:="="
                Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=10, Criteria1:="M"
                Set Plage = .Range("_filterdatabase").Offset(1)
                If Plage.Resize(, 1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                    Set Plage = Plage.Resize(Plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
                    Workbooks.Open myPath & "SlaveBook.xls"
                    For Each c In Plage
                        c.Offset(, 22) = Application.VLookup(c, [E:AA], 23, 0)
                        c.Offset(, 22).NumberFormat = "dd/mm/yy"
                    Next c
                    Workbooks(Fich).Close False
                End If
                .Range("E1:AA1").AutoFilter
            ElseIf Fich = "SlaveBook2.xls" Then
                .Range("E1:AA1").AutoFilter
                Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=23, Criteria1:="="
                Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=10, Criteria1:="I"
                Set Plage = .Range("_filterdatabase").Offset(1)
                If Plage.Resize(, 1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                    Set Plage = Plage.Resize(Plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
                    Workbooks.Open myPath & "SlaveBook2.xls"
                    For Each c In Plage
                        c.Offset(, 22) = Application.VLookup(c, [A:J], 10, 0)
                        c.Offset(, 22).NumberFormat = "dd/mm/yy"
                    Next c
                    Workbooks(Fich).Close False
                End If
                .Range("E1:AA1").AutoFilter
            End If
            Fich = Dir
        Loop
    End With
End Sub

Display More

christianhau · Sep 15th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Sorry for the late reply, a weekend without internet connection

The problem is that all the other columns will have data in them, they might be empty but they might also have data in them. So the point is to have this working without any interference with the other columns, is that possible? I only say this since you stated in you last post that column A:D could not contain any data, and in my original worbook every column from A to AF will contain data.

I am really making this hard for you aren't I, please ask me if anything is not clear from my side, I will be more than happy to explain!

daniel.c · Sep 15th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Sorry have to written such a nonsense. It really does not matter to have data in columns A:D. Next time I'll Have a coffee before answering

christianhau · Sep 15th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Jeezus, it works perfectly!! Thank you so much! You are a VBA god

**Ger Plante** · Sep 15th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

I'm sure God will be pleased that his son Jesus is doing such good VBA work. A miracle worker and VBA programmer... one of the same really ::D

christianhau · Sep 17th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Hi again! Sorry to be bothering you..

As I said the code works flawlessly but I was wondering if you could help me with adressing some performance issues? Right now my two slave workbooks contain about 3500 rows each that is checked with the code. This takes approximately 10 minutes to do and I was hoping to get the performance up a bit. Do you have any suggestions to where in the code the program might hang or perform poorly?

Thanks!!

daniel.c · Sep 17th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Hi.
In the two macros, add the following lines at the beginning of the macro :

Code

Application.ScreenUpdating = False
    Calc = Application.Calculation
    Application.Calculation = xlCalculationManual

and, at the end, add :

Code

Application.Calculation = Calc
    Application.ScreenUpdating = True

At the top of the module, add :

Code

Public Calc As Long

christianhau · Sep 17th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

excellent! That solved it! Would have done a search on it but I didn't think it would be that easy. Reminds me of the turbobutton I had on my old computer, thanks again!

christianhau · Oct 8th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Hi again! I was hoping you would grant me one last favour. Can you comment your code? I am trying to customize it a bit since there is atleast one more date field that has to be compared in the same manner as what the code is doing right now. But I am struggling with understanding your code so if you could explain it I would be veeeery grateful. If you don't have the time I can always ask a direct question as well, let me know!

daniel.c · Oct 8th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Hope this will be OK. I forgot some things :

Code

Private Sub Workbook_Open()
    Dim Plage As Range, c As Range, Fich As String
    Const myPath = "e:\donnees\daniel\mpfe\test\"
    With Sheets("Not Updated Master Workbook")
        ' opens first workbook in the folder defined by "Const"
        Fich = Dir(myPath & "*.xls")
        'Loop into the folder
        Do While Fich <> ""
            ' case workbook is "SlaveBook.xls"
            If Fich = "SlaveBook.xls" Then
                'set autofilter
                .Range("E1:AA1").AutoFilter
                ' Set filter on column AA
                Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=23, Criteria1:="="
                ' set filter on column O
                Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=10, Criteria1:="M"
                ' define "Plage" as database offset 1
                Set Plage = .Range("_filterdatabase").Offset(1)
                ' case at least one row is filtered out
                If Plage.Resize(, 1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                    ' assign filtered rows, first column only to Plage
                    Set Plage = Plage.Resize(Plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
                    ' open workbook
                    Workbooks.Open myPath & "SlaveBook.xls"
                    'Loops into Plage
                    For Each c In Plage
                        c.Offset(, 22) = Application.VLookup(c, [E:AA], 23, 0)
                        c.Offset(, 22).NumberFormat = "dd/mm/yy"
                    Next c
                    'Close File
                    Workbooks(Fich).Close False
                End If
                'reset autofilter
                .Range("E1:AA1").AutoFilter
            ElseIf Fich = "SlaveBook2.xls" Then
                ' same thing for the other workbook
                .Range("E1:AA1").AutoFilter
                Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=23, Criteria1:="="
                Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=10, Criteria1:="I"
                Set Plage = .Range("_filterdatabase").Offset(1)
                If Plage.Resize(, 1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                    Set Plage = Plage.Resize(Plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
                    Workbooks.Open myPath & "SlaveBook2.xls"
                    For Each c In Plage
                        c.Offset(, 22) = Application.VLookup(c, [A:J], 10, 0)
                        c.Offset(, 22).NumberFormat = "dd/mm/yy"
                    Next c
                    Workbooks(Fich).Close False
                End If
                .Range("E1:AA1").AutoFilter
            End If
            Fich = Dir
        Loop
    End With
End Sub

Display More

If you have any trouble with it, please, post the workbooks again to refresh my mind.
Daniel

Dave Hawley · Oct 9th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Daniel, you are a Saint, Gentlemen and a Schollar.

umeshbapat · May 29th 2013

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Hi Christien,

Can you please post the final version of this?

I am solving the same problem like this.

Thanks,
Umesh

Automatic Updating Of Master Worbook From Slave Workbooks

Participate now!

Share