none
VBScript to add an additional sheet in excel RRS feed

  • Question

  • I am new to VBScript. I want to create a script through which I can import data from text file (delimited) in an excel and then add an additional sheet in the excel. Also want to rename booth the sheets and move data from the 1st sheet to the additional sheet. I have created a script which is working till saving the file but can't manage to add an additional sheet to it. Below provided the code:

    Const xlDelimited = 1                                       
    Set objExcel = CreateObject("Excel.Application")             
    objExcel.DisplayAlerts = FALSE
    objExcel.Visible = false                                     
    objExcel.workbooks.Opentext "D:\prasenjit\automation testing\Diag_output.txt",,,xlDelimited,,,,,,,True,"|"
    objExcel.ActiveWorkbook.SaveAs "D:\prasenjit\automation testing\vbstest.xls", 1 
    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit

    I am using the below statements to add an additional sheet:

    set objworkbook = objExcel.workbooks.add(1)
    set objworksheet = objworkbook.worksheets.add(,objworkbook.worksheets(objworkbook.worksheets.count))

    But either it is opening a second workbook opening (Book1.xls) automatically without any delimiited text file records or no additional sheets are getting added but the text file is getting imported with sheet name as the text file name!

    Friday, August 21, 2015 12:47 PM

Answers

  • Const xlDelimited = 1
    Path = "D:\prasenjit\automation testing\"
    'Create a new instance of Excel
    Set xlApp = CreateObject("Excel.Application")
    'Open the text file
    xlApp.Workbooks.OpenText Path & "Diag_output.txt", , , xlDelimited, , , , , , , True, "|"
    'Get the workbook
    Set xlWb = xlApp.ActiveWorkbook
    'Get the sheet
    Set xlWs = xlWb.ActiveSheet
    'Rename it
    xlWs.Name = "My 1st Sheet"
    'Add another one
    Set xlWs = xlWb.Worksheets.Add(, xlWb.Worksheets(xlWb.Worksheets.Count))
    'Rename it
    xlWs.Name = "My 2nd Sheet"
    'Save the file
    xlWb.SaveAs Path & "test.xls", 1
    'Close it
    xlWb.Close
    'Done
    xlApp.Quit

    • Proposed as answer by David_JunFeng Monday, August 24, 2015 6:39 AM
    • Marked as answer by crynet Monday, August 24, 2015 7:31 AM
    Friday, August 21, 2015 2:42 PM

All replies

  • Const xlDelimited = 1
    Path = "D:\prasenjit\automation testing\"
    'Create a new instance of Excel
    Set xlApp = CreateObject("Excel.Application")
    'Open the text file
    xlApp.Workbooks.OpenText Path & "Diag_output.txt", , , xlDelimited, , , , , , , True, "|"
    'Get the workbook
    Set xlWb = xlApp.ActiveWorkbook
    'Get the sheet
    Set xlWs = xlWb.ActiveSheet
    'Rename it
    xlWs.Name = "My 1st Sheet"
    'Add another one
    Set xlWs = xlWb.Worksheets.Add(, xlWb.Worksheets(xlWb.Worksheets.Count))
    'Rename it
    xlWs.Name = "My 2nd Sheet"
    'Save the file
    xlWb.SaveAs Path & "test.xls", 1
    'Close it
    xlWb.Close
    'Done
    xlApp.Quit

    • Proposed as answer by David_JunFeng Monday, August 24, 2015 6:39 AM
    • Marked as answer by crynet Monday, August 24, 2015 7:31 AM
    Friday, August 21, 2015 2:42 PM
  • Thanks, Andreas.

    WOW!!! works like a charm. Just request you to explain what I was missing in my code.



    Crynet

    Monday, August 24, 2015 7:41 AM