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 ...
  • 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?

      • wynandb1's avatar
        wynandb1
        Contributor
        10 years ago

        Hi Helen

         

        It worked this time. Thank you very much for your assistance!! Its much appreciated!!

         

        This will help me more then you can imagine!!

         

        Have a great day

         

        Kind Regards

        Wynand