You are here:

Excel/VBA: opening/comparing two workbooks


QUESTION: In summary, the two things that have me stumped are 1) how to use VBA to open a workbook in the current folder based on a static string in the name of the file and 2) removing essentially (though not exact) duplicate rows between two workbooks. Here's what I mean.

I will have two workbooks in the same folder named according to the form "12345 Pre-work.xlsx" and "12345 Post-work.xlsx" ("12345" is an example, but "Pre-work" and "Post-work" will be static). My idea is that the user would open the "Pre-work" workbook and run the macro, which would look within the same folder as the current workbook and open the workbook with "Post-work" at the end of its file name.

The next thing is to remove rows between the two workbooks that are essentially duplicates based on matching data in columns A through J, M through O, X, and Y on the sheet named "Alpha" in both of the files. I can figure a way to do this with spaghetti code, but I imagine there's a much quicker and more efficient way to handle this with VBA.

Any help you can give would be greatly appreciated. Thank you!

ANSWER: Steve,

So I named the active workbook 12345 Pre-work.xlsx

I then put in these instructions in the immediate window to demonstrate the approach

SnamePre = Activeworkbook.FullName
? SnamePre
C:\Data\12345 Pre-work.xlsx
SnamePost = Replace(SnamePre, "Pre", "Post")
? sNamePost
C:\Data\12345 Post-work.xlsx

so the operative commands are:
SnamePre = Activeworkbook.FullName
SnamePost = Replace(SnamePre, "Pre", "Post")

so now sNamePost contains the same name as the activeworkbook, but with "Pre" replaced by "Post"

so you could open the other workbook with SnamePost

the name also is fully qualified with the path of the activeworkbook.

as far as your essentially duplicate data, you would have to loop through each data row in one workbook and then search through each data row in the other workbook and compare the field values (in a loop) in column A:J, M:O, X:Y and determine if they match.  
If so you could then do What? (you haven't really fully defined the What):

delete both rows, delete the row in which workbook?  you didn't say.

You also didn't say if there could be multiple rows in the second workbook that could be essential duplicates of the subject row in the first workbook.

Perhaps you see this approach as spaghetti code, but to me it is the logical way to perform the function you describe.  

So I will stop there and you can clarify if you need more specific help.

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Your first solution makes complete sense. Thank you!

I get the looping, but to answer your questions, what I would want would be to delete the matching row from BOTH spreadsheets. If row X in "Pre" matches multiple rows in "Post" (or vice versa) I would only want one of the rows in "Post" deleted. How could this work?


Here is some sample code that worked for me as I understand your description:

Test it on copies of your workbooks until you are satisfied it does what you want.

Sub abc()
Dim bkpre As Workbook
Dim bkpost As Workbook

Dim shPre As Worksheet, shPost As Worksheet
Dim rPre As Range, rPost As Range
Dim cellPre As Range, cellPost As Range
Dim delPre As Range, cell As Range
Dim rwPost As Range, bMatch As Boolean
Dim SnamePre As String
Dim SnamePost As String

' assume 12345 Pre-Work.xlsx is already open
Set bkpre = Workbooks("12345 Pre-Work.xlsx")
SnamePre = bkpre.FullName
SnamePost = Replace(SnamePre, "Pre", "Post")
Set bkpost = Workbooks.Open(SnamePost)

Set shPre = bkpre.Worksheets("Alpha")
Set shPost = bkpost.Worksheets("Alpha")
Set rPre = shPre.Range("A2", shPre.Cells(shPre.Rows.Count, "A").End(xlUp))
Set rPost = shPost.Range("A2", shPost.Cells(shPost.Rows.Count, "A").End(xlUp))
For Each cellPre In rPre
 Set rPost = shPost.Range("A2", shPost.Cells(shPost.Rows.Count, "A").End(xlUp))
 For Each cellPost In rPost
   Set rwPost = cellPost.Range("A1:J1,M1:O1,X1:Z1")
   bMatch = True
   For Each cell In rwPost
     If shPre.Cells(cellPre.Row, cell.Column).Value <> cell.Value Then
         bMatch = False
         Exit For
     End If
   If bMatch Then
     If delPre Is Nothing Then
         Set delPre = cellPre
         Set delPre = Union(delPre, cellPre)
     End If
     Exit For
   End If
If Not delPre Is Nothing Then
End If
End Sub

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.