VBScript to add an additional sheet in excel

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!

Answer 1

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

Answer 2

Thanks, Andreas.

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

---

Crynet