vbscript to merge excel files

November 21, 2011 at 22:44:23
Specs: Windows XP
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

See More: vbscript to merge excel files

Report •


#1
January 12, 2012 at 06:30:34
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 :-)


Report •
Related Solutions


Ask Question