How to Refresh multiple workbook in a directory?

[dmadhup]

I am using following code to refresh the workbook. However, If I have more than one workbook (for example 10) then how do I optimize my code.
OR
Do I have to repeat code 10 times? Any idea, please.

Sub refresh()
Application.ScreenUpdating = False
Dim filename1 As String
filename1 = "C:\Users\Cost.xlsx"


If Len(Dir(filename1)) = 0 Then
    MsgBox "Could not find the file " & filename1, vbExclamation, "File Not Found"
    Exit Sub
Else
    Workbooks.Open filename1, ReadOnly:=False, IgnoreReadOnlyRecommended:=True
    Workbooks(ThisWorkbook.Name).RefreshAll
  
    Windows("Cost.xlsx").Activate
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End If

 

[SpillerBD]

Repeat the code or set the Data Connections in each file to refresh when opening the file.

 

[dmadhup]

Hi SpillerBD,

Thank you for a response. I have made data connection on each file to the SQL database. The file data is only updated after we do Refresh. And there is more than 10 file which all has a data connection, therefore I am trying to optimize the above code using some kind loop.

 

[SpillerBD]

I cannot say I'm crazy about it but you can work with this.
Update the DirLoc for your purpose, though make that a test folder with copies of your files.

Sub RefreshWBs()
    Dim CurFile As String, DirLoc As String
    Dim DestWB As Workbook
    Dim ws As Object 'allows for different sheet types
    Dim OrigWB As Workbook
    [COLOR=#0000ff]DirLoc = "H:\MSO\Documents"[/COLOR]
    CurFile = Dir(DirLoc & "\*.xls*")
    
    Application.ScreenUpdating = True
    Application.EnableEvents = False
            
    Do While CurFile <> vbNullString
        Application.DisplayAlerts = False
        
        'Workbooks.Open Filename:=DirLoc & "\" & CurFile
        Set OrigWB = Workbooks.Open(Filename:=DirLoc & "\" & CurFile)
        
        OrigWB.RefreshAll
        Application.DisplayAlerts = True
        OrigWB.Close SaveChanges:=True
        CurFile = Dir
    Loop
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
 
      Set DestWB = Nothing
  End Sub

 

[dmadhup]

Thank you SpillerBD for the update!