How to combine 4 Exel files in to 1 Exel file with 4 sheets?
How to combine 4 Exel files in to 1 Exel file with 4 sheets?
(OP)
I need some help with combining 4 different Exel Spreadsheets in to one Exel file with 4 sheets (one from each individual file). I want to do it using a Visual Basic script. My intend is to automate the process of combining 4 different Exel spreadsheets in to one Exel file with 4 sheets. Any ideas or code sample? Thanks in advance. - Kris





RE: How to combine 4 Exel files in to 1 Exel file with 4 sheets?
CODE
Pth = "C:\Documents and Settings\Mala Singh\Desktop\"
Dim SourceWkbk As Workbook, CombinedWkbk As Workbook
FilesTocombine = Array("Wkbk1", "Wkbk2", "Wkbk3", "Wkbk4")
Set CombinedWkbk = Workbooks.Add
For i = LBound(FilesTocombine) To UBound(FilesTocombine)
FileNm = FilesTocombine(i)
Set SourceWkbk = Workbooks.Open(Pth & FileNm)
SourceWkbk.Worksheets(1).Copy after:=CombinedWkbk.Sheets(CombinedWkbk.Sheets.Count)
SourceWkbk.Close
Next i
CombinedWkbk.SaveAs Pth & "Combined.xls"
End Sub
You can substitute the path and file names in the above code.
RE: How to combine 4 Exel files in to 1 Exel file with 4 sheets?
RE: How to combine 4 Exel files in to 1 Exel file with 4 sheets?
I now KNOW it worked for you.
:)
Mala Singh
'Dare to Imagine'
RE: How to combine 4 Exel files in to 1 Exel file with 4 sheets?
RE: How to combine 4 Exel files in to 1 Exel file with 4 sheets?
In your project DIM the variables as Excel objects thus:
CODE
Dim wbXL As Excel.Workbook
Dim wsXL As Excel.Worksheet
Dim rngXL As Excel.Range
using your own variable names of course
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: How to combine 4 Exel files in to 1 Exel file with 4 sheets?
The code I wrote was in Excel VBA - for which the Excel objects like Workbook, Worksheet etc are native. When using the code from any other VB/VBA environment you need to set reference to the Excel object library and declare the variables as johnwm has suggested.
Mala Singh
'Dare to Imagine'