help on calling macro

Contributor

the macro is supposed to open all files in a specific folder call/execute a couple of macros in the just opened workbook save and close it, if i omit the call for macro it looks to work fine. the problem start when i try to call the macro to run, i get this message. any suggestion will be of a great help

thank you

 

 

olopa67_0-1647833634551.png

 

Sub AllFiles()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
  
    folderPath = "O:\1Paolo\360 CATERING\RECEPE AND COSTING\RECEPES\"
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.xlsm")
    Do While filename <> ""
      Application.ScreenUpdating = True
        Set wb = Workbooks.Open(folderPath & filename)
         
        'Call a subroutine here to operate on the just-opened workbook
        Call refresh_recipe
        DoEvents
        Call index_update
        DoEvents
        
        
        filename = Dir
        ActiveWorkbook.Close
            Loop
  Application.ScreenUpdating = True
End Sub

 

5 Replies
오늘 처음으로 액셀 공부를 시작하였습니디,매우 어려움을 느끼고 있습니다.어느수준이
됬을 때 토론방을 방문하려합니다.감사합니다. 서서
Perhaps try (there are single quotes around the workbook name, which is necessary if there are spaces - but, I don't think it will hurt anything if your workbook name does not have spaces).

Application.Run "'" & wb.name & "'!Macro_Name"

@JMB17 

thank you for your help

it works fine when i call the INDEX_UPDATE macro but it doesn't update the power query connection when it run the REFRESH_RECIPE macro. basically it should refresh the table after it opens the recipe workbooks file and then update the INDEX FILES but it doesn't perform the refresh. i tried to avoid the macro and have the connection to be refresh on the opening of the file but still it doesn't refresh the connection. it works if manually open the file. i try to slowdown the macro but no luck either.

i hope i was able to explain the issues clearly enough, im sorry but english is not my main language.

thank you again    

 

 

 

 

 

 

Sub AllFiles()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
  'Application.DisplayAlerts = False

    folderPath = "O:\1Paolo\360 CATERING\RECEPE AND COSTING\RECEPES\"
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.xlsm")
    Do While filename <> ""
      Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & filename)
         DoEvents
        'Application.Run "'" & wb.Name & "'!refresh_recipe"
        Application.Run "'" & wb.Name & "'!index_update"
        DoEvents
        'Call a subroutine here to operate on the just-opened workbook
        'Call refresh_recipe
        'DoEvents
        'Call index_update
        'DoEvents
        'Application.Wait (Now + TimeValue("0:00:15"))
        Application.Run "'" & wb.Name & "'!save"
        DoEvents
        
        filename = Dir
        ActiveWorkbook.Close SaveChanges:=False
        
            Loop
  Application.ScreenUpdating = False
End Sub

 

Is the query associated with refresh_recipe set to run in the background? If so, then the macro will be continuing before the query finishes.
thank you JMB17 i resolved with this application code
Application.CalculateUntilAsyncQueriesDone
thank you for your time, i really appreciated