How to combine 4 Exel files in to 1 Exel file with 4 sheets? 1

[kris2002]

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

 

[Mala]

Assuming that all four source files are in the folder "C:\Documents and Settings\Mala Singh\Desktop" and are named Wkbk1, Wkbk2, Wkbk3 and Wkbk4, the following code will take the first sheet of each of the 4 files, put them in a new workbook and save the combined workbook in the same folder as the source files:

Sub CombineSheets()
  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.

 

[kris2002]

Mala, thanks for your input. I will definitely try this out and let you know. Thanks again.

 

[Mala]

kris:
I now KNOW it worked for you.




Mala Singh
'Dare to Imagine'

 

[kris2002]

Hi Mala, I tried your code. I get an error when I compile the code using visual basic 6. Here is the error I get on the line "Dim SourceWkbk As Workbook, CombinedWkbk As Workbook". It gives me "User-defined type not specified". I have no clue how to fix it. If you could help me with this I would appreciate it. Thanks. - Kris

 

[johnwm]

If you're using VB6 then you need to add reference to the Excel object library. Go to Project menu - References and make sure the Microsoft X.X Object Library is selected.

In your project DIM the variables as Excel objects thus:

Dim appXL As Excel.Application
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:

http://www.essexsteam.co.uk

 

[Mala]

Kris,
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'