Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Link to other data source" - How can I find out what is linked? | Excel Discussion (Misc queries) | |||
link source data | Excel Discussion (Misc queries) | |||
Break a link/formula but keep the data? | Excel Worksheet Functions | |||
How do I break a link to a source when opening Excel 2000? | Excel Worksheet Functions | |||
Unable ot Break link for Macro with no source | Excel Discussion (Misc queries) |