CODEWITHSUNDEEP
vbaexcel
user1026987
user1026987

Reputation: 73

Excel merging two files based on a variable

I want to write a program that will match variable names in two separate workbooks, than copy all information from variable until page break from both workbooks into a new work. Each workbook has multiple pagebreaks not sheets. For example:

Workbook A (Variable = X)    
Persons          Name  
X                    Bill  

Work Book B  
Persons          Nickname  
X                   Billy  

New Workbook  
Page 1  
Persons          Name  
X                   Bill  

Page 2   
Persons          Nickname  
X                  Billy

I was using the code at this site to merge the two selected workbooks, but I cannot figure out how to match by name and than copy to page break. Can anyone have suggestions or can help direct me? Thank you!

Code: This is not correct but I was trying to use a Vlookup to find at least one of the values in the worksheet 

MergeSelectedWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim SelectedFiles() As Variant
    Dim NRow As Long
    Dim FileName As String
    Dim NFile As Long
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    Dim VariableX As Variant

    ' Create a new workbook and set a variable to the first sheet.
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\Documents\Test"

    ' Set the current directory to the the folder path.
    ChDrive FolderPath
    ChDir FolderPath
    SelectedFiles = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)

    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1

    ' Loop through the list of returned file names
    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        ' Set FileName to be the current workbook file name to open.
        FileName = SelectedFiles(NFile)

        ' Open the current workbook.
        Set WorkBk = Workbooks.Open(FileName)

        ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NRow).Value = FileName

        ' Set the source range to be A9 through C9.
        ' Modify this range for your workbooks. It can span multiple rows.
        Set SourceRange = VBAVlookup(2, WorkBk.Worksheets(1).Range("A1:A25"), 2, False)

        ' Set the destination range to start at column B and be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)

        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value

        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count

        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
    Next NFile

    ' Call AutoFit on the destination sheet so that all data is readable.
    SummarySheet.Columns.AutoFit

    End Sub

Function VBAVlookup(ByVal search As Variant, _
                    cell_range As Range, _
                    offset As Long, _
                    Optional opt As Boolean = False)

Dim result As Variant
result = WorksheetFunction.VLookup(search, cell_range, offset, opt)
'do some cool things to result

VBAVlookup = result

End Function

Upvotes: 0

Views: 1242

Answers (1)

Aaron Thomas
Aaron Thomas

Reputation: 5281

Since you need to combine information for each variable, I'm guessing a variable exists only once in at least one of the workbooks? In other words, you won't need to combine information within a workbook, before combining between workbooks, will you?

If this is the case, and one workbook lists each variable (and possibly additional info for that variable) only once, you can use it as a base workbook as you search between workbooks.

Here are some general pointers to get things going:

Using Excel-VBA code, open up the workbooks:

'define the input filepath
Dim inputfilepath As String
'define workbook name where variable info is located
Dim workbookTitle As String
workbookTitle = "Workbook1"

'this creates a filepath, filename, and file extension.
'if not ".xls", change to make appropriate
inputfilepath = ActiveWorkbook.Path & "\" & workbookTitle & ".xls"

'open comparing workbook. see .OpenText method documentation for
'more details and options
Workbooks.OpenText Filename:=inputfilepath, Origin:= _
  xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
  xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
  Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
  Array(2, 1)), TrailingMinusNumbers:=True

How do you find variables? You mentioned using VLOOKUP, which is fine. But in this case since you are using Excel-VBA, I'd like to suggest using a Range.Value property. You can use a for loop to go through each variable in the base workbook.

How do you find if that variable exists in a different workbook? Then search for that variable using another for loop, nested inside the first for loop:

'find the variable.
'here, it's assumed variable on first line
Dim myVar As String

'1st for loop
'w needs to be assigned to the appropriate workbook,
'and s needs to be assigned to the appropriate worksheet
For varRow = 2 To Workbooks(w).Worksheets(s).UsedRange.Rows.Count

    'assign the variable name to "myVar"
    myVar = Workbooks(w).Worksheets(s).Range(Cells(varRow, 0)).Value

    'nested for loop
    'similar to parent for loop, w2 needs to be assigned to the
    'appropriate workbook for finding additional variable info,
    'and s2 needs to be assigned to the appropriate worksheet
    For varRow2 = 2 To Workbooks(w2).Worksheets(s2).UsedRange.Rows.Count

        'assign the variable under inspection in the comparison
        'worksheet to "compareVar."
        compareVar = Workbooks(w2).Worksheets(s2).Range(Cells(varRow2, 0)).Value

        'perform StrComp to compare myVar and compareVar, and see
        'if a match.
        If StrComp(CStr(myVar), CStr(campareVar)) <> 0 Then

            'code for merging values here, since this
            'will execute if the variables match

        End If

    Next

Next

I use StrComp function to see if a variable name exists in another workbook. I also use .UsedRange.Rows.Count to define the limits of the for loop, since this is one way to define the working range in a worksheet. The for loops just go through row-by-row, looking at info in that line. This framework can be adapted for how information is set up in the workbooks.

Now this goes through row-by-row, but how do you go pagebreak-to-pagebreak? Thanks to this SO answer, you can iterate through page breaks. I've changed the first for loop from above so it takes advantage of searching by pagebreak. Compare this to the code above, to see how it's changed. This can also be adapted to fit the details:

'1st for loop
'w needs to be assigned to the appropriate workbook,
'and s needs to be assigned to the appropriate worksheet
For Each pgBreak In Workbooks(w).Worksheets(s).HPageBreaks

    'assuming the variable is immediately after a pagebreak,
    'assign the variable name to "myVar"
    myVar = Workbooks(w).Worksheets(s) _
    .Range(Cells(pgBreak.Location.Row + 1, 0)).Value

Hope all this helps and gets things going for you.

Upvotes: 1

Related Questions