0

Here is the some code i took from someother existing vba to start

Set XL = CreateObject("Excel.Application")
XL.Visible=True 
XL.Workbooks.Open "path\book1.xls"
XL.Worksheets.Add(After:=Sheet1)

Here i need to add two different sheets to book1.xls and save as
book12013DEC03.xls the two sheets come from book2.xls and book3.xls(one sheet
from each) book1.xls has one sheet already and i need the new sheets after this.

XL.ActiveWorkbook.SaveAs "path\book12013DEC03.xls"
XL.ActiveWorkbook.Close
XL.Quit
Community
  • 1
  • 1
Sam
  • 1
  • 2

1 Answers1

1

This should probably get you started

Sub joinws()
    Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = Workbooks("book1.xls")
    'Dim ws As Worksheet
    Set wb2 = Workbooks("book2.xls")
    wb2.Sheets(1).Copy Before:=wb1.Sheets(1)
    Set wb2 = Workbooks("book3.xls")
    wb2.Sheets(1).Copy Before:=wb1.Sheets(2)
    Application.DisplayAlerts = False
    wb1.SaveAs Filename:="book12013DEC03.xls"
    Application.DisplayAlerts = True
End Sub

(minor edition as per OPs comment) (another minor edition to avoid privacy warning popup, as per OPs comment)

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • Actually i need in a format like my code because i am executing this script in my SAS program. and also didn't see the book1.xls in your code.And i am not sure if I can save your code with .vbs extension and run in sas program – Sam Dec 04 '13 at 17:25
  • If you already have book1.xls open, then you would use `Set wb1 = Workbooks("book1.xls")` instead. As for the other part, you asked about Excel VBA, both in the title and body, and that is what I used. I guess you meant VBScript (your code uses it, but I took it only as an example). Although [they are not the same](http://msdn.microsoft.com/en-us/library/ms970436.aspx), you may likely use my code, see [this](http://stackoverflow.com/q/10232150/2707864), or a VBScript version which demands minor changes. Hope it helps. – sancho.s ReinstateMonicaCellio Dec 04 '13 at 21:36
  • Thanks Sancho for your details and I definitely need VBScript because i run that script in my SAS Program at the end. My SAS program creates 3 workbooks and currently they are combining the sheets into a new workbook manually but i want to automate that process by adding the VBScript at the end of my SAS Program. So, do you think you can modify the above program to make it a a VBScript, I would really appreciate it – Sam Dec 05 '13 at 14:00
  • Did you check [the link provided in my previous comment](http://stackoverflow.com/q/10232150/2707864)? You may post the outcome of trying suggestions therein. – sancho.s ReinstateMonicaCellio Dec 05 '13 at 14:26
  • Hi Sancho i did check the link and it works out find but when i am savining the final workbook it throws a warning in a pop up window on top of the final workbook "Privacy Warning: This document contains macros, ActiveX controls, XML expansion pack information, or Web Componenets. These may include personal information that cannot be removed by the Document Inspector. So for this i have to click Ok to finish the process – Sam Dec 05 '13 at 15:39
  • Please check the updated code (got [this](http://www.mrexcel.com/forum/excel-questions/519071-how-remove-privacy-warning.html) after googling "vbscript vba skip Privacy Warning"). – sancho.s ReinstateMonicaCellio Dec 05 '13 at 16:09