Automatic Updating Of Master Worbook From Slave Workbooks

christianhau · Sep 2nd 2008

Hi!

I have basically read all the posts on similar subjects and have tried for a few days to make it work but my solution is far from good enough. So now I am asking for you help.

My project: I have a Master workbook that contains the complete sales for the entire business. This is based on input from three different departments. These three departments have their own Excel workbook that they enter information into. The four files are all located in the same directory on a network folder.

What I want to do is to automatically gather all the entries from the three slave workbooks to the master workbook whenever the macro I am trying to create is run. There is no way of knowing how many new entries each workbook will contain and they have to be added so that that they don't overwrite eachother and so that they are put in sequence after the last row in the master workbook.

Now the information to be gathered is located on the first worksheet in all the three workbooks. The destination sheet in the Master workbook is also the first worksheet in the workbook. The structure of the information that I want to copy is equal on all the worksheets meaning that one sale is entered as a new row in the sheet. It is this row that I want to copy to the mastersheet.

What makes it a bit more complicated is that I want to extract certain information from one sheet and different information from other sheets. From one sheet i want to copy the entire row and from another sheet I just want to copy certain figures like Order Number, Customer, Price and so on. Meaning that I will have a different set of what I want to copy depending on what workbook I am copying from.

Here is what I have so far, it is not working by far and I tried to aim for something simple to start with since my vba experience is limited. So far I cannot copy anything into my Destination master file.

Code

Sub Merge()
    Dim i As Integer
    Dim Source As Workbook
    Dim Destination As Workbook


    
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
     
    On Error Resume Next
     
    Set Destination = ThisWorkbook
    
     
    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "C:\Documents and Settings\nochhau\Desktop\Excelprosjekt"
        .FileType = msoFileTypeExcelWorkbooks
        
         
        If .Execute > 0 Then 'Workbooks in folder
    For i = 1 To .FoundFiles.Count 'Loop through all
         'Set Paste target first
        With Destination.Sheets(1)
            '''''' Here I would then have to make an if to do a different selection based on what input file I have
            Set Tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
        End With
         'Open Souce file
        Set Source = Workbooks.Open(.FoundFiles(i))
         'Copy and paste to destination
        Source.Sheets(i).Range("A1").Copy Tgt
         'Close source file without saving
        Source.Close False
    Next i
    
    End If


    End With
     
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.CutCopyMode = False
End Sub

Display More

So if anyone can help me I would be very grateful!

daniel.c · Sep 2nd 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Not sure it's quite what you want... Try :

Code

Sub Merge()
    Dim i As Integer
    Dim Source As Workbook
    Dim Destination As Workbook
    Dim myFileName As String
     
     
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
     
    On Error Resume Next
     
    Set Destination = ThisWorkbook
     
     
    With Application.FileSearch
        .NewSearch
         'Change path to suit
        '.LookIn = "C:\Documents and Settings\nochhau\Desktop\Excelprosjekt"
        .LookIn = "e:\donnees\daniel"
        .FileType = msoFileTypeExcelWorkbooks
         
         
        If .Execute > 0 Then 'Workbooks in folder
            For i = 1 To .FoundFiles.Count ' Loop through all
                 'Set Paste target first
                myFileName = Dir(.FoundFiles(i))
                
                'Open Souce file
                Set Source = Workbooks.Open(.FoundFiles(i))
                With Destination.Sheets(1)
                     '''''' Here I would then have to make an if to do a different selection based on what input file I have
                    Select Case myFileName
                    Case "dept1.xls"
                        'Here selection for first file
                    Case "dept2.xls"
                        'Here selection for second file
                    Case "dept3.xls"
                        'Here selection for third file
                    End Select
                    Set Tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
                End With
                 'Copy and paste to destination
                Source.Sheets(i).Range("A1").Copy Tgt
                 'Close source file without  saving
                Source.Close False
            Next i
             
        End If
         
    End With
     
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.CutCopyMode = False
End Sub

Display More

christianhau · Sep 2nd 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Thank you so much for taking the time

