Excel:Template with links, automate break links 3

[Larft]

I have an Excel workbook that I have automated with macros to copy two of the sheets out to a new workbook and then break the link and remove some formatting to retain just the data as a copy of the results in the parent workbook. All was fine until I set it up as a template on the network (which is necessary to share it simultaneously). When the macro runs I get errors for just about everything. I think this is all due to the state things are in at the moment, template opens as new workbook and any references to the original on the network are no longer valid.

Any help anyone has will be greatly appreciated.

Thanks in advance!

 

[SkipVought]

...
    For i = 0 To UBound(astrLinks)
    ' Break each link in the active workbook.
    Workbooks(sNewBook).BreakLink _
        Name:=astrLinks(i), _
        Type:=xlLinkTypeExcelLinks
  
    Next

End If

Skip,
[sub]
[red][/red]
[/sub]

 

[Larft]

Skip,

Expression "i" now resolves to "0" but I still get the out of range error. I don't have enough hair left to rip out I'm afraid, hopefully you guys won't boot me out of here any time soon.

Thanks again.

 

[PHV]

And what about this ?

Dim astrLinks As Variant, i As Integer
' Define variable as an Excel link type.
astrLinks = Workbooks(sNewBook).LinkSources(Type:=xlLinkTypeExcelLinks)
For i = UBound(astrLinks) To 1 Step -1
  ' Break each link in the new workbook.
  Workbooks(sNewBook).BreakLink _
      Name:=astrLinks(i), _
      Type:=xlLinkTypeExcelLinks
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Larft]

Thanks to everyone for putting up with my lack of depth on this subject, I finally got it to work with a combination of several of the posted code samples. Here is the final code that did everything that I needed to accomplish:

 Sub CopyData1_2()

Dim sTemplate As String
Dim sNewBook As String

' Copies the first two sheets in the workbook to a new workbook
    Sheets(Array("Entry", "Calculated Values")).Copy

'Remove the buttons and formatting from the new workbook
     Sheets("Entry").Unprotect
     With ActiveSheet
    .Shapes("Button 1").Delete
    .Shapes("Button 2").Delete
    .Shapes("Button 3").Delete
    .Shapes("Button 4").Delete
    .Shapes("Button 5").Delete
    .Shapes("Button 6").Delete
    .Shapes("Button 7").Delete
     Cells.Select
     Selection.Interior.ColorIndex = xlNone
     End With
     Sheets("Calculated Values").Select
     With ActiveSheet
    .Unprotect
    .Shapes("Button 1").Delete
    .Shapes("Button 2").Delete
     Sheets("Entry").Select
     Range("A1").Select

' Get the names for both workbooks
    sNewBook = ActiveWorkbook.Name ' New workbook created from copy
    sTemplate = ThisWorkbook.Name ' Workbook created from template
    
    Dim astrLinks As Variant, i As Integer
    ' Define variable as an Excel link type.
    astrLinks = Workbooks(sNewBook).LinkSources(Type:=xlLinkTypeExcelLinks)
    For i = UBound(astrLinks) To 1 Step -1
    ' Break each link in the new workbook.
      Workbooks(sNewBook).BreakLink _
      Name:=astrLinks(i), _
      Type:=xlLinkTypeExcelLinks
Next

End With

End Sub

It could probably be streamlined further but it works.

Thanks for everyone's patience and willingness to help out a newbie, I gave a star to everyone for helping out.

 

[CBasicAsslember]

Just a few suggestions:
1. Put all your declarations at the top of the code. For example:

Dim astrLinks As Variant, i As Integer

could be moved to the top.

2. Change:

     Cells.Select
     Selection.Interior.ColorIndex = xlNone

To:

Cells.Interior.ColorIndex = xlNone

You don't have to select the cells make the change.

3. Document the code with comments so when you go back in 6 months you know what the code is doing.

 

[Larft]

CBasicAssembler,

Thanks again, I incorporated your revisions.