Breaking the External Data Link

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carl - 19 Jan 2006 23:03 GMT

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

Reply to this Message

Gary Keramidas - 19 Jan 2006 23:43 GMT

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

Signature

Gary

>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
[quoted text clipped - 5 lines]
> TIA
> Candyman

Reply to this Message

Carl - 24 Jan 2006 05:43 GMT

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

> would this work for you
>
[quoted text clipped - 23 lines]
>> TIA
>> Candyman

Reply to this Message

Gary Keramidas - 24 Jan 2006 05:44 GMT

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

Signature

Gary

> That was perfect! Thank you so much. I implemented on friday but could not see
> the final tested results until today.
[quoted text clipped - 26 lines]
>>> TIA
>>> Candyman

Reply to this Message