using schedual task in windows to run Data Stream request

mohammad.assadsolimani

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!!

Alex Putkov.1

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.