0

Please help,

my vbscript: saveGSheet.vbs (to download sheet) works fine when manually run. I woud like to automat this, but an automatic task will NOT download the file.

How to run this in Task Scheduler? On Windows Server 2008.

Task Scheduler - settings:

  • Action: Run program: saveGSheet.bat with code: c:\Windows\SysWOW64\cscript saveGSheet.vbs
  • Run task as LOCAL SERVICE
  • Run task when NOT LOGGED IN

Tried already:

  • Run with highest privileges does not help
  • creating "Desktop" folders does not help
  • "Log on as a batch job" does not help

Possible reasons:

Not logged in means objects doesnt work properly - possibly needs to be run in interactive mode like MS Excel? I do not understand what in my script needs to be run interactive (logged in)?

  • "MSXML2.XMLHTTP.3.0"
  • "ADODB.Stream"

Vbscript:

' Set your settings
strFileURL = "https://docs.google.com/spreadsheets/d/1B5jBWGHT1dGKCwE9KLTlFsyymNCc1s4AH1LcFQOcwqQ/export?format=xlsx"
strHDLocation = "C:\file.xlsx"

' Fetch the file
Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP.3.0")

objXMLHTTP.open "GET", strFileURL, false
objXMLHTTP.send()

'Response 200 is OK, now download sheet
If objXMLHTTP.Status = 200 Then
  Set objADOStream = CreateObject("ADODB.Stream")
  objADOStream.Open
  objADOStream.Type = 1 'adTypeBinary

  objADOStream.Write objXMLHTTP.ResponseBody
  objADOStream.Position = 0    'Set the stream position to the start

  objADOStream.SaveToFile strHDLocation
  objADOStream.Close
  Set objADOStream = Nothing
End if

Note: I should not use any third party apps, so I dont use wget for example.

EDIT: Made log file as suggested, result: 

Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

C:\AppBackUpTools\ALM\NotifikaceVSE\SLA_Escalation\download_excel\saveGSheet.vbs(27, 3) ADODB.Stream: Write to file failed.
Petr Schiebel
  • 1
  • 4

2 Answers2

0

modify your saveGSheet.bat as below:

c:\Windows\SysWOW64\cscript saveGSheet.vbs > %temp%\saveGSheet.log 2>&1

after the task is executed, examine the content of the log file at %temp%\saveGSheet.log that should give you some clue to debug further

some1
  • 857
  • 5
  • 11
-1

You have no rights to write there. Change the folder ie to the folder where you have vbscript.

Zbynak
  • 1