.VBS Script Help Please
Good day
Could someone please assist with my problem if possible ? Not sure if this is the right forum ? Please note that I got this from the web. I am not a developer or a VB script guy.
I have this .vbs scrip that I want to use to populate a cell in a excel spreatsheet with the current date each time I execute the script.
The script that I have, opens the excel workbook, and populates the cell, but does not save and close the workbook.
I cannot seem to get it to do this ?
Any help would be highly appreciated.
******
Dim objXl
Dim objWb
Dim dtMonth
Set objXl = CreateObject("EXCEL.APPLICATION")
dtMonth = Right(String(2,"0") & Month(date), 2)
objXl.workbooks.OpenText"C:\Users\Wynandb\Desktop\FolderName\FileName.xls"
objXl.Visible = true
objXl.cells(3,1).Value = (DatePart("yyyy",Now()))& Right(String(2,"0") & Month(date), 2)&Right(String(2,"0") & Day(date), 2)
objXL.Save
objXL.Close
******
Kind Regards
Wynand
Hi Wynand,
You need to call the .Save and .Close methods against the workbook object, not the Excel object:Dim objXl, objWb, value
value = DatePart("yyyy", Now()) _
& Right(String(2, "0") & Month(Date), 2) _
& Right(String(2, "0") & Day(Date), 2)
Set objXl = CreateObject("Excel.Application") objXl.Visible = True
Set objWb = objXl.Workbooks.Open("C:\Users\Wynandb\Desktop\FolderName\FileName.xls") objWb.ActiveSheet.Cells(3,1).Value = value objWb.Save objWb.Close objXL.Quit
Other notes:- Use .Open to open Excel files. .OpenText is for parsing text files.
- If this code runs in TestComplete, use aqConvert.DateTimeToFormatStr method to format date strings:
value = aqConvert.DateTimeToFormatStr(aqDateTime.Now, "%Y%m%d")
