How to code it in macro?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Does anyone have any suggestions on how to code it in macro?

There is a list of files under column A, I would like to create a macro to
open - updating all links - save - close each file one by one for each file
from A2 to A20. There is one condition to process each file, for example;

The following files will not be processed until the last updated for this
file C:\documents\A.xls is today, then open - updating all links - save -
close following files,

C:\documents\A1.xls in cell A2
C:\documents\A2.xls in cell A3
C:\documents\A3.xls in cell A4

The following files will not be processed until the last updated for this
file C:\documents\B.xls is today, then open - updating all links - save -
close following files,

C:\documents\B1.xls in cell A5
C:\documents\B2.xls in cell A6
C:\documents\B3.xls in cell A7

The following files will not be processed until the last updated for this
file C:\documents\C.xls is today, then open - updating all links - save -
close following files,

C:\documents\C1.xls in cell A8
C:\documents\C2.xls in cell A9
C:\documents\C3.xls in cell A10
....

Does anyone have any suggestions on how to code it in macro?
Thanks in advance for any suggestions
Eric
 
See if this works for you.

Option Explicit

Sub Test()
Dim aWS As Excel.Worksheet
Dim myRange As Excel.Range
Dim myWB As Excel.Workbook
Dim r As Excel.Range

Set aWS = ActiveSheet
Set myRange = aWS.Range("A2:A10")

For Each r In myRange
Set myWB = Workbooks.Open(r.Text, UpdateLinks:=True)
myWB.Close savechanges:=True
Set myWB = Nothing
Next r

End Sub
 
Thank you very much for suggestions
The condition to hold processing the files is missing, for example, if the
last updated for this file C:\documents\B.xls is yesterday, then please hold
the following lists for processing:

C:\documents\B1.xls in cell A5
C:\documents\B2.xls in cell A6
C:\documents\B3.xls in cell A7

Do you have any suggestions on how to add this condition in coding?
I get no idea on how to do it,
Please help
Thank you very much for any suggestions
Eric
 
Back
Top