VBA - Freeze data in external data range

P

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

External data and password 2
Open a word mail-merge document from Excel VBA 1
Copy value from changing cells without VBA 1
delete external data query 1
query to external data 3
excel external data range "failed to refresh" 0
Location of SQL in an external data connection 1
manipulating Query data 'on the fly'Question 2

Top