How to write data into Excel using VBscript

In order to write data into Excel using VBScript, we can leverage Excel.application object. This object provides multiple methods which help us to create, open, close, or delete a workbook or sheet for reading, and writing purposes.

On this page, we have a list of functions for write capability of Excel files using VBscript such as the Creation of an Excel file, write data into Excel using VBScript, and Copy data from one sheet to another using VBScript

Example 1: How to Create an Excel Workbook using VBScript

Function createExcel()

  'Create a new Microsoft Excel object
  Set myxl = createobject("excel.application")
  'To make Excel visible
  myxl.Application.Visible = true 
  myxl.Workbooks.Add
  wait 2

  'Save the Excel file as qtp.xls
  myxl.ActiveWorkbook.SaveAs  "D:\qtp.xls"
  'close Excel
  myxl.Application.Quit
  Set myxl=nothing
  
End Function

Example 2: How to write data into Excel using VBScript

Function createExcelEnterData()
  Set myxl = createobject("excel.application")

  'Make sure that you have created an excel file before exeuting the script.
  'Use the path of excel file in the below code
  'Also make sure that your excel file is in Closed state before exeuting the script.

  myxl.Workbooks.Open "D:\qtp.xls" 
  myxl.Application.Visible = true

  'this is the name of  Sheet  in Excel file "qtp.xls"   where data needs to be entered 
  set mysheet = myxl.ActiveWorkbook.Worksheets("Sheet1")

  'Enter values in Sheet1.
  'The format of entering values in Excel is excelSheet.Cells(row,column)=value
  mysheet.cells(1,1).value ="Name"
  mysheet.cells(1,2).value ="Age"
  mysheet.cells(2,1).value ="Ram"
  mysheet.cells(2,2).value ="20"
  mysheet.cells(3,1).value ="Raghu"
  mysheet.cells(3,2).value ="15"

  'Save the Workbook

End Function

Example 3: How to Copy data from one sheet to another sheet using VBScript

Function copyOneSheetToAnother()

  Set myxl = createobject("excel.application")

  'To make Excel visible
  myxl.Visible = True

  'Open  a workbook "qtp1.xls"
  Set Workbook1= myxl.Workbooks.Open("C:\qtp1.xls")

  'Open  a workbook "qtp2.xls"
  Set Workbook2= myxl.Workbooks.Open("C:\qtp2.xls")

  'Copy  the used range of  workbook "qtp1.xls"
  Workbook1.Worksheets("Sheet1").UsedRange.Copy

  'Paste the copied values in above step in the  A1 cell  of  workbook "qtp2.xls"
  Workbook2.Worksheets("Sheet1").Range("A1").PasteSpecial Paste =xlValues

  'Save the workbooks
  Workbook1.save
  Workbook2.save

  'close the workbooks
  Workbook1.close
  Workbook2.close

  myxl.Quit
  set myxl=nothing

End Function