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!

 

[CBasicAsslember]

Post some code.

 

[Larft]

Here is a sample of one of macros I set up, it works fine using the workbook on it's own, but if I convert it to a Template and then open a new work book with it, it does not:

[Sub CopyData1_2()
'
' CopyData1_2 Macro
' Macro recorded 11/10/2006 by LA
'

'
    Sheets(Array("Entry", "Calculated Values")).Select
    Sheets("Entry").Activate
    Sheets(Array("Entry", "Calculated Values")).Copy
    Cells.Select
    Selection.Interior.ColorIndex = xlNone
    ActiveSheet.Shapes("Button 5").Select
    Selection.Cut
    ActiveSheet.Shapes("Button 1").Select
    Selection.Cut
    ActiveSheet.Shapes("Button 2").Select
    Selection.Cut
    ActiveSheet.Shapes("Button 3").Select
    Selection.Cut
    ActiveSheet.Shapes("Button 4").Select
    Selection.Cut
    ActiveSheet.Shapes("Button 6").Select
    Selection.Cut
    ActiveSheet.Shapes("Button 7").Select
    Selection.Cut
    Range("A1").Select
    ActiveWorkbook.BreakLink Name:="\\tryn01\common\APC\FormulaCalc\FormulaCalcTool.xls", Type:=xlExcelLinks
    Windows("FormulaCalcTool.xls").Activate
    Sheets("Entry").Select
    End Sub

I'm sure this is due to the fact that the workbook is now open only in the application and not from a network source.

Thanks for your help!

 

[CBasicAsslember]

I should have been more specific. I need to see what you do when you open up the workbook.

 

[Larft]

Not sure what you mean, a startup sequence? I don't have a customized startup or are you referring to what I do with the spreadsheet? If you mean opening the template, it's saved to the network in a shared template resource, I then open it using "File/New" etc.

Or are you referring to something else?

Thanks again for your help.

 

[CBasicAsslember]

I am interested in the macros you run first. I would assume that you are creating a new workbook initially. If you create a new workbook based on a template the name is not the same as the template name. For example, if I had a template named 'MyExcelData' and created a workbook it would have the name ''MyExcelData1'. I suspect you are using the template name to refer to the new workbook.

 

[Larft]

You are correct about the name, the new workbook carries the template name with a number suffix. I'm sure this is part of the problem plus the fact that the originating template is not open, only the workbook created from it, I don't know how to reference this kind of situation in the macro code, I have only a very limited knowledge of VBA and am creating the macro by recoding keystrokes.

Thanks again in advance for your help!

 

[CBasicAsslember]

This should help you a bit. To get the name of the workbook the code is in:

Sub test()
Dim sTemp As String

    sTemp = ThisWorkbook.Name

End Sub

Try stepping through the code using F8 so you can see what is happening. Turn on the "Locals Window" (under the view menu in the VBA editor) so you can see how variables are changed.

A template is just that, a template. When you create a workbook with a template it just makes a copy of the template. Off hand I can't think of anything other then the name of the workbook that would be different. I think once you fix the name references the workbook should work.

 

[Larft]

Thanks for the reply, the holiday has kept me from replying sooner.

This still doesn't help me though, at least I don't see how. I have stepped through the macro code, it continually fails at this point:

 ActiveWorkbook.BreakLink Name:= _
        "N:\Template\EasyTone\FormulaCalcTool.xlt", Type:=xlExcelLinks

It's as if it just can't find the link to the originating template file. I can go to "Edit/Link/Break Links" in the menu in the copied workbook and break it manually but the code that used to work when I open the original pre template workbook no longer works.

Any help from here would be greatly appreciated.

Thanks in advance!

 

[SkipVought]

Hi,

The NAME in the BreakLinks method, is a LINK NAME -- not a workbook name.

Try this

Sub UseBreakLink()

    Dim astrLinks As Variant, i as integer

    ' Define variable as an Excel link type.
    astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

for i = 0 to ubound(astrLinks)
    ' Break each link in the active workbook.
    ActiveWorkbook.BreakLink _
        Name:=astrLinks(i), _
        Type:=xlLinkTypeExcelLinks
next
End Sub

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

 

[Larft]

Hello, Thanks for you earlier suggestion

I tried the code out, pasted it into the code for the sheet and ran it from the macro pulldown and it still gives me an error. Maybe what I'm trying to do wasn't very clear. After creating a new spreadsheet from the template and editing data etc., I run the macro which does several things: Copies the first two sheets in the workbook to a new book, unprotects them, deletes several control buttons and removes some cell formatting, then breaks the link. I'm doing all of this so that only the calculated results of the data are saved, which pares it down to about a 70k file, otherwise the entire thing is about 2.6 megs including lookup tables and calculations. I have created the macros by recording keystrokes and everything works properly if I run the macro from the original workbook, but once I save it as a template and then open it from this location as a new workbook the portion of the macro that breaks the link fails when I run it.

Hopefully this will shed more light on my problem.

Thanks in advance for your help!

 

[SkipVought]

"...as a new workbook ..."

the code is for the ActiveWorkbook. If you are referencing another workbook, then use THAT REFERENCE, rather than the ActiveWorkbook.

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

 

[CBasicAsslember]

Larft,

The macro recorder is a great way to learn VBA. The code it generates is not very efficient and, as you have found out, can create some problems. I took your code and cleaned it up a bit. You will notice that the amount of code needed is a lot less then the code the macro recorder generated.

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
    
' Get the names for both workbooks
    sNewBook = ActiveWorkbook.Name ' New workbook created from copy
    sTemplate = ThisWorkbook.Name ' Workbook created from template
    
' Remove the buttons from the new workbook
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
End With

You will notice I used 2 variables to get the different workbook names. You should also notice that I didn't do the BreakLink code. I think it will be useful, as an exercise, for you to add the BreakLink based on the code that SkipVought generously provided.

 

[Larft]

Skip and CBasicAssembler,

Thanks again for your help on this.

I've worked out both parts of the code both of you supplied and have it working up to the BreakLink point, which gives me: "'Run-time error 9':SubScript out of range" , my ignorance on the subject is showing at this point. The Debug error points to this part of the code:

 ActiveWorkbook.BreakLink _
        Name:=astrLinks(i), _
        Type:=xlLinkTypeExcelLinks

I'm sure I've missed something in defining the "astrLinks" portion, or am I off here too?

Here is the full code at this point, you'll notice I've added back some of the parts I had previously (probably not optimum code usage)

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

' Get the names for both workbooks
    sNewBook = ActiveWorkbook.Name ' New workbook created from copy
    sTemplate = ThisWorkbook.Name ' Workbook created from template
    
' Remove the buttons from the new workbook
     With ActiveSheet
    .Unprotect
    .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
    
    Dim astrLinks As Variant, i As Integer

    ' Define variable as an Excel link type.
    astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

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

End With
End Sub

Thanks in advance once again for sharing your expertise.

 

[SkipVought]

AGAIN....

What Workbook???

If not the ActiveWorkbook

is it [red]sNewBook[/red] or [red]sTemplate[/red] ???

Workbooks([red][b]??????[/b][/red]).BreakLink _
        Name:=astrLinks(i), _
        Type:=xlLinkTypeExcelLinks

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

 

[CBasicAsslember]

I think you have to test astrLinks to make sure it doesn't return 'empty'.

If Not IsEmpty(astrLinks) Then

   For i = 0 To UBound(astrLinks)
       'code
   Next

End If

It would also be a good idea to reference the Workbooks(????) you want rather then hope and pray the active workbook is the right one.

 

[Larft]

Skip,

It would be the link between the active workbook, which is "SNewBook" and the original workbook "sTemplate" that I want to break the link to. I tried your suggestion but I just don't know enough about the syntax to get it right, I still get the same error in that section of the code. I tried replacing the ?????? with SNewBook etc to the same result.

Any further help you can offer would be greatly appreciated.

Thanks in advance!

 

[SkipVought]

Did you do as CBasic suggested - check the array?

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

 

[CBasicAsslember]

Don't put quotes around sNewBook. It should be:

Workbooks(sNewBook)

 

[Larft]

Skip and CBasicAssembler,

Sorry to be so dense on this topic but VB is very new to me.

Didn't use the quotes, it was just part of my message. I added the suggested test and astrLinks is coming up Empty in the Locals window initially, after completing the IfEmpty test, expression "i" value is 1, then is equal to 2 when the BreakLink portion of the code comes up, I then still get the out of range result. I'm out of range with a result of 2 and it's looking for 0?

Here's where I'm at with the code:

 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

' Get the names for both workbooks
    sNewBook = ActiveWorkbook.Name ' New workbook created from copy
    sTemplate = ThisWorkbook.Name ' Workbook created from template
    
' Remove the buttons from the new workbook
     With ActiveSheet
    .Unprotect
    .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
    
    Dim astrLinks As Variant, i As Integer

    ' Define variable as an Excel link type.
    astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If Not IsEmpty(astrLinks) Then

    For i = 0 To UBound(astrLinks)
    
    Next

End If

    ' Break each link in the active workbook.
    Workbooks(sNewBook).BreakLink _
        Name:=astrLinks(i), _
        Type:=xlLinkTypeExcelLinks
    
End With
End Sub

Once again, your help is much appreciated........