Removing Links from Excel Spreadsheet

[nikkiswin]

Ok all you experts out there - I need help!!
I have a workbook saved as a template (.xlt) file which has been linked to another .xls file using the Template Wizard and Data Tracking.
I want to remove the link and stop using the Data Tracking but can't find out how to do this.
I've already downloaded a nifty add-in which automatically searches the file for broken links but this isn't finding the data tracking links. I've also tried saving the template under another filename and as an xls file but nothing seems to get rid of the data tracking.
WHAT CAN I DO NOW????

Nikki

 

[KenWright]

Which addin have you already used. Bill Manville has a superb utility called FINDLINK.xla that will find just about anything that is link related, and then gives you options on how to deal with them. It can be downloaded from the following link:-

http://www.bmsltd.co.uk/MVP/Default.htm

Regards
Ken............

 

[Gavona]

Never used this addin before...

Have you realised that there is a 'very hidden' worksheet and a named range?
Deleting these stops the data tracking but when you open the sheet there is still an 'enable macros' message. I can't find the macro though.

 

[Gavona]

Actually the easiest way to sort your problem is to copy the worksheet to a new workbook.

I'd still like to know how to find that macro though!

 

[KenWright]

Have you deleted the modules as well, as any module whtheer empty or not will trigger the message.

Failing that, the following is from Chip Pearson's website, and will delete all VBA in a projects. NOTE THE WARNING in there as it will permanently delete the code.

http://www.cpearson.com/excel/vbe.htm

Deleting All VBA Code In A Project

The procedure below will delete all the VBA code in a project. You should use this procedure with care, as it will permanently delete the code. Standard modules, user forms, and class modules will be removed, and code within the ThisWorkbook module and the sheet modules will be deleted. You may want to export the VBA code, using the procedure above, before deleting the VBA code.

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub


Regards
Ken.................

 

[nikkiswin]

Hi Ken, I have been using findlink.xla!!! but thanks for the tip, I can't delete the Modules or the VBA as I am using both in my workbook....

Hi Gavona, How do I find this very hidden worksheet? I can find the named ranges OK but there still seems to be this worksheet hidden somewhere because my add-in keeps finding it!!

Thanks to both of you...

 

[Gavona]

Open visual basic editor,
In View menu enable project explorer and properties windows
Select the sheet in Project Explorer
Properties window shows Visible property as 2-XL sheet very hidden. You can change this using dropdown.

Once the shhet is visible you can return to excel and delete it (I didn't have much joy exploring it)

However I still say, copy the worksheet to a new book. But now I would add, export/import or copy your code to the new book. I feel sure that will sort your problem but I agree that it may feel like an admission of defeat!