Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it in macro?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it in macro?
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 -- HTH, Barb Reinhardt "Eric" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it in macro?
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 "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Eric" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to dll and vba code trouble shoot. | Excel Programming | |||
Slow code when used as VBA code instead of macro (copying visible columns) | Excel Programming | |||
Can I use code/macro to change code/macro in an existing file? | Excel Programming | |||
read macro code by vb code | Excel Programming | |||
do anybody have a sample code for executing excel macro from vb code?<eom | Excel Programming |