I like your solution regarding the different cases, looks a lot like what I am looking for!
I do although have some questions since I can't make it copy some sample data for me.

I want the destination file to be the same as the file we opened. There is only one file that should be written to and that is the destination file. None of the files change filename.

I was just wondering about this because of this code snippet, does this have anything to do with the pasting of data? As far as I could tell from the code you are pasting to Destination since you specified it to be ThisWorkbook.

Code

'Set Paste target first
          myFileName = Dir(.FoundFiles(i))

Is there any reason why there is nothing being copied from my three sample files to my sample master file? My sample master file is empy and my three slave files are just filled with some random numbers to see if i could do the copying. And yes, I did change the file location Thanks again!!

daniel.c · Sep 2nd 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

First, sorry for the folder change ;-))
As your code works, you copy cell A1 of sheet 1 from the first file, cell A1 of sheet 2 from the second file, etc.
I think you should replace :

Code

Source.Sheets(i).Range("A1").Copy Tgt

with

Code

Source.Sheets(1).Range("A1").Copy Tgt

I tested the modified macro and it seems to work (provided you only want cell A1 to be copied).
As regards to :

Code

myFileName = Dir(.FoundFiles(i))

it only returns the file name, which I use in the Select Case - End Select part.

christianhau · Sep 3rd 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Thanks again! Changed it so that it copies from sheet1 every time. But it is still not working.. Hmm, as far as I can tell it should be working. Just to make sure that I understand it all this code snippet takes whatever is in A1 of sheet1 in the different workbooks and copies it to the Tgt value?

Code

'Copy and paste to destination
          Source.Sheets(1).Range("A1").Copy Tgt

And the target value is defined to be the first row after the last inserted row in the Destination workbook?

Code

Set Tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)

If I understand this correctly then why is nothing being copied from my sourcefiles to my destination file? Have you tested it in a folder with four files, three input files and one master file? And just some random data in the input files?
Thank you so much again!

Dave Hawley · Sep 3rd 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Comment out

Code

On Error Resume Next

and debug.

christianhau · Sep 3rd 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Ahhh... thank you! Finally realized what I was doing wrong!!! Didn't realize that the .FoundFiles also counted the workbook that the macro was written in. So that value ended up being four and so in the last iteration of the loop it copied a blank value from itself and pasted that where I was expecting to see some values. So by changing the for loop to:

Code

For i = 1 To 3 '  Loop through all

it never uses the Destination sheet as an input sheet. Sweet! Thanks for the help guys!

christianhau · Sep 3rd 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Short follow up question: What do you think would be the best solution to make sure that only the new additions to the source workbooks are copied into the destination workbook? Whenever you run the macro i want to cope only the newly added entries. I figured that I need to store a variable indicating the last copied row but where do I store it? In the sheet itself?

Any other suggestions are also very much appreciated!

daniel.c · Sep 3rd 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

If the department ID is stored in the sales row, you don't need to store anything, it will be simple to retrieve it :
1. retrieve the last row in the master file;
2. retrieve the corresponding sale in the source file.
If it's not the case, yes, store it in a cell of the master file.

christianhau · Sep 10th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Its me again I am getting closer to getting my script running smoothly but ran into a bump.

My master workbook is now updated with all the new rows that have been added to the slave workbooks since the last time it was opened. Now the problem is that there is one cell in each row that is not always filled out. This is a total sales sheet and the orders are filled in as they arrive but the actualy payment date will not show up untill the merchandise has been shipped to the customer.

This means that I want the row to be copied to the master sheet from the slave even if the payment date is not set. But I then want to make sure that if the payment date is set it is updated to the masterworkbook.

Does this mean that I have to traverse the entire worksheet (several thousand entries), every time I open the master workbook, and crosscheck every blank payment date with the actual entry in the slave workbook to see if the payment date has been added since the last time? Or is there an easier way?

If there is then please do enlighten me

Thanks again!

daniel.c · Sep 10th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Quote

Does this mean that I have to traverse the entire worksheet (several thousand entries), every time I open the master workbook, and crosscheck every blank payment date with the actual entry in the slave workbook to see if the payment date has been added since the last time? Or is there an easier way?

Yes, but you did not have to read each row. You should filter the data to find the empty payment dates and work on the filtered rows. Alternatively, you may use the "Find" instruction to find blank payment date cells. Feel free to post a sample file, and i'll return the corresponding code.

christianhau · Sep 10th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Great! Here is a file with two columns representing a selection of the data that I am working on. The ID column is a unique indentifier for the row. Instead of attaching two workbooks I made two sheets instead since I presume that the method you use would work equally good on two sheets as it would on two workbooks. But in my case it is two different workbooks both with the information on the first worksheet. If you need anything else please let me know, thanks in advance!

daniel.c · Sep 10th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

I moved the "updated slave workbook" sheet to a new workbook which i named "SlaveBook.xls". Paste the following code in the "ThisWorkbook" module :

Code

Private Sub Workbook_Open()
    Dim Plage As Range, c As Range
    Const myPath = "e:\donnees\daniel\mpfe\"
    With Sheets("Not Updated Master Workbook")
        Workbooks.Open myPath & "SlaveBook.xls"
        .Range("A1").AutoFilter
        .Range(.[A1], .[A65536].End(xlUp)).AutoFilter Field:=2, Criteria1:="="
        Set Plage = .Range("_filterdatabase").Offset(1)
        Set Plage = Plage.Resize(Plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
        For Each c In Plage
            c.Offset(, 1) = Application.VLookup(c, [A:B], 2, 0)
        Next c
        Range("A1").AutoFilter
    End With
    Workbooks("SlaveBook.xls").Close False
End Sub

Display More

As you have 3 slave files, you'll have to filter the department column too.

christianhau · Sep 10th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Thanks! This code is too advanced for me but working on understanding it now. Is there a way to not just display the rows that were changed after the macro is done? I want to update the master workbook but leave how it is displayed intact.
Thank you so much again!

daniel.c · Sep 10th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Try :

Code

Private Sub Workbook_Open()
    Dim Plage As Range, c As Range
    Const myPath = "e:\donnees\daniel\mpfe\"
    With Sheets("Not Updated Master Workbook")
        Workbooks.Open myPath & "SlaveBook.xls"
        .Range("A1").AutoFilter
        Range(.[A1], .[A65536].End(xlUp)).AutoFilter Field:=2, Criteria1:="="
        Set Plage = .Range("_filterdatabase").Offset(1)
        Set Plage = Plage.Resize(Plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
        For Each c In Plage
            c.Offset(, 1) = Application.VLookup(c, [A:B], 2, 0)
        Next c
        .Range("A1").AutoFilter
    End With
    Workbooks("SlaveBook.xls").Close False
End Sub

Display More

A missing dot that matters !

christianhau · Sep 11th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

didnt realize the genius of VLookup before now

But struggling a bit here, first time I ran the macro it went perfect! but when i removed some random values from the Payment Date field in the Examplefile.xls and ran it again I just end up with values like 39126. And I cannot figure out why..

Another thing. In my actual sheet the Payment Date is located in the AA column and the ID is located in the E column. In between there is plenty of data that should not be messed with is the following then a working solution?

Code

Private Sub Workbook_Open() 
    Dim Plage As Range, c As Range 
    Const myPath = "e:\donnees\daniel\mpfe\" 
    With Sheets("Not Updated Master Workbook") 
        Workbooks.Open myPath & "SlaveBook.xls" 
        .Range("E1").AutoFilter 
        Range(.[E1], .[E65536].End(xlUp)).AutoFilter Field:=27, Criteria1:="=" 
        Set Plage = .Range("_filterdatabase").Offset(1) 
        Set Plage = Plage.Resize(Plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) 
        For Each c In Plage 
            c.Offset(, 1) = Application.VLookup(c, [D:AA], 27, 0) 
        Next c 
        .Range("A1").AutoFilter 
    End With 
    Workbooks("SlaveBook.xls").Close False 
End Sub

Display More

And another thing. Since I have to check in possibly three different sheets for the corresponding ID and Payment Date, how do I do this?

daniel.c · Sep 11th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

1. 39126 means "13/02/07"; It's the way Excel computes time (day 1 is "01/01/1900" (sometimes "01/01/1904). Enter that date in a celland apply standard number format and you'll get 39126. I added a line of code to correct it :

Code

c.Offset(, 26).NumberFormat = "dd/mm/yy"

The macro I post below should certainly be modified. I used column C to add the department. Moreover, if column A is not used, the filters instructions have to be modified. I created three department files :
- dept1.xls
- dept2.xls
- dept3.xls
I am just thinking that I did not change their structures (IDs in column A and dates in B).
You better send a new sample file. Anyway, here is my code (see also attached file).

Code

Private Sub Workbook_Open()
    Dim Plage As Range, c As Range, Fich As String, Crit As String
    Const myPath = "e:\donnees\daniel\mpfe\"
    With Sheets("Not Updated Master Workbook")
        Fich = Dir(myPath & "*.xls")
        Do While Fich <> ""
            Crit = ""
            Select Case Fich
                Case "dept1.xls"
                    Crit = "dept1"
                    Workbooks.Open myPath & Fich
                Case "dept2.xls"
                    Crit = "dept2"
                    Workbooks.Open myPath & Fich
                Case "dept3.xls"
                    Crit = "dept3"
                    Workbooks.Open myPath & Fich
            End Select
            If Crit <> "" Then
                .Range("A1:AA1").AutoFilter
                Range(.[A1], .[A65536].End(xlUp)).AutoFilter Field:=27, Criteria1:="="
                Range(.[A1], .[A65536].End(xlUp)).AutoFilter Field:=3, Criteria1:=Crit
                Set Plage = .Range("_filterdatabase").Offset(1)
                On Error GoTo Suite
                Set Plage = Plage.Resize(Plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
                For Each c In Plage
                    c.Offset(, 26) = Application.VLookup(c.Offset(, 4), [A:B], 2, 0)
                    c.Offset(, 26).NumberFormat = "dd/mm/yy"
                Next c
Suite:
                On Error GoTo 0
                .Range("A1").AutoFilter
                Workbooks(Fich).Close False
            End If
            Fich = Dir
        Loop
    End With
End Sub

Display More

Of course, I'll add comments when the macro will be finalized.

christianhau · Sep 12th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

Again.. Thank you so much for the help!! You got skills man But since I am struggling a bit with changing your code to my suit i do humbly ask for some further assistance.

So! To make it easier for you and to exclude any guesswork, here is a to the point examplefile of how my files look. I wasn't sure if you wanted me to put data in the other columns but since there is a chance they could be blank anyway I left them untouched. The columns that are important are now as before the ID and the Payment date. But I have now put them where they are in the original document. I also included a a type column that signifies if the row came from Slavebook or Slavebook2.

Slavebook is signified by an M and Slavebook2 is signified with an I. I hope this makes it a bit easier!

I decided to leave out workbook number three since it might turn out to be excluded anyhow, so to save worktime here is my examplefiles!

daniel.c · Sep 12th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

The important thing for me to know is what is the first column of data.

christianhau · Sep 12th 2008

Re: Automatic Updating Of Master Worbook From Slave Workbooks

If I understand you correctly here is the overview of the first column of data:

Master Workbook:
ID: Column "E"
Type: Column "N"
Payment Date: Column "AA"

Slavebook:
ID: Column "E"
Payment Date: Column "AA"

Slavebook2:
ID: Column "A"
Payment Date: Column "J"

The data that is lacking is the payment date in the master workbook and it is to be gathered from the payment date in slavebook 1 and 2 at different locations in the workbook depending if it's slavebook 1 or 2. If this does not answer your question then please clearify.

Automatic Updating Of Master Worbook From Slave Workbooks

Participate now!

Share