Break a link to a data source
 

I have a template that imports external data (OBDC) and/refreshes upon
startup. Once opened, the user saves the file as a differnent name (.xlt to
..xls). When the user opens up the .xls, it automatically refreshes the data
(problem if a new query was request from our MRP system).

Is there a way with VBA to break the data link once the file is saved as an
..xls?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Need code here

End Sub

Break a link to a data source
 

asmenut

Me.Sheets(1).QueryTables(1).Delete

Change the 1's to fit your situation. This will delete the query table, but
the data will remain. See also

http://www.dicks-clicks.com/excel/Ex....htm#DeleteVBA

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"asmenut" wrote in message
...
I have a template that imports external data (OBDC) and/refreshes upon
startup. Once opened, the user saves the file as a differnent name (.xlt
to
.xls). When the user opens up the .xls, it automatically refreshes the
data
(problem if a new query was request from our MRP system).

Is there a way with VBA to break the data link once the file is saved as
an
.xls?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Need code here

End Sub

Break a link to a data source
 

Thanks. Had to createa a macro though. If I tried to save the template
(.xlt) with that code in the SaveBefore routine, it killed the link in the
template.

"Dick Kusleika" wrote:

asmenut

Me.Sheets(1).QueryTables(1).Delete

Change the 1's to fit your situation. This will delete the query table, but
the data will remain. See also

http://www.dicks-clicks.com/excel/Ex....htm#DeleteVBA

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"asmenut" wrote in message
...
I have a template that imports external data (OBDC) and/refreshes upon
startup. Once opened, the user saves the file as a differnent name (.xlt
to
.xls). When the user opens up the .xls, it automatically refreshes the
data
(problem if a new query was request from our MRP system).

Is there a way with VBA to break the data link once the file is saved as
an
.xls?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Need code here

End Sub

Break a link to a data source
 

asmenut

Yeah, that's a problem. You could use OnTime to call a macro that deletes
the query like

Application.OnTime Now+TimeSerial(0,0,0), "DelQuery"

That would run after the BeforeSave is done but it presents another problem.
If the user saves it during the close process (user closes the unsaved
template and chooses yes when prompted to save), the workbook would re-open
to run that macro.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"asmenut" wrote in message
...
Thanks. Had to createa a macro though. If I tried to save the template
(.xlt) with that code in the SaveBefore routine, it killed the link in the
template.

"Dick Kusleika" wrote:

asmenut

Me.Sheets(1).QueryTables(1).Delete

Change the 1's to fit your situation. This will delete the query table,
but
the data will remain. See also

http://www.dicks-clicks.com/excel/Ex....htm#DeleteVBA

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"asmenut" wrote in message
...
I have a template that imports external data (OBDC) and/refreshes upon
startup. Once opened, the user saves the file as a differnent name
(.xlt
to
.xls). When the user opens up the .xls, it automatically refreshes
the
data
(problem if a new query was request from our MRP system).

Is there a way with VBA to break the data link once the file is saved
as
an
.xls?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Need code here

End Sub