How to run a function upon opening the Excel file

[johnkimjh]

Hello, in cell A1, I have a function

=@MSTS("F00000GUMA","Historical_SEC_Yield|HSC0N","1/1/1990","12/31/2023","CORR=C, DATES=TRUE, ASCENDING=TRUE, FREQ=D, DAYS=T, FILL=B, HEADERS=TRUE")

This is a function that imports data from Morningstar, and I know for a fact that it works. I want this function to process 

[#2]

@johnkimjh 

To automatically run a function upon opening an Excel file, you can use the Workbook_Open event in VBA (Visual Basic for Applications). Here's how you can do it:

1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
2. In the Project Explorer window, find the workbook where you want to run the function upon opening. It will be listed as "VBAProject (YourWorkbookName)".
3. Double-click on "ThisWorkbook" to open the code window for the workbook.
4. In the code window, paste the following VBA code:

vba

Private Sub Workbook_Open()
    ' Run your function in cell A1
    Range("A1").Formula = "=YourFunctionHere"
    ' Replace "YourFunctionHere" with your actual function
End Sub

5. Replace "=YourFunctionHere" with your actual function. In your case, it would be:

vba

Private Sub Workbook_Open()
    ' Run the MSTS function in cell A1
    Range("A1").Formula = "=@MSTS(""F00000GUMA"",""Historical_SEC_Yield|HSC0N"",""1/1/1990"",""12/31/2023"",""CORR=C, DATES=TRUE, ASCENDING=TRUE, FREQ=D, DAYS=T, FILL=B, HEADERS=TRUE"")"
End Sub

6. Close the VBA editor.

Now, whenever you open the Excel file, the Workbook_Open event will trigger, and the specified function will be automatically inserted into cell A1.

Make sure to save your workbook as a macro-enabled workbook (*.xlsm) to retain the VBA code. Additionally, note that the Workbook_Open event will only trigger if macros are enabled in the Excel settings. If macros are disabled, the function will not be automatically inserted upon opening the workbook.The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.