How to remove external data links via Macro 2

[cashback]

I need to be able to remove external data links from sheets that are generated via a macro. The resulting sheets need to retain the data but no links.
Copy / Paste / Values does not work and I cannot record the steps into a macro when I tried :
Select External Data Range Properties then removed the tick from the Save Query Definition box.

Just need the code to remove the range without deleting the contents of the cells..

 

[dhulbert]

What form are these links in, are they just cell refernces ot other workbooks, pivot table, msquery results etc etc.

 

[xlbo]

Sheets("Sheetname").Querytables("QueryTableName").delete

tip - try looking at the object model for the object you are trying to manipulate - this can be brought up by pressing F2 whilst in the VBE - you can then search it and see all properties and methods associated with an object

A querytable has a "delete" method so you can logically assume that the way to get rid of one is to delete it !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

or

for each ws in worksheets
  for each qt in ws.querytables
    qt.delete
  next
next

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[dhulbert]

Guys.

The code you're posting till delete the querytables but what cashback seems to want to do is to delete the links behind the tables and leave the data.
If you have a pivot table you can do Copy-PasteValues and the pivot data is left althought the ability to update and change the table is gone.

think thats the sort of thing that is required.

 

[SkipVought]

The code for deleting querytable links does not delete the data.

???

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[xlbo]

What Skip said !!

Querytables(1).delete

deletes the links only - not the data

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AppStaff]

Cool skip thanks. Been trying to figure out how to do this also. Is there a way to delete any macros or vba from a worksheet so the user is not bothered with those warnings either?

Here's what I'd like to do.

1. Open template with queries and macros.
2. Load data from queries and reformat with macros/vba
3. Delete macro's and links so that its a static report with no warning messages.
4. Save new file.

Star awarded for prior post

 

[cashback]

Thanks to all that replied.

I used the code from Skip and it works perfectly. No external links remain on the sheet. I now have a spreadsheet for vendor schedules which takes data from my AS400 sorts it by supplier number creates a seperate spreadsheet for each and automatically emails the sheet to the supplier. All of this came from various bits of code found here at tek tips..

Thanks again...