Breaking the External Data Link

[Carl]

I create reports using an SQL to external data. The report then copies
particular data to division tabs and saves as a separate file. The trouble
is when we open the new spreadsheet there is a link to that External data
and if someone tries to refresh the data, they see my datasource and login.
How do I get rid of the Link to the external data source on the copied
pages?

TIA
Candyman

 

[Gary Keramidas]

would this work for you

sub remove()
Dim MyQT As QueryTable
Dim i As Long
Dim j As Long
Dim sheet As Worksheet

For Each sheet In Sheets
i = sheet.Index
For j = Worksheets(i).QueryTables.Count To 1 Step -1
Set MyQT = Worksheets(i).QueryTables(j)
MyQT.Delete
Next
Next
end sub

 

[Carl]

That was perfect! Thank you so much. I implemented on friday but could not
see the final tested results until today.
Carl

 

[Gary Keramidas]

you're welcome, i think bob p or tom o gave me something similar a while back,
so thanks goes to them, too.