Insert a ROW of data to multiple - differently named, and located, but same structured workbooks

[ita]

Insert a ROW of data to multiple - differently named, and located, but same structured workbooks
Excel 2007
Windows 7 OS
Environment:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Excel 2007 in a SharePoint environment with full rights and access to all the folders, subfolders, and files.
Workbook called “Masterproject.xlsx” is in a folder for master files used as a template (not really a “template” format just used as an empty example to copy) and is NOT currently linked to any other workbooks, has already been copied (multiple times over 20 to 100) to another folder called “projects” and under several subfolders of various names call “(whatever city name)” (depending on the location of the project) and under various file names called “cityproject.xlsx” (the word city in the file name is really just the various city names) (i.e. chicagoproject.xlsx, or londonproject.xlsx, or mumbaiproject.xlsx, etc.)
All the files may have different names and locations, BUT the internal structure of all the files are the identical. They have the same sheet names, cell locations, etc. However, there is data in some cells that are different from each other and should be different and should not be changed or overwritten. (just moved around after inserting the new row or rows)
Request:<o></o>
After the initial setup, and use of all the copied files (that now has important different data in the cells) I want to add a needed “new” row of information to the structure of all the files.
A cool way to do this would be to only have to add the row to the “Master” and it would make that same exact row data addition in all existing files, somehow updating all the other files. (I am willing to touch each of the existing files once if I need to do so, or whatever)
I am at a loss as to how to do this accurately or effectively without manually doing this each time to each file individually. And the answer may be “Not possible”.
Any assistance is greatly appreciated. Seems like a good puzzle. Any brave takers?

 

[b.downey]

Will the new "Row" of data be inserted at the same "Row No" number in each of the workbboks (Master Project and others)?

Is it a single Row of data or multiple rows?

Are formulas involved with the new row?

Can you provide an example of both the before and after?

 

[ita]

Thank you for considering the challenge of this issue!

Will the new "Row" of data be inserted at the same "Row No" number in each of the workbboks (Master Project and others)?
Yes.

