CODEWITHSUNDEEP
excelvbscript
Krish13287
Krish13287

Reputation: 198

Add new sheet in Excel (vbscript controlled)

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkBook = objExcel.Workbooks.Add()
i=4
objExcel.cells(1,1) = "Test1"
objExcel.cells(1,1).Font.Bold = True
objExcel.cells(2,1) = "Steps No"
objExcel.cells(2,1).Font.Bold = True
objExcel.cells(2,2) = "Test Steps"
objExcel.cells(2,2).Font.Bold = True
objExcel.cells(2,3) = "Expected Result"
objExcel.cells(2,3).Font.Bold = True
objExcel.cells(2,4) = "Remarks"
objExcel.cells(2,4).Font.Bold = True

'Need to add a new sheet in the excel and add content to it

objWorkBook.SaveAs(strFile)
objExcel.Quit

I am trying to create an Excel spreadsheet and add data to it. By default the content is getting updated in the first sheet. Need to add content to the second sheet. Not sure how to add data in the second sheet.

Upvotes: 2

Views: 23802

Answers (1)

brettdj
brettdj

Reputation: 55672

Something like this

Set objWorkbook = objExcel.Workbooks.Add(1)
adds a single sheet workbook

Set objWorkSheet = objWorkbook.Sheets.Add
a second sheet to work with

shorter version

Dim objExcel, objWorkbook, objWorkSheet
 Set objExcel = CreateObject("Excel.Application")
 objExcel.Visible = True
 Set objWorkbook = objExcel.Workbooks.Add(1)
 Set objWorkSheet = objWorkbook.Sheets.Add

 i = 4
 With objWorkSheet
        .Cells(1, 1) = "Test1"
        .Cells(1, 1).Font.Bold = True
        .Range("A2:D2") = Array("Steps No", "Test Steps", "Expected Result", "Remarks")
        .Range("A2:D2").Font.Bold = True
 End With

full code

 Dim objExcel, objWorkbook, objWorkSheet
 Set objExcel = CreateObject("Excel.Application")
 objExcel.Visible = True
 Set objWorkbook = objExcel.Workbooks.Add(1)
 Set objWorkSheet = objWorkbook.Sheets.Add

 i = 4
 With objWorkSheet
    .Cells(1, 1) = "Test1"
    .Cells(1, 1).Font.Bold = True
    .Cells(2, 1) = "Steps No"
    .Cells(2, 1).Font.Bold = True
    .Cells(2, 2) = "Test Steps"
    .Cells(2, 2).Font.Bold = True
    .Cells(2, 3) = "Expected Result"
    .Cells(2, 3).Font.Bold = True
    .Cells(2, 4) = "Remarks"
    .Cells(2, 4).Font.Bold = True
 End With

Upvotes: 3

Related Questions