computing
  • 0

Vbscript To Merge Excel Files

  • 0

Hi ,
I need to write vb script for merging two excel file
I have an excel file a.xls having data
Id Name
1 a
2 b
3 c
and second excel file b.xls having data
Id Name
4 d
5 e
the output in a.xls should be
1 a
2 b
3 c
4 d
5 e

Share

1 Answer

  1. Location xls to single xls:

    Option Explicit

    Sub CombineFiles()

    Dim Path As String
    Dim FileName As String
    Dim Wkb As Workbook
    Dim WS As Worksheet

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Path = “D:\Documents and Settings\305019854\Desktop\test” ‘Change as needed
    FileName = Dir(Path & “\*.xls”, vbNormal)
    Do Until FileName = “”
    Set Wkb = Workbooks.Open(FileName:=Path & “” & FileName)
    For Each WS In Wkb.Worksheets
    WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Next WS
    Wkb.Close False
    FileName = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub

    this is for combine

    Sub Combine1()
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ‘ add a sheet in first place
    Sheets(1).Name = “Combined”

    ‘ copy headings
    Sheets(2).Activate
    Range(“A1”).EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range(“A1”)

    ‘ work through sheets
    For J = 2 To Sheets.Count ‘ from sheet 2 to last sheet
    Sheets(J).Activate ‘ make the sheet active
    Range(“A1”).Select
    Selection.CurrentRegion.Select ‘ select all cells in this sheets

    ‘ select all lines except title

    ‘ copy cells selected in the new sheet on last line
    Selection.Copy Destination:=Sheets(1).Range(“A65536”).End(xlUp)(2)
    Next
    End Sub

    Enjoy 🙂

    • 0