Skip to content

Instantly share code, notes, and snippets.

@nlharri
Forked from simply-coded/CreateExcelFile.vbs
Created November 19, 2018 13:50
Show Gist options
  • Save nlharri/a8a230631789367b54854e606ef89417 to your computer and use it in GitHub Desktop.
Save nlharri/a8a230631789367b54854e606ef89417 to your computer and use it in GitHub Desktop.
Use VBScript to create, open, and edit excel files. ( Excel needs to be installed on your computer ).
'Microsoft Excel Automation Basics
':: Create and edit an Excel File.
'---------------------------------
'create the excel object
Set objExcel = CreateObject("Excel.Application")
'view the excel program and file, set to false to hide the whole process
objExcel.Visible = True
'add a new workbook
Set objWorkbook = objExcel.Workbooks.Add
'set a cell value at row 3 column 5
objExcel.Cells(3,5).Value = "new value"
'change a cell value
objExcel.Cells(3,5).Value = "something different"
'delete a cell value
objExcel.Cells(3,5).Value = ""
'get a cell value and set it to a variable
r3c5 = objExcel.Cells(3,5).Value
'save the new excel file (make sure to change the location) 'xls for 2003 or earlier
objWorkbook.SaveAs "C:\Users\UserName\Desktop\vbsTest.xlsx"
'close the workbook
objWorkbook.Close
'exit the excel program
objExcel.Quit
'release objects
Set objExcel = Nothing
Set objWorkbook = Nothing
'Microsoft Excel Automation Basics
':: Open and edit an Excel File.
'---------------------------------
'create the excel object
Set objExcel = CreateObject("Excel.Application")
'view the excel program and file, set to false to hide the whole process
objExcel.Visible = True
'open an excel file (make sure to change the location) .xls for 2003 or earlier
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\UserName\Desktop\vbsTest.xlsx")
'set a cell value at row 3 column 5
objExcel.Cells(3,5).Value = "new value"
'change a cell value
objExcel.Cells(3,5).Value = "something different"
'delete a cell value
objExcel.Cells(3,5).Value = ""
'get a cell value and set it to a variable
r3c5 = objExcel.Cells(3,5).Value
'save the existing excel file. use SaveAs to save it as something else
objWorkbook.Save
'close the workbook
objWorkbook.Close
'exit the excel program
objExcel.Quit
'release objects
Set objExcel = Nothing
Set objWorkbook = Nothing
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment