Merging two files

[narendra1302]

Hi,

I am having two Excel files.
Now I want to merge these two files. Also there are Hyperlinks in both the files. While merging can we update all the hyperlinks.
All columns are to be merged.


Thanks
Narendra

 

[narendra1302]

Any update please

 

[narendra1302]

Hi

 

[narendra1302]

Hi All,

Is there any solution

Thanks
Narendra

 

[Ruddles]

Almost anything is possible. What do you mean by "merge two files"?

Do you want one large workbook containing all the worksheets from the two source workbooks?

Or do you want the individual worksheets merged in some way, maybe at the cell level? What if they both have values in the same cell? Which one do you want to keep? Or do you want them added together? Or something else?

What does "merge columns" mean? What if you have two workbooks like this:-

<TABLE style="WIDTH: 238pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=314 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=82 height=17>

Workbook 1​

</TD><TD class=xl71 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 26pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=34>

​

</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=82>

Workbook 2​

</TD><TD class=xl71 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 26pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=34>

​

</TD><TD class=xl71 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=82>

Workbook 3​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" height=17>

G​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">

G​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">

G​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

1​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

?​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

2​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

?​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

3​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

7​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

?​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

8​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

?​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

4​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

9​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

?​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

5​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

?​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

6​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

?​

</TD></TR></TBODY></TABLE>​

What do you want the third one to contain?

"Merge" is a very vague word. If you tell us exactly what you want to achieve, preferably with some solid examples, then we may be able to help you.

 

[narendra1302]

Dear Ruddles,

Thanks for showing intrest.

The two files are different. However I want 3rd files, where in it will ask which column to paste where from which file.

Actually we have complaint database - in wich we have complaint numbers & we are traccking it. We have old database & new database. New database contains some additional columns & extra features (Auto Complaint generator, site name, Regions automatically). and these features are not there in old. And now we want one database which will have both OLD as well as NEW.

Thanks
Narendra

 

[narendra1302]

Hi Ruddles,

Any update please

Thanks
Narendra

 

[Ruddles]

No, sorry, I'm actually working at the moment and I only visit the board occasionally during the day. This is not a trivial project you're undertaking - it's certainly much too complex for me to dash off during a few spare moments.

You will either have to drive it yourself by learning the various techniques involved - like opening multiple workbooks simultaneously, transferring data between VBA and the worksheets and resolving the various conditions that arise whilst you're merging them, or you'll have to find someone able to devote the necessary time to writing the code for you.

I'm actually out tonight and tomorrow night but I may have some time at the weekend to look at this and maybe get you started. If someone else offers to get the ball rolling in the meantime, I would urge you to accept their offer.

However, as I said in my previous post, you'll need to tell us at some point exactly what you want to achieve, preferably with some solid examples. Perhaps you could start putting that information together so they're available to whoever needs them.

 

[Ruddles]

Without seeing the two source workbooks it's impossible to write any code to merge them. From your brief description, as this seems to be a one-off task which you'll never need to do again, I suspect the quickest and easiest way would be to merge them manually.

However if you feel you want to go down the VBA route, here is some code which will prompt the user for two source workbook names, open them so that they're available for the programmer to reference all the data in the (via the object variables wBook(1) and wBook(2)), open a new workbook (wBook(3)), then prompt for a save location and filename for the new workbook.

I've inserted a couple of 'placeholder' comments which indicate where the code should be inserted to perform the actual merging of the source workbooks and the creation of the new workbook. Place this code in a new general code module and test it to make sure it works okay on your system.

[FONT=Fixedsys]Option Explicit
Option Compare Text
Option Base 1[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Fixedsys]Public Sub MergeWorkbooks()
  
  Dim strWorkbook(3) As String
  Dim wBook(3) As Workbook
  Dim wSheet(3) As Worksheet
  Dim iCtr As Integer[/FONT]
[FONT=Fixedsys]
  Dim iDot As Integer
  Dim strFileFormat As String
  Dim intFileFormat As Integer
  
  For iCtr = 1 To 2
    strWorkbook(iCtr) = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xl*), *.xl*")
    If strWorkbook(iCtr) = "False" Then Exit Sub
    Set wBook(iCtr) = Workbooks.Open(strWorkbook(iCtr))
    Set wSheet(iCtr) = wBook(iCtr).Sheets(1)
  Next iCtr
  
  Set wBook(3) = Workbooks.Add
  Set wSheet(3) = wBook(3).Sheets(1)
  
  Application.ScreenUpdating = False
  
[COLOR=green]  ' the code to merge your two source workbooks goes here
  
  ' end of code to merge source workbooks[/COLOR]
  
  Application.ScreenUpdating = True
  
  strWorkbook(3) = Application.GetSaveAsFilename(FileFilter:="Excel Workbook (*.xlsx), *.xlsx, " _
                & "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm, Excel Binary Workbook (*.xlsb), *.xlsb," _
                & "Excel 97-2003 Workbook (*.xls),*.xls", FilterIndex:=1)
  If strWorkbook(3) = "False" Then
    MsgBox "Merged workbook closed without saving - changes lost." & Space(10) & vbCrLf & vbCrLf, _
           vbOKOnly + vbExclamation
  Else
    iDot = InStrRev(strWorkbook(3), ".")
    strFileFormat = Mid(strWorkbook(3), iDot + 1)
    Select Case strFileFormat
      Case "xlsx": intFileFormat = xlOpenXMLWorkbook
      Case "xlsm": intFileFormat = xlOpenXMLWorkbookMacroEnabled
      Case "xlsb": intFileFormat = xlExcel12
      Case "xls": intFileFormat = xlExcel8
    End Select
    wBook(3).SaveAs Filename:=strWorkbook(3), FileFormat:=intFileFormat
  End If
  
  For iCtr = 1 To 3
    strWorkbook(iCtr) = wBook(iCtr).Name
    wBook(iCtr).Close savechanges:=False
  Next iCtr
    
  MsgBox "Workbooks " & strWorkbook(1) & " and " & strWorkbook(2) & " merged." _
       & Space(10) & vbCrLf & vbCrLf _
       & "Workbook " & strWorkbook(3) & " saved.", vbOKOnly + vbExclamation
       
End Sub[/FONT]

If you decide that you don't want to merge the source workbooks manually, your next step is to decide exactly how you want to merge them and work towards getting the code written to do that. But before you do, please consider whether merging them manually might be quicker and easier. VBA isn't the answer to every problem!