best way to remove external links

after refreshing the external links to a text file, what's the best way to
remove the links and just save the values?

--


Gary

Take a look at findlinks...

http://www.oaltd.co.uk/MVP/

--
HTH...

Jim Thomlinson


"Gary Keramidas" wrote:

> after refreshing the external links to a text file, what's the best way to
> remove the links and just save the values?
>
> --
>
>
> Gary
>
>
>
>

doesn't find any links, this is a query to a text file that i need to delete

sorry

--


Gary


"Jim Thomlinson" <JThomlinson@belron-Re-Move-This-canada.ca> wrote in
message news:73F8D239-36E1-41B5-A6D3-090E8943DD24@microsoft.com...
> Take a look at findlinks...
>
> http://www.oaltd.co.uk/MVP/
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Gary Keramidas" wrote:
>
>> after refreshing the external links to a text file, what's the best way
>> to
>> remove the links and just save the values?
>>
>> --
>>
>>
>> Gary
>>
>>
>>
>>

That is a good one. I am not 100% sure. I am a little busy today but I will
see if I can find something...
--
HTH...

Jim Thomlinson


"Gary Keramidas" wrote:

> doesn't find any links, this is a query to a text file that i need to delete
>
> sorry
>
> --
>
>
> Gary
>
>
> "Jim Thomlinson" <JThomlinson@belron-Re-Move-This-canada.ca> wrote in
> message news:73F8D239-36E1-41B5-A6D3-090E8943DD24@microsoft.com...
> > Take a look at findlinks...
> >
> > http://www.oaltd.co.uk/MVP/
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Gary Keramidas" wrote:
> >
> >> after refreshing the external links to a text file, what's the best way
> >> to
> >> remove the links and just save the values?
> >>
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >>
> >>
>
>
>

activesheet.querytables(1).Delete

adjust to refer to the correct table.

--
Regards,
Tom Ogilvy




"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:usT4KNYBGHA.412@TK2MSFTNGP15.phx.gbl...
> after refreshing the external links to a text file, what's the best way to
> remove the links and just save the values?
>
> --
>
>
> Gary
>
>
>

Please fix your system date

--
Kind regards,

Niek Otten

"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:usT4KNYBGHA.412@TK2MSFTNGP15.phx.gbl...
> after refreshing the external links to a text file, what's the best way to
> remove the links and just save the values?
>
> --
>
>
> Gary
>
>
>

i did, that's windows vista that plays games with the date's and times

--


Gary


"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:OXdYUjZBGHA.2644@TK2MSFTNGP09.phx.gbl...
> Please fix your system date
>
> --
> Kind regards,
>
> Niek Otten
>
> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
> news:usT4KNYBGHA.412@TK2MSFTNGP15.phx.gbl...
>> after refreshing the external links to a text file, what's the best way
>> to remove the links and just save the values?
>>
>> --
>>
>>
>> Gary
>>
>>
>>
>
>

Promising! :-)

"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:ubJP3DaBGHA.1864@TK2MSFTNGP12.phx.gbl...
>i did, that's windows vista that plays games with the date's and times
>
> --
>
>
> Gary
>
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:OXdYUjZBGHA.2644@TK2MSFTNGP09.phx.gbl...
>> Please fix your system date
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
>> news:usT4KNYBGHA.412@TK2MSFTNGP15.phx.gbl...
>>> after refreshing the external links to a text file, what's the best way
>>> to remove the links and just save the values?
>>>
>>> --
>>>
>>>
>>> Gary
>>>
>>>
>>>
>>
>>
>
>

Are you sure your time zone is correct?

Gary Keramidas wrote:
>
> i did, that's windows vista that plays games with the date's and times
>
> --
>
> Gary
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:OXdYUjZBGHA.2644@TK2MSFTNGP09.phx.gbl...
> > Please fix your system date
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> > "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
> > news:usT4KNYBGHA.412@TK2MSFTNGP15.phx.gbl...
> >> after refreshing the external links to a text file, what's the best way
> >> to remove the links and just save the values?
> >>
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >>
> >
> >

--

Dave Peterson

Gary:

I currently use code that loops through the entire workbook and deletes
the external data source definitions, yet leaves the data it grabbed
like this:

Sub FreezeData()
Dim MyQT, qt As QueryTable
Dim i As Integer
For Each Sheet In Sheets
i = Sheet.Index
For Each qt In Worksheets(i).QueryTables
Set MyQT = qt
MyQT.Delete
Next
Next
End Sub

HTH,
Jana

thanks, jana

--


Gary


"Jana" <Bauer.Jana@gmail.com> wrote in message
news:1135113041.885678.53180@g44g2000cwa.googlegroups.com...
> Gary:
>
> I currently use code that loops through the entire workbook and deletes
> the external data source definitions, yet leaves the data it grabbed
> like this:
>
> Sub FreezeData()
> Dim MyQT, qt As QueryTable
> Dim i As Integer
> For Each Sheet In Sheets
> i = Sheet.Index
> For Each qt In Worksheets(i).QueryTables
> Set MyQT = qt
> MyQT.Delete
> Next
> Next
> End Sub
>
> HTH,
> Jana
>

deleting my original post to get it off of the top


--


Gary


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:usT4KNYBGHA.412@TK2MSFTNGP15.phx.gbl...
> after refreshing the external links to a text file, what's the best way to
> remove the links and just save the values?
>
> --
>
>
> Gary
>
>
>

You're quite welcome, Gary. Let me know if that works for you.

Jana

jana's code is error prone...

To delete members of a collection:
NEVER iterate a collection by object
ALWAYS enumerate by numerical index from Highest to Lowest
THEN delete.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Gary Keramidas wrote :

> thanks, jana

Thanks,
I never knew that could cause a problem...I am still pretty new at
coding in excel. How would you recommend the code be altered?
Jana

Sub FreezeData()
Dim MyQT As QueryTable
Dim i As Long
Dim j As Long
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


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jana" <Bauer.Jana@gmail.com> wrote in message
news:1135115752.364613.14930@z14g2000cwz.googlegroups.com...
> Thanks,
> I never knew that could cause a problem...I am still pretty new at
> coding in excel. How would you recommend the code be altered?
> Jana
>

RP:
Thanks very much! I will implement the changes you've made.

Appreciate the clarification

Jana

Hi

It is an ODBC query, is it?
Insert>Name>Delete - and delete named range(s) which determine(s) returned
data range(s)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:usT4KNYBGHA.412@TK2MSFTNGP15.phx.gbl...
> after refreshing the external links to a text file, what's the best way to
> remove the links and just save the values?
>
> --
>
>
> Gary
>
>
>

Sorry!
Insert>Name>Define


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )