Mar 20 2022 08:34 PM
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
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
Mar 20 2022 08:50 PM
Mar 20 2022 10:51 PM
Mar 22 2022 04:43 AM
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
Mar 22 2022 03:10 PM
Mar 26 2022 02:38 AM