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