Results 1 to 6 of 6
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Update Files in a Directory (95/97/2000)

    I have updated a master costing template so a formula references a constant from a linked sheet instead of being embedded into the formula. However as we have 1000's of existing files to update, opening each file, running an update macro, closing and then opening the next file will be a long tedious task.
    As a really tyro tyro VBA person, is there a way that VBA code will open the first file in the current directory, run the formula update as per attached, save the file, open the next file, and stop when the last file is updated?
    <img src=/S/help.gif border=0 alt=help width=23 height=15><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Attached Files Attached Files
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Files in a Directory (95/97/2000)

    See if THIS THREAD will get you started.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Files in a Directory (95/97/2000)

    Adapting the code that Legare pointed to, the following should work for a folder named Cata <pre>Public Sub GlueUpdate()
    Dim strFName As Variant
    Dim oWB As Workbook
    Application.ScreenUpdating = False
    strFName = Dir("Cata*.xls", vbNormal)
    While strFName <> ""
    Set oWB = Workbooks.Open("Cata" & strFName)
    ActiveSheet.Unprotect
    <font color=red>Range("AY7").FormulaR1C1 = _
    "=IF(RC[-39]=0,"""",(RC[-39]*[CM2002.xls]FoamFibre!R21C6)*[CM2002.xls]FoamFibre!R20C6)"
    Range("AY7:AY23").FillDown</font color=red>
    ActiveSheet.Protect
    With oWB
    .Save
    .Close
    End With
    strFName = Dir()
    Wend
    Application.ScreenUpdating = True
    End Sub</pre>

    The lines in red assign the formula and copy it to the adjacentt cells. As the activesheet when you open a workbook will be the activesheeet when the workbook was last saved, you might want to ensure the correct sheet is in use, rather than the active sheet.

    Andrew C

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Files in a Directory (95/97/2000)

    As I just realised your formula contains a reference to an external file, you may wish to preventt the code looking for permission to update links. You can replace the line that opens the file with <pre> Set oWB = Workbooks.Open("Eata" & strFName, UpdateLinks:=0)</pre>

    The 0 (zero) value for updatelinks does not update any links. You may use any of the following :

    0 Doesn't update any references
    1 Updates external references but not remote references
    2 Updates remote references but not external references
    3 Updates both remote and external references

    Andrew C

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Update Files in a Directory (95/97/2000)

    Thank you Legare and Andrew, I will work with your code after the month end roll over. Your solutions specify the directory in the code, if I deleted that line would the code update all the files in the current opened directory? I ask this as the "costings" directory is the upper level, with numerous customer named folders under the parent. I intended the user to open a customer directory, update files in that directory, then navigate to the next directory.
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Files in a Directory (95/97/2000)

    No, it will not recurse through subdirectories, that is much more complicated. I'll have to think about how to do that.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •