VBA - Freeze data in external data range

[Paul Kraemer]

Hi,

I have an Excel 2003 workbook in which I use MS Query to get data from a SQL
Server database. The query returns records to an External Data Range defined
on one of my worksheets. This works great as long as the PC I open the
workbook on is able to connect to the SQL Server.

At times, however, I want to generate a copy of this workbook that does not
require a connection to the SQL server. To do this, I right-click in the
External Data Range and choose Properties. In the External Data Range
Properties dialog box, I clear the checkbox for "Save Query Definition".
This does just what I want - it removes the underlying query but retains the
data. I save the workbook to a different filename, email it to whoever I
want, and they can view the data without a connection to the SQL server.

I would like to be able to automate this. Can I use VBA to accomplish the
same thing as when I clear the "Save Query Definition" checkbox? If anyone
could tell me how to do this or where I can find instructions, I would
appreciate it. I can't seem to find it in online help.

Thanks,
paul

 

[dan dungan]

Hi Paul,

I found these two suggestions by searching this group for "Save Query
Definition":

This one from Tom Ogilvy:
Newsgroups: microsoft.public.excel.programming
From: "Tom Ogilvy" <[email protected]>
Date: Fri, 11 Jun 2004 09:47:45 -0400
Local: Fri, Jun 11 2004 5:47 am
Subject: Re: how do you break the link with MS Query - Database via
code

Activesheet.QueryTables(1).Delete

will delete the querytable definition, but leave the data. It seems
that is what you want.

--
Regards,
Tom Ogilvy


And very similar from Rob van Gelder:


This deletes all querytables from ActiveSheet

Dim qtb As QueryTable

For Each qtb In ActiveSheet.QueryTables
qtb.Delete
Next

This approach will leave behind the named range for the querytable.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

HTH

Dan

 

[Paul Kraemer]

Hi Dan, that worked great. Thanks for the help!

 

[dan dungan]

You're welcome, Paul.