question

Upvotes
Accepted

using schedual task in windows to run Data Stream request

I have 3 Data Stream requests, which are running by opening

I use a vba-code to open these 3 Data-Stream requests


Public Sub cron()
Dim wb As Workbook
Set wb = Workbooks.Open("O:\Corona Reporting\data\Aktien_Aktuell.xlsm")
  wb.Save
' export

    Dim MyFileName As String
    Dim CurrentWB As Workbook, TempWB As Workbook

    Set CurrentWB = wb
    ActiveWorkbook.Worksheets("Data").UsedRange.Copy

    Set TempWB = Application.Workbooks.Add(1)
    With TempWB.Sheets(1).Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With

    'Dim Change below to "- 4"  to become compatible with .xls files
    MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & ".csv"

    Application.DisplayAlerts = False
    TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    TempWB.Close SaveChanges:=False
    Application.DisplayAlerts = True
'end of export
  wb.Close SaveChanges:=False
  '
  Set wb = Workbooks.Open("O:\Corona Reporting\data\Renten_Aktuell.xlsm")
  wb.Save
  wb.Close SaveChanges:=False
  Set wb = Workbooks.Open("O:\Corona Reporting\data\Swaprates_Aktuell.xlsm")
  wb.Save
  wb.Close SaveChanges:=False
  ThisWorkbook.Saved = True
  Application.Quit
End Sub

and finally, I am using a vbs-Script (crontab.vbs) to call the vba file (crona.xlsm) above.

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
'~~> Change Path here
Set xlBook = xlApp.Workbooks.Open("O:\Corona Reporting\data\crona.xlsm", 0, True)
xlApp.Run "cron"

xlBook.Saved = True
xlBook.Save
'xlBook.Save
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "Finished."
WScript.Quit

Now the problem:

if I execute the vba vba file (crona.xlsm) directly, I get all Data Stream requests. However, if I execute the crontab.vbs the Data Stream requests are not updated!!

datastream-apidsws-apivbavbscript
1588580937812.png (54.5 KiB)
tempsnip.png (264.6 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Upvotes
Accepted
39.3k 76 11 27

When you launch Microsoft Excel as an OLE Automation object using the CreateObject command, add-ins, files that are located in the XLStart directory, and the default new workbook are not loaded. See this thread for discussion on similar topic and to learn how to deal with this.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.