Break a link to a data source

[Guest]

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

 

[Dick Kusleika]

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/ExternalData4.htm#DeleteVBA

 

[Guest]

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]

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.