MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question
I posted this somewhere else and have not received any feedback. I'm hoping someone here may be able to help. I have been trying to get the stockhistory function to work with my workbook open event, but whenever the workbook opens, I get a '1004' error. However when I run the routine manually after the workbook is open, it works fine. Any help would be great. Below is the code in the workbook open routine.
Private Sub Workbook_Open() Dim aStock As String Dim aDate As Date Dim aStock_Price As Variant 'this does not work on workbook open event, but works when run after open aStock = "TSLA" aDate = "11/4/2022" aStock_Price = Application.WorksheetFunction.StockHistory(aStock, aDate, aDate, 0, 0, 1) 'code error here on workbook open event MsgBox ("Test stockhistory function: " & aStock_Price(1)) 'this seems to work on workbook open event Sheets("Sheet1").Range("C3") = "" Sheets("Sheet1").Range("C3").Formula = "=" & Sheets("Sheet1").Range("B3").Address(False, False) & ".Price" MsgBox ("Test stock.price function on sheet1: " & Sheets("Sheet1").Range("C3")) End Sub