Open a sheet, execute a function from an add-in, and save

Question

I need to runs a thousand of excel files (.xlsx) to get data. Each file has many sheets, and in each sheet, cell A1 contains a function of an Excel add-in (Morningstar Excel add-in). Right now, I have to manually open each file. When the add-in loads, the function in cell A1 gets executed and cell A1 displays "Processing...". I would have to wait a few seconds or minutes for the data to return. Once the sheet is filled with data, I would save it as a csv file.

How do I automate this process?

I have written a macro to open the excel files and save the sheets as CSV files. However, it bypasses the data requesting and downloading process. I added the option to wait a few seconds but the Excel files opens in a frozen status, i.e. the add-in is not loaded and the function in cell A1 does not run. How can I:

1. Open the file
2. Make sure the add-in is loaded
3. Make sure the function in cell A1 of each sheet runs
4. Check if there is any data. One way is to check, say cell A10, is not empty
5. Save the sheet as a CSV file

Here is my code so far:

Sub morningstar_VBA()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim filename As String
Dim path_to_save As String
Dim FldrPicker As FileDialog
Dim w As Long

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xlsx*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(filename:=myPath & myFile)

    'Ensure Workbook has opened before moving on to next line of code
    For w = 1 To Worksheets.Count
        With Worksheets(w).Copy
            'the ActiveWorkbook is now the new workbook populated with a copy of the current worksheet
            With ActiveWorkbook
                filename = .Worksheets(1).Name
                path_to_save = "E:\Morningstar_download\test\" & filename
                .SaveAs filename:=path_to_save, FileFormat:=xlCSV
                DoEvents
                .Close savechanges:=False
            End With
        End With
    Next w

    wb.Close savechanges:=True

    'Ensure Workbook has closed before moving on to next line of code
    DoEvents

    'Get next file name
    myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Answer 1

include the code below to refresh add-ins, i.e. making sure all functions from add-ins run. Not sure about other add-ins

Set cmd = Application.CommandBars("Cell").Controls("Refresh All")
    cmd.Execute