Forum Discussion

wynandb1's avatar
wynandb1
Contributor
10 years ago

.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 

  • HKosova's avatar
    HKosova
    10 years ago

    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")
  • HKosova's avatar
    HKosova
    SmartBear Alumni (Retired)
    10 years ago

    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")
    • wynandb1's avatar
      wynandb1
      Contributor
      10 years ago

      Hi Helen

       

      Thank you for the reply.  This code doen not run in TC. Its a .VBS file I want to run using a .BAT file.

       

      However, I tried to use your code as you put it here, I just rectified the path with the corrent file name and it doesnt work. 

       

      I get the following error message on Line 4 Char 31 Error : Expected end of staement.

       

       

      • HKosova's avatar
        HKosova
        SmartBear Alumni (Retired)
        10 years ago

        Updated the code above - could you please try once again?