Removing Access External Data Queries from Excel
 

oops, forgot something
for each n in sheets("sheet1").NAMES
n.delete
next


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
With external queries to yahoo finance, etc I delete the names in
insertnamedefine.

for each n in sheets("sheet1")
n.delete
next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michael Kintner" wrote in message
...
Hello Folks,

I have a Excel Spreadsheet with Microsoft Access external data query
links. I would like to create a snapshot in time of the data in the Excel
Spreadsheet. I do this now by manually going to each external data query
cell (there are 8 of them), right click the cell, select Data Range
Properties, then uncheck the Save Query Definition, it asks me to confirm
and press OK. Now the external link is disconnected saving the last data
query value in the cell.

The interesting thing here is when I try to record via the excel macro
recorder nothing shows up in the macro recorder vb code.

Doesn anybody have a method to turn off all external data links via
programming?

Thank you in advance,
Mike

Removing Access External Data Queries from Excel
 

That worked great!! Thank you!!!

"Don Guillett" wrote in message
...
oops, forgot something
for each n in sheets("sheet1").NAMES
n.delete
next


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
With external queries to yahoo finance, etc I delete the names in
insertnamedefine.

for each n in sheets("sheet1")
n.delete
next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michael Kintner" wrote in message
...
Hello Folks,

I have a Excel Spreadsheet with Microsoft Access external data query
links. I would like to create a snapshot in time of the data in the
Excel Spreadsheet. I do this now by manually going to each external
data query cell (there are 8 of them), right click the cell, select Data
Range Properties, then uncheck the Save Query Definition, it asks me to
confirm and press OK. Now the external link is disconnected saving the
last data query value in the cell.

The interesting thing here is when I try to record via the excel macro
recorder nothing shows up in the macro recorder vb code.

Doesn anybody have a method to turn off all external data links via
programming?

Thank you in advance,
Mike

Removing Access External Data Queries from Excel
 

Well may it do not work so great!!!!!

I get a runtime error when I use the Delete as shown below. The interesting
part is if I do this manually it works but when inside the code I get a
runtime 1004 error.

Sub Delete_Data_Links()
Sheets("IMS-Begin").Select
ActiveWorkbook.Names("Query_from_MS_Access_Databas e").Delete
ActiveWorkbook.Names("Query_from_MS_Access_Databas e_1").Delete
ActiveWorkbook.Names("Query_from_MS_Access_Databas e_2").Delete
ActiveWorkbook.Names("Query_from_MS_Access_Databas e_3").Delete
Sheets("IMS-Data").Select
ActiveWorkbook.Names("Query_from_IMS_ACCESS_2").De lete
ActiveWorkbook.Names("Query_from_IMS_ACCESS_3").De lete
Sheets("Txn-Totals").Select
ActiveWorkbook.Names("Query_from_IMS_Access").Dele te
ActiveWorkbook.Names("Query_from_IMSPROD").Delete
End Sub




"Don Guillett" wrote in message
...
oops, forgot something
for each n in sheets("sheet1").NAMES
n.delete
next


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
With external queries to yahoo finance, etc I delete the names in
insertnamedefine.

for each n in sheets("sheet1")
n.delete
next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michael Kintner" wrote in message
...
Hello Folks,

I have a Excel Spreadsheet with Microsoft Access external data query
links. I would like to create a snapshot in time of the data in the
Excel Spreadsheet. I do this now by manually going to each external
data query cell (there are 8 of them), right click the cell, select Data
Range Properties, then uncheck the Save Query Definition, it asks me to
confirm and press OK. Now the external link is disconnected saving the
last data query value in the cell.

The interesting thing here is when I try to record via the excel macro
recorder nothing shows up in the macro recorder vb code.

Doesn anybody have a method to turn off all external data links via
programming?

Thank you in advance,
Mike