VBA to open, update links, save, and close files.

[jaihawk8]

I have 75 earning statements that all reside in the same folder.

When I manually update them, I:

• Open each file in Excel
• I receive a message box saying "This workbook contains links to one or more external sources that could be unsafe. If you trust the links, update them to get the latest data. Otherwise, you can keep working with the date you have."
• I click on the update button
• The file updates
• I click save
• I close the file

I am hoping to find a VBA solution that would open each one of the files automatically and do the steps listed above.

Is this possible?

 

[breynolds0431]

You can add that to part of the loop before the wbSource.Close happens.

For example:

Do Until sFile = ""
          'open the file
          Set wbSource = Workbooks.Open(FOLDER_PATH & sFile)
       
          'update the data links
          ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
         
         'ensure Q2 tab is activated
          wbSource.Sheets("Q2").Activate

          'close the file
          wbSource.Close savechanges:=True
          
          sFile = Dir() 'Added to reset sFile
Loop

If that should change during each quarter change, you could calculate the current quarter. Assuming a January to December fiscal year:

dim qrt as long: qrt = (Month(date()) + 2) \ 3  'sets the current quarter

Do Until sFile = ""
          'open the file
          Set wbSource = Workbooks.Open(FOLDER_PATH & sFile)
       
          'update the data links
          ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
         
         'ensure current Q tab is activated
          wbSource.Sheets("Q" & qrt).Activate 

          'close the file
          wbSource.Close savechanges:=True
          
          sFile = Dir() 'Added to reset sFile
Loop