How do I create an excel file using VBScript? I searched the net but it just mentions opening an existing file.
This is the extraction from the Internet shown below
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\New_users.xls")
I want to know how do you create a new excel file or .xls using vbscript?
Thanks and regards
Maddy
Here is a sample code
strFileName = "c:\test.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
objWorkbook.SaveAs(strFileName)
objExcel.Quit
set objExcel = CreateObject("Excel.Application")
objExcel.Application.DisplayAlerts = False
set objWorkbook=objExcel.workbooks.add()
objExcel.cells(1,1).value = "Test value"
objExcel.cells(1,2).value = "Test data"
objWorkbook.Saveas "c:\testXLS.xls"
objWorkbook.Close
objExcel.workbooks.close
objExcel.quit
set objExcel = nothing `
'Create Excel
Set objExcel = Wscript.CreateObject("Excel.Application")
objExcel.visible = True
Set objWb = objExcel.Workbooks.Add
objWb.Saveas("D:\Example.xlsx")
objExcel.Quit
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
intRow = 2
dim ch
objWorksheet.Cells(1,1) = "Name"
objWorksheet.Cells(1,2) = "Subject1"
objWorksheet.Cells(1,3) = "Subject2"
objWorksheet.Cells(1,4) = "Total"
for intRow = 2 to 10000
name= InputBox("Enter your name")
sb1 = cint(InputBox("Enter your Marks in Subject 1"))
sb2 = cint(InputBox("Enter your Marks in Subject 2"))
total= sb1+sb2+sb3+sb4
objExcel.Cells(intRow, 1).Value = name
objExcel.Cells(intRow, 2).Value = sb1
objExcel.Cells(intRow, 3).Value = sb2
objExcel.Cells(intRow, 4).Value = total
ch = InputBox("Do you want continue..? if no then type no or y to continue")
If ch = "no" Then Exit For
Next
objExcel.Cells.EntireColumn.AutoFit
MsgBox "Done"
enter code here
This code creates the file temp.xls in the desktop but it uses the SpecialFolders property, which is very useful sometimes!
set WshShell = WScript.CreateObject("WScript.Shell")
strDesktop = WshShell.SpecialFolders("Desktop")
set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add()
objWorkbook.SaveAs(strDesktop & "\temp.xls")
