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