Is it a single Row of data or multiple rows?
It could be single or more depending on if I need to add more items (however, I'm willing to do one at a time if multiple presents potential problems or makes this too complicated.)

Are formulas involved with the new row?
No. Just simple text cells.

Can you provide an example of both the before and after?

Before:

Column A Column B Column C
1 Feed Dog Susan 9:00AM
2 Take Dog Out Jeff 11:00AM
3 Brush Dog Susan 4:00PM
4 Feed Dog Tom 5:00PM


After:

Column A Column B Column C
1 Feed Dog Susan 9:00AM
2 Take Dog Out Jeff 11:00AM
3 Brush Dog Susan 4:00PM
4 Play with Dog Cathy 4:30PM
5 Feed Dog Tom 5:00PM


Thank you in advance. Hope you can find a solution.

 

[b.downey]

Ok. I would like to try this in stages.

What I am invisioning is to use a master sheet that has been updated with all the right information as a base to update the rows in the other project workbooks.

Given the two data sets you provided, I put to the following code together. It assumes the "Master" sheet contain the "Desired" information and the "Target" sheet has a subset of the "Master" sheet. The code will update the "Target" based on the differences from the main. It uses column "A" in both sheets to determine if there is a difference

Sub ModifySubWb()
    Dim rngMst As Range
    Dim rngTrg As Range
    
    Dim MstRow As Long
    Dim TrgRow As Long
    
    Set rngMst = ThisWorkbook.Sheets("Master").UsedRange
    Set rngTrg = ThisWorkbook.Sheets("Target").UsedRange
    
    TrgRow = 1
    For MstRow = 1 To rngMst.Rows.Count
        If rngMst.Cells(MstRow, 1) <> rngTrg.Cells(TrgRow, 1) Then
            rngMst.Rows(MstRow).Copy
            rngTrg.Rows(TrgRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End If
        TrgRow = TrgRow + 1
    Next MstRow
End Sub

If this code works as expected, I can then make it work between two different worksbooks. and then make it iterate thru the File Structure to find all the "Project" work books that need to be updated.

How many levels will their be in the directoy path?

 

[ita]

Wow, this is way cool!

Well it works, with one glitch. when I run the first row is fine, then the second is blank then the third row on is all good. So it looks like this.

Master:

ColumnA
1 Feed Dog AM
2 Walk Dog
3 Brush Dog
4 Feed Dog PM

Target (1st run):

ColumnA
1 Feed Dog AM
2
3 Walk Dog
4 Brush Dog
5 Feed Dog PM

This still cause a difference between the two so a 2nd running of the code will result in the following:

Target (2nd run):

ColumnA
1 Feed Dog AM
2 Walk Dog
3 Brush Dog
4 Feed Dog PM
5
6 Walk Dog
7 Brush Dog
8 Feed Dog PM

Then all other running of the code without changes to the mater doesn't change the above (2nd run) example. Obviously the initial 1st run with the skipped line cause a difference still and therefore the program appropriately tries to match again.

But this is way cool so far. (I hope I don't add to the problem, or discourage you, but I better tell you now that my user just told me "what happens if I need to delete a row?"... ) Oh great! Just when I see the light and direction you are going with this, you seem to be right on tack with the original specification I layed out! (so sorry, I hope this doesn't make you give up on me! )

 

[b.downey]

I must be missing something. I have a target sheet with the following entries starting in cell A1.

Feed Dog AM
Walk Dog
Brush Dog
Feed Dog PM

The "Modified" Master sheet has the following entries starting in cell A1 as well

Feed Dog AM
Walk Dog
Brush Dog
Play with Dog
Feed Dog PM

After running the Macro, The Master and Target are identical.

Is you set up different?

Bill

 

[b.downey]

I sent of the email a little prematurly.

Try the following:

Option Explicit
Sub ModifySubWb()
    Dim wsMst As Worksheet
    Dim wsTrg As Worksheet
    
    Dim MstRow As Long
    Dim TrgRow As Long
    
    Set wsMst = ThisWorkbook.Sheets("Master")
    Set wsTrg = ThisWorkbook.Sheets("Target")
    
    TrgRow = 1
    For MstRow = 1 To wsMst.Range("A" & wsMst.Rows.Count).End(xlUp).Row
        If wsMst.Cells(MstRow, 1) <> wsTrg.Cells(TrgRow, 1) Then
            wsMst.Rows(MstRow).Copy
            wsTrg.Rows(TrgRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End If
        TrgRow = TrgRow + 1
    Next MstRow
End Sub

 

[ita]

Excellent!!! This stage works GREAT. Even adds mulitiple lines, and blank lines inbetween. Very nice.

So how would you like to proceed. Do you want to go to the next stage as you indicated, taking this steps at a time? (Or as I indicated, the user said something about deleting a row... if I try that with this code, it duplicates and pushes down several rows of stuff.)

Either way (if we can't delete a row) that is not the end of the world, so far the ability to add lines is far more than we expected.

So I'm ready for your next step OR the abiility to add AND "delete" rows.

By The Way... I'm new to this site. Is there some sort of rewards or points or compensation for you and this great help? Or is this like a wicked puzzle site for you all, better than sudoku!?

 

[b.downey]

Ok... This code needs to be put into a VB Module within the Master Workbook. It also assumes the worksheet that contains the master is called "Master"

When you execute the Macro, "Process", a standard Dialog box will appear askinf for the "Folder" that containse the "*.project.xlsx" files. The code will move thru each of the XL files in the direcory (folder) and make the needed changes. Prior to making any changes, it will save a backup copy to a backup directory.

Option Explicit
Sub Process()
    Dim wsResults As Worksheet
    Dim WbTrg As Workbook
    Dim ws As Worksheet
    
    Dim fd As FileDialog
    Dim FileName As String
    
    Dim BackupFilePath As String
    
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        .AllowMultiSelect = False
        .Show
    End With
    
    If fd.SelectedItems.Count <= 0 Then
        Exit Sub
    End If
    Set wsResults = GetResultsWs(ThisWorkbook)
    
    BackupFilePath = fd.SelectedItems(1) & "\BackUp"
    If Len(Dir(BackupFilePath, vbDirectory)) = 0 Then
        MkDir BackupFilePath
    End If
    
    FileName = Dir(fd.SelectedItems(1) & "\*project.xlsx")
    Do While Len(FileName) > 0
        
        'Open the Target Workbook as Read only
        Workbooks.Open FileName:=FileName, ReadOnly:=False, UpdateLinks:=False
        Set WbTrg = ActiveWorkbook
        WbTrg.SaveCopyAs BackupFilePath & "\" & WbTrg.Name
        
        Call WriteResults(wsResults, "Processing File: " & FileName)
        Call ModifyTrgWs(WbTrg.Worksheets(1))
        
        'Close the Target WorkBook
        'WbTrg.Save
        WbTrg.Close SaveChanges:=True
        
        'Get next file in the directory
        FileName = Dir()
    Loop
    
    MsgBox "Complete", vbInformation
End Sub
Function WriteResults(wsResults As Worksheet, s As String)
    Dim RowNo As Long
    RowNo = wsResults.Range("A" & wsResults.Rows.Count).End(xlUp).Row + 1
    
    wsResults.Cells(RowNo, "A") = Now()
    wsResults.Cells(RowNo, "B") = s
End Function
Function GetResultsWs(wb As Workbook) As Worksheet
    Const SheetName As String = "Results"
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        If ws.Name = SheetName Then
            Set GetResultsWs = ws
            Exit Function
        End If
    Next
    
    wb.Sheets.Add After:=Worksheets(Worksheets.Count)
    wb.ActiveSheet.Name = SheetName
End Function
Function ModifyTrgWs(wsTrg As Worksheet)
    Dim wsMst As Worksheet
    
    Dim MstRow As Long
    Dim TrgRow As Long
    
    Set wsMst = ThisWorkbook.Sheets(1)
    
    TrgRow = 1
    For MstRow = 1 To wsMst.Range("A" & wsMst.Rows.Count).End(xlUp).Row
        If wsMst.Cells(MstRow, 1) <> wsTrg.Cells(TrgRow, 1) Then
            wsMst.Rows(MstRow).Copy
            wsTrg.Rows(TrgRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End If
        TrgRow = TrgRow + 1
    Next MstRow
End Function

I am planning on having a second macro for the delete row requirement

 

[ita]

Setup
I have a test directory called: C:\DATA\DEB\
The 'Master.xlsx' is in that folder.

There is a subfolder called: C:\DATA\DEB\PROJECTS
I've placed 4 test file in this folder called:
berlinproject.xlsx
chicagoproject.xlsx
londonproject.xlsx
romeproject.xlsx

Ran into a problem...

Run-time error '1004'
'berlinproject.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct.

it stops at: (RED LINE)

'Open the Target Workbook as Read only
Workbooks.Open FileName:=FileName, ReadOnly:=False, UpdateLinks:=False

I did some homework and read about 1004 errors and maybe the full path isn't set? I tried to look at the "locals" and did not see the 'Filename' variable has a full path, it doesn't it just has the file name (i.e. berlinproject.xlsx)

I did see the backup folder path variable, and we did get a "backup" folder created in the "projects" folder. But I am very novice at this VBA stuff, so I may not be seeing it or understanding if it is even necessary for this code functions. I've done some programing so I can follow your code okay... I just don't have the experience and knowledge of all the codes and functions in VBA to begin to write my own.

(Side note: my Dad worked for IBM and brought home one of the very first PC's, and as a kid I had one of the first IBM PC's serial number under 100, and I wrote BASIC programs and then ADVANCE BASIC when it came out, then in college some programing classes, and a few jobs I did some Database programing, but now I've been a consultant for some time now and only infrequently get to play in code like this.)

I was thinking... Would we need have the line include something like Workbooks.open 'path' + 'FileName'...etc. there is a function called 'thisworkbook.path' but I think that returns the location of were my workbook that is calling it is, and that isn't were the other workbooks are.

In your example the other workbooks are in a "single" folder we specify when we run the VB code "process". (In my orignal description, remember that there are several folders with copies of this workbook, not just one folder with serveral workbooks. Therefore, if that is an issue, I am willing to have an extra "sheet" in the Master workbook that can have a list of the folders to search?!? Otherwise if you are willing and it's just as easy, we could specify a 'root' folder and have you search "all" subfolders for the "*project.xlsx" files?!? I'll let you determine which would be best for us.

Again, I can't thank you enough for your efforts here. And I am really enjoying this because I think programing is so cool and it's been so long since I've done/seen this kind of stuff and I thought this would be really impossible.

Oh one more thing as I indicated we are using Excel 2007... and it is asking me to save my file in a XLMX format sometimes? so it will like these macros? Is this and issue and is it really necessary? Will this affect your code here? I am sure I have my macro security turned to "full open and allow".