×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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?

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?

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:

CODE

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.

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

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

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

kris:
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?

(OP)
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

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

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:

CODE

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: www.essexsteam.co.uk

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

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'

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources