I have an excel workbook that I need to open on a loop and get refreshed. This is a sample of the code from the workbook:
Sub Refresh_Report()
Dim msg As String
Dim msgPrompt As String
msg = "Would you like to refresh all the reports?"
msg = msg & vbNewLine & vbNewLine
msg = msg & "Yes - Refresh"
msg = msg & vbNewLine
msg = msg & "No - Cancel"
msg = msg & vbNewLine & vbNewLine & vbNewLine
msgPrompt = MsgBox(msg, vbYesNo, "Refresh Report?")
If xmsgPrompt= vbYes Then
Sheets("First Sheet").Select
Range("A1").Select
Application.Run ("Process")
Else
Exit Sub
End If
End Sub
This sub works great and is not the problem. The problem lies with when I use another macro on another workbook to open all the files in a folder, run the macro, dave and close the workbook and loop to the next one. I have that macro working great... except for the prompt box. When the macro launches (which is does), it stops when the message box opens. I'm trying to have 'yes" automatically selected so it will continue running the macro.
A sample of the code there is:
...
Do While myFile <> ""
Set wb = Workbooks.Open(Filename:=myPath & myFile)
wb.Application.Run ("'" & myFile & "'" & "!" & "Refresh_Report")
wb.Close SaveChanges:=True
myFile = Dir
Loop
...
I have tried putting Application.DisplayAlerts = False before the run part and Application.DisplayAlerts = True after. I have tried using SendKeys "{Enter}" after running the macro. I've tried a whole host of things. The closest I have got is using SendKeys "{Enter}". It did not work for the first file in the folder but it did on the second, not on the third and so on.
Thanks again for your help,
Matt