Answered by:
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.QuitI 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 Friday, August 21, 2015 2:15 PM Move to more appropriate forum
- Moved by Varsha GAdministrator Wednesday, September 14, 2016 4:19 AM data issue
- Moved by Varsha GAdministrator Wednesday, September 14, 2016 4:21 AM data issue
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