Breaking the External Data Link

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

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

--


Gary


"Carl" <[email protected]> wrote in message
news:iUUzf.9837$jR.9464@fed1read01...
>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
>

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

"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:[email protected]...
> 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
>
> --
>
>
> Gary
>
>
> "Carl" <[email protected]> wrote in message
> news:iUUzf.9837$jR.9464@fed1read01...
>>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
>>
>
>

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

--


Gary


"Carl" <[email protected]> wrote in message
news:U6jBf.11225$jR.6655@fed1read01...
> That was perfect! Thank you so much. I implemented on friday but could not see
> the final tested results until today.
> Carl
>
> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
> news:[email protected]...
>> 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
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Carl" <[email protected]> wrote in message
>> news:iUUzf.9837$jR.9464@fed1read01...
>>>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
>>>
>>
>>
>
>