Forum Discussion

wynandb1's avatar
wynandb1
Contributor
10 years ago
Solved

.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)

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

      • wynandb1's avatar
        wynandb1
        Contributor
        10 years ago

        Hi Helen

         

        Can you help me with one last issue please  ? 

         

        Will it be possible for you to help me to tweak this code to save the date in the file as current date + 2 days ? 

         

        I want it to generate the date 2 days from now, so if today is Wednesday, it should give me Friday's date ? 

         

        Kind Regards

        Wynand