VBScript to add an additional sheet in excel

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!

  • Moved by Bill_Stewart 16 hours 54 minutes ago Move to more appropriate forum
August 21st, 2015 8:49am

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

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 10:44am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics