Open, allow link updates, and Save

Hi All..........

If someone would be so kind, I am in need of a macro that will reside in a
Master WorkBook and when run as a "before save event", would open and allow
update all links therein, and save, one at a time, all other .xls files in
the same directory whose filename start with "XY-" no
quotes............exact quantity of the files is unknown, but may be 10 to
100. Any other files in the directory, including the Master, not having the
leading "XY-" in their filename are not to be affected.


TIA..........

Vaya con Dios,
Chuck, CABGx3

Dim v() as String
Dim sName as String
Dim i as Long, j as Long
redim v(1 to 100)

sName = dir( _
thisworkbook.Path & "\XY-*.xls")
i = 0
Do while sName <> ""
i = i + 1
v(i) = thisworkbook.Path & "\" & sname
sName = Dir()
Loop
redim preserve v(1 to i)
for j = 1 to ubound(v)
set bk = workbooks.Open( _
FileName:=v(j), UpdateLinks:=3)
bk.close Savechanges:=True
Next

Untested, but this should be something like what you are looking for (as I
understand you question and it wasn't real clear). Assumes master does not
start with XY-



--
Regards,
Tom Ogilvy



"CLR" <croberts@tampabay.rr.com> wrote in message
news:uGGkUioUFHA.3572@TK2MSFTNGP12.phx.gbl...
> Hi All..........
>
> If someone would be so kind, I am in need of a macro that will reside in a
> Master WorkBook and when run as a "before save event", would open and
allow
> update all links therein, and save, one at a time, all other .xls files in
> the same directory whose filename start with "XY-" no
> quotes............exact quantity of the files is unknown, but may be 10 to
> 100. Any other files in the directory, including the Master, not having
the
> leading "XY-" in their filename are not to be affected.
>
>
> TIA..........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>

Unbelieveably cool.............it works perfectly.........

You are a Prince among us mortals Tom..........

Thank you ever so much.......

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uIJEES0UFHA.628@tk2msftngp13.phx.gbl...
> Dim v() as String
> Dim sName as String
> Dim i as Long, j as Long
> redim v(1 to 100)
>
> sName = dir( _
> thisworkbook.Path & "\XY-*.xls")
> i = 0
> Do while sName <> ""
> i = i + 1
> v(i) = thisworkbook.Path & "\" & sname
> sName = Dir()
> Loop
> redim preserve v(1 to i)
> for j = 1 to ubound(v)
> set bk = workbooks.Open( _
> FileName:=v(j), UpdateLinks:=3)
> bk.close Savechanges:=True
> Next
>
> Untested, but this should be something like what you are looking for (as I
> understand you question and it wasn't real clear). Assumes master does
not
> start with XY-
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "CLR" <croberts@tampabay.rr.com> wrote in message
> news:uGGkUioUFHA.3572@TK2MSFTNGP12.phx.gbl...
> > Hi All..........
> >
> > If someone would be so kind, I am in need of a macro that will reside in
a
> > Master WorkBook and when run as a "before save event", would open and
> allow
> > update all links therein, and save, one at a time, all other .xls files
in
> > the same directory whose filename start with "XY-" no
> > quotes............exact quantity of the files is unknown, but may be 10
to
> > 100. Any other files in the directory, including the Master, not having
> the
> > leading "XY-" in their filename are not to be affected.
> >
> >
> > TIA..........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
>
>

Hi Chuck,

Thanks for the challenge! I started working on this the same day you posted, but I’ve never tried using VBA for updating links, so I had to do some research to see how VBA could accomplish your needs and it took me a while to develop and test my solution. I couldn’t fully resolve all of the issues I encountered (more on this later).

My original code surprisingly (based on my inexperience with this requirement) turned out to use the same Workbook.Open UpdateLinks:=3 method as Tom Ogilvy’s (Great job, Tom! Thanks for the lesson on very efficient code which I’ll attempt to incorporate in future projects; I ‘recycled’ some old code of mine to start with and spent more time on this than I expected to)…

Here’s a synopsis of my research on updating links in Excel:

You can set how Excel updates links using the Tools > Options menu, and selecting the Calculation multipage tab in the dialog…

In the upper portion of the multipage, the ‘Calculation’ settings you make are ‘global’ settings that apply to all workbooks in Excel when they are opened. That is where you set if calculations are performed automatically, ‘semi-automatically’, or manually with an additional setting if manual to ‘recalculate before save’.

In the lower portion of the multipage, the ‘Workbook options’ are ‘local’ settings that apply only to the open workbook at the time the settings are made. Here is where you set if ‘remote’ references (links to other files) are updated and/or ‘external’ link values (to other worksheets in the same file) are saved when the calculation event is triggered for the open workbook.

Additionally, in the Edit multipage is an option setting to ‘Ask to update automatic links’. If selected, Excel will prompt the user to confirm before updating the links automatically.

Other factors include the link source(s) in the file; i.e. documents, editions, DDE or OLE servers.

However, during testing I found that the “XY-…” files wouldn’t update if the ‘global’ calculation setting was set to ‘Manual’ without the additional option set to ‘Recalculate before save’. I believe that the default calculation setting when Excel is installed is set to ‘Automatic’.

So, I revised my original approach to determine the calculation settings and modify them if needed to update the linked files, and then reset them before saving the files. However, during code execution when resetting the ‘local’ settings, my testing produced mixed results. The ‘Update remote references’ setting in the “XY_…” files would remain ‘checked’ regardless if it was selected or not prior to my attempt to reset it to its’ original setting and I haven’t been able to resolve why yet…

My code may prove useful; it also includes the following features (modified legacies of ‘recycled’ code, but still prudent):

An option dialog to cancel the linked file update process, since you may not need to update the “XY-…” files every time you change/save the Master workbook.

An error handler to alert if no “XY-…” linked files are found in the same directory as the Master workbook. If linked “XY-…“ files are present, it updates the Excel status bar with progress throughout the file processing routine, i.e. – “Updating file 1 of 100 : XY-File1; please wait…”.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Declare variables
Dim oldStatusBar, newHour, newMinute, newSecond, waitTime, userAlert
Dim mastCalcSet, mastRemSet, mastExtRef, mastCalcB4
Dim remoteRefSet, externalRefSet As Boolean
Dim i, MyFilCount, fileCount As Integer
Dim Msg, Style, Title As String
Dim a(999), myLocation, LoadDir As String
    
    ' Allow user to allow save without updating linked files...
    ' Define user dialog parameters
    Msg = "All 'XY-...' linked files will be updated if you" & Chr(13) & _
        "choose to continue by selecting 'OK'." & Chr(13) & Chr(13) & _
        "Do you want to continue?"
    Style = vbOKCancel + vbExclamation + vbDefaultButton1
    Title = "Linked File(s) Update Alert"
    ' Display user dialog
    userAlert = MsgBox(Msg, Style, Title)
    If userAlert = vbCancel Then GoTo TheEnd
    
    ' Start of update routine...
    ' Turn off screen updating (program runs faster)...
    Application.ScreenUpdating = False
    ' Disable Excel alerts during processing...
    Application.DisplayAlerts = False
    
    ' Notify user of progress...
    ' Get status for status bar...
    oldStatusBar = Application.DisplayStatusBar
    ' Display the status bar...
    Application.DisplayStatusBar = True
    Application.StatusBar = "Searching for linked 'XY-...' files to update; please wait..."
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 2
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    
    'Set the filepath to the directory of the 'Master' file...
    myLocation = ActiveWorkbook.Path
    ChDir myLocation
    
    ' Initialize the array counter...
    i = 0
    ' Load array with names of "XY-...." Excel files in 'Master' file directory...
    a(i) = Dir("XY-*.xls")
    ' If no "XY-...." Excel files are present, alert user & exit program...
    If a(i) = "" Then
        GoTo FilesMissing       ' the error handler...
        Exit Sub
    Else
        ' Loop through the "XY-...." Excel files to count files as loop limit...
        Do
            i = i + 1
            a(i) = Dir()
        Loop Until a(i) = ""
        fileCount = CStr(i)
        ' Get current application calculation method settings...
        ' The 'global' setting...
        mastCalcSet = Application.Calculation
        ' The optional 'global' setting if manual...
        mastCalcB4 = Application.CalculateBeforeSave
        ' The 'local settings (remote & external references) for Master file...
        mastRemSet = ActiveWorkbook.UpdateRemoteReferences
        mastExtRef = ActiveWorkbook.SaveLinkValues
        ' If set to calculate manually, change setting to allow links to update...
        If mastCalcSet = xlCalculationManual Then
            If Application.CalculateBeforeSave = False Then Application.CalculateBeforeSave = True
        End If
        ' Start looping through the Excel file(s) to open & update each one...
        For MyFilCount = 0 To (fileCount - 1)
            LoadDir = CurDir & "\"
            ' Provide file processing status to user ...
            Application.StatusBar = _
            "Updating file " & MyFilCount + 1 & " of " & fileCount & ": " & a(MyFilCount) & "; please wait..."
            ' Open the current "XY-..." file...
            Workbooks.Open LoadDir & (a(MyFilCount)), _
                        ReadOnly:=False, IgnoreReadOnlyRecommended:=True
'           ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlLinkTypeExcelLinks
            ' Change remote links reference setting if needed...
            remoteRefSet = ActiveWorkbook.UpdateRemoteReferences
            If remoteRefSet = False Then
                ActiveWorkbook.UpdateRemoteReferences = True
            End If
            ' Change external links reference setting if needed...
            externalRefSet = ActiveWorkbook.SaveLinkValues
            If externalRefSet = False Then
                ActiveWorkbook.SaveLinkValues = True
            End If
            ' Save changes (links are now updated)...
            ActiveWorkbook.Save
            ' Reset 'local' settings for current file being processed...
            ActiveWorkbook.UpdateRemoteReferences = remoteRefSet  ' Remote references setting
            ActiveWorkbook.SaveLinkValues = externalRefSet  ' External references setting
            ' Save & close current file being processed...
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True
        ' Process next "XY-..." linked file...
        Next MyFilCount
        ' Reset screen updating and status bar...
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.StatusBar = False
        Application.DisplayStatusBar = oldStatusBar
    End If
    ' Reset application calculation method to original settings...
    Application.Calculation = mastCalcSet
    If mastCalcSet = xlCalculationManual Then Application.CalculateBeforeSave = mastCalcB4
    ' Reset the 'local' settings for Master file...
    ActiveWorkbook.UpdateRemoteReferences = mastRemSet  ' The remote references
    ActiveWorkbook.SaveLinkValues = mastExtRef  ' The external references
' Code location 'jumped' to if user chooses to cancel the update routine...
TheEnd:
    Exit Sub
'Error handler if no file(s) exist in directory of 'Master' file...
FilesMissing:
    ' Define user dialog parameters
    Msg = "There are no 'XY-...' files in the " _
        & myLocation & "directory." & Chr(13) & _
        "The program stopped and no updates were made."
    Style = vbOKOnly + vbCritical + vbDefaultButton1
    Title = "Missing File(s)"
    ' Display user dialog
    Response = MsgBox(Msg, Style, Title)
    ' Reset screen updating and status bar...
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
End Sub

Hopefully those far more knowledgeable than I can shed some light…

Thanks again!
theDude