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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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…”.

Please Login or Register  to view this content.

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

Thanks again!
theDude