search and save

[Jorn]

Good Afternoon,

I have a program which is nested into excel that is updated everycouple of days.

The problem is that people save the spreadsheet in a number of locations on their C:Drives, and these copies are not updated.

Would anyone have any idea as to what code you may use to search for the same filename on the C drive and save the current update to it? I would have this happen when the updated program is opened...

Any help would do!

Regards

John.

 

[P Sitaram]

Put this in the Workbook_Open Event code:

Sub UpdateFiles()
    Application.ScreenUpdating = False
    
    Dim sFName, sFPath, sFullName As String
    Dim iCtr As Integer
    
    sFName = ActiveWorkbook.Name
    sFPath = ActiveWorkbook.Path
    sFullName = sFPath & "\" & sFName
    
    With Application.FileSearch
    .NewSearch
    .LookIn = "c:\" 'change to suit
    .SearchSubFolders = True
    .Filename = sFName
    If .Execute > 0 Then
      For iCtr = 1 To .FoundFiles.Count
        If .FoundFiles(iCtr) <> sFullName Then
            On Error Resume Next
            ActiveWorkbook.SaveAs .FoundFiles(iCtr)
            On Error GoTo 0
        End If
      Next iCtr
    End If
  End With
  
  Application.ScreenUpdating = True
End Sub

 

[Jorn]

Cheers matey!

This works an absolute treat!

One thing though- can i get it to save without prompting??

J

 

[HalfAce]

Hi,
I don't see psitaram on at the moment, so I'll give it a go.
I beleive if you put
Application.DisplayAlerts = False
right above this line
ActiveWorkbook.SaveAs .FoundFiles(iCtr)
and Application.DisplayAlerts = True right below it, you should be able to save these files without the prompt.
(Did I guess right on what you're wanting? )
Dan

 

[Jorn]

Matey- You are a living legend!

Cheers!