I have a VBA function that opens a file, refreshes all the data connections, saves the file and the returns "true" if the refresh was successful. Here is the code:
Public Function RefreshFile(ByVal wbName As String, ByVal FilePath As String, ByVal pword As String)
Dim blTmp As Boolean
Dim wbRefresh As Workbook
blTmp = Not (IsWorkBookOpen(FilePath))
If blTmp Then
Workbooks.Open Filename:=FilePath, ReadOnly:=False, Notify:=False, WriteResPassword:=pword, IgnoreReadOnlyRecommended:=True
Else
RefreshFile = False
Exit Function
End If
Set wbRefresh = Workbooks(wbName & ".xlsx")
wbRefresh.Activate
ActiveWorkbook.RefreshAll
DoEvents
'MsgBox "complete"
wbRefresh.Close savechanges:=True
RefreshFile = True
End Function
The list opens a series of files and some are password protected and read-only suggested (hence the parameters passed). With the read-only suggested files though, files get saved with only one of the connections refreshed. Basically it refreshes the first connection and then saves the file.
I've tested this a number of times and if I stop the function after 'DoEvents' (notice the MsgBox to stop the code), then all the connections are in fact refreshed. If I don't stop the code and reopen the saved file later on, only one of the connections get refreshed. Is this an issue with 'RefreshAll' or when it goes to save the file? Perhaps I'm not opening the file with the full, appropriate permissions??
