Don't Update Links

[Paul Watkins]

I have a workbook (Book1.xls) which has links in it and also has a macro
that saves an array of sheets from the original under a different name
(date.xls)

Example:
<<strFileName = Worksheets("transfer").Range("AA1").Text
ThisWorkbook.Worksheets(Array("sheet1", "sheet2", "sheet3",
"sheet7")).Copy
ActiveWorkbook.SaveAs Filename:="C:\TEMP\ " & strFileName & ".xls">>

(the strfileName is a date)

This code copies the sheets but none of the macros / VB Code
The problem i have is that when i open the book (date.xls) it either asks to
update links or updates regardless (if the setting is enabled under options)

Is there any way to adapt/add to the above code to add an entry to the VB
Code 'ThisWorkbook' (When the book opens) to disable the automatic updating
of links

I believe the line i need to add is something like:-

<<updateLinks = False>>

Any Help would be appreciated

Paul

 

[Paul Watkins]

Thanks for the reply, but the 1st book i have has links in it and they are
forced to update when i open it, the second book needs to have vb code under
it to force links not to update.
Is it possible to create a macro in a 'fresh' workbook from the another open
workbook?

 

[Dave Peterson]

If you open the workbook in code, you can tell excel not to update the links.

Take a look in VBA's help for workbooks.open.

You'll see an updatelinks parm that has a few options.

 

[John Thow]

I have a workbook (Book1.xls) which has links in it and also has a macro
that saves an array of sheets from the original under a different name
(date.xls)

Example:
<<strFileName = Worksheets("transfer").Range("AA1").Text
ThisWorkbook.Worksheets(Array("sheet1", "sheet2", "sheet3",
"sheet7")).Copy
ActiveWorkbook.SaveAs Filename:="C:\TEMP\ " & strFileName & ".xls">>

(the strfileName is a date)

This code copies the sheets but none of the macros / VB Code
The problem i have is that when i open the book (date.xls) it either asks to
update links or updates regardless (if the setting is enabled under options)

Is there any way to adapt/add to the above code to add an entry to the VB
Code 'ThisWorkbook' (When the book opens) to disable the automatic updating
of links

I believe the line i need to add is something like:-

<<updateLinks = False>>

Any Help would be appreciated

Paul

Paul,

You could try removing the links before you close the new workbook as in:-

' Reset Cells To Remove References To Book1
Workbooks("Date.xls").Worksheets(<Sheet Number>).Activate
Cells.Replace What:="[Book1.xls]", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I have a similar process and this works for me. (Excel 97)

[For some reason I can't quite work out, this works in 1 situation and not in
another - where the replace didn't find the reference even though I could see
it!!. There I used :-

Workbooks("Date.xls").Worksheets(<Sheet Number>).Activate
ActiveWorkbook.ChangeLink Name:="Book1.xls", NewName:= _
"Date.xls", Type:=xlExcelLinks

which might be worth a try if the first example doesn't do it for you...]

HTH


--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Paul Watkins]

Thanks for all the replies.
I tried a few including the one below which as you said doesn't seem to work
in every instance.
i'll try a few other options and see what i come up with

Thanks again

Paul

I have a workbook (Book1.xls) which has links in it and also has a macro
that saves an array of sheets from the original under a different name
(date.xls)

Example:
<<strFileName = Worksheets("transfer").Range("AA1").Text
ThisWorkbook.Worksheets(Array("sheet1", "sheet2", "sheet3",
"sheet7")).Copy
ActiveWorkbook.SaveAs Filename:="C:\TEMP\ " & strFileName & ".xls">>

(the strfileName is a date)

This code copies the sheets but none of the macros / VB Code
The problem i have is that when i open the book (date.xls) it either asks to
update links or updates regardless (if the setting is enabled under options)

Is there any way to adapt/add to the above code to add an entry to the VB
Code 'ThisWorkbook' (When the book opens) to disable the automatic updating
of links

I believe the line i need to add is something like:-

<<updateLinks = False>>

Any Help would be appreciated

Paul

Paul,

You could try removing the links before you close the new workbook as in:-

' Reset Cells To Remove References To Book1
Workbooks("Date.xls").Worksheets(<Sheet Number>).Activate
Cells.Replace What:="[Book1.xls]", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I have a similar process and this works for me. (Excel 97)

[For some reason I can't quite work out, this works in 1 situation and not in
another - where the replace didn't find the reference even though I could see
it!!. There I used :-

Workbooks("Date.xls").Worksheets(<Sheet Number>).Activate
ActiveWorkbook.ChangeLink Name:="Book1.xls", NewName:= _
"Date.xls", Type:=xlExcelLinks

which might be worth a try if the first example doesn't do it for you...]

HTH


--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[John Thow]

Thanks for all the replies.
I tried a few including the one below which as you said doesn't seem to work
in every instance.
i'll try a few other options and see what i come up with

Thanks again

Paul

[Snip old stuff]

Paul,

With some help from Dave Peterson (I was a tad tired yesterday and couldn't
see something that should of been obvious...) I've come up with another
approach. Try adding something like this to the code in 'Book1.xls':-

Sub RemoveLinks()

'Note that links to other sheets in the same workbook are preseved by this
'procedure. Therefore, the new workbook must contain worksheets with the
'same names as those referenced in the old one, otherwise you'll get an
' "invalid ' 'reference" error! If you remember that, it will change all the
'links in the 'new workbook to refer to itself.

Dim Indeks As Integer
Dim Linx As Variant
Dim NewNameX As String
Dim OldNameX As String

NewNameX = "Date.xls"
OldNameX = "Book1.xls"

Workbooks(NewNameX).Activate
Linx = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(Linx) Then 'Test for the existence of links
For Indeks = 1 To UBound(Linx)
ActiveWorkbook.ChangeLink Name:= _
Linx(Indeks), NewName:=NewNameX, Type:=xlExcelLinks
Next Indeks
End If

End Sub

Maybe that'll do it...

Happy Yule!


--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!