Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do break the link from my excel workbook to a access database.
The access database my excel workbook is trying to link to no longer exists.
Yet each time I open the lworkbook I receive a message to enable or disable automatic refresh. How do I unlink and how do I shut off the prompting for refresh? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do break the link from my excel workbook to a access database.
I hope this doesn't post twice, I tried once and it appeared to fail, (reply
window errased itself after it caused me to log in again after I clicked the Post button) so I am going to try again. Vernon, the usefulness of this may depend on which version of Excel you are using. Please note, you should only make the following changes in a copy of the working copy, not the original. Only make the modified copy the working copy after you have tested all changes to make sure you have not make a horrible, horrible mistake. Three methods below, in descending order of safety. The first one requires you to go to each worksheet and manually disable automatic update on open for each query. This one is safer because you get to look at each query as you go and will be able to be sure you actually want to do it. The second method uses VBA code to do all of the QueryTables at once, less safe only because you do not get that chance to Abort Abort if you realize, after all, that you actually need one or more of the tables to update. On the other hand, the VBA method is completely reversible. The third method destroys the queries. The following is for 'QueryTables' or queries embedded in the worksheet: First Method: For more information, search Help for refresh imported data. Do this on EVERY worksheet in the workbook in question. On each worksheet in the workbook, Right-Click inside the data area of each query. You will get a dropdown menu with the usual Right-Click suspects such as Cut, Copy, Paste, and Paste Special. If you are inside the query result area, you will also see additional options allowing you to work with the query itself, such as Edit Query, Data Range Properties, and Refresh Data. If you click Data Range Properties you will get a dialog box called "External Data Range Properties". In this box you will find many choices, including "Refresh data on file open". Likely, this box is 'checked' on one or more of your QueryTables. Uncheck that box for each embedded query that you do not want to update on open. If you do this for all of the QueryTables in the workbook, this will stop the attempt at updating on open and should stop the notifications on open. Please note that there could be more than one QueryTable in a worksheet. They can even end up overlapping, making them hard to find. If you make every QT that you can find not refreshing on open and you still get the message on open, than go to each sheet, click Edit | Go To Then look in the Go To list for things named Query_from_[Data source name]. If you find more than one on a particular sheet, Go To both and make sure that you have turned off automatic update on open for both. Second Method: If you still get the message on startup, create a new, blank worksheet in the workbook. Right click on the tab of the new worksheet and select View Code. The Visual Basic for Applications (VBA) window will open with an open code window (module) showing. Paste the following code in the module. ' code starts************** Sub List_QueryTables() Dim x As Integer x = 0 With ActiveWorkbook For Each s In .Sheets x = x + 1 y = 0 Debug.Print "Worksheet#"; x, "Worksheet Name:"; s.Name For Each q In s.QueryTables y = y + 1 Debug.Print , "QueryTable Count:"; y, "Query Name: "; q.Name, "Refresh on Open? "; q.RefreshOnFileOpen ' q.RefreshOnFileOpen = False ' Debug.Print , , q.Name, "Refresh on Open? "; q.RefreshOnFileOpen Next q Next s End With End Sub 'code ends*********** Note that there is a single quote (or an apostrophe) in front of two lines of code, leave them there for now. Make sure your blinking cursor is within any line of the code in the Sub procedure that you have pasted. Also note that the first Debug.Print line [Debug.Print , "QueryTable Count:"; y, "Query Name: "; q.Name, "Refresh on Open? "; q.RefreshOnFileOpen] word-wraps in this Discusson Goup Reply window, it should not wrap in the code window (all one line). It should paste ok, but check that it is on a single line. In the top of the VBA window, you will see the usual menus such as File, Edit, and View; you will also see the less usual items of Debug and Run. Under the VBA menu Run, and with your cursor inside the code you have pasted, click Run Sub/Userform. After you have run the List_QueryTables Subroutine, under the VBA View menu, click Immediate Window. This window is where the Debug.Print command sends output. Scan down the list in the immediate window to see if you find any QueryTables with Refresh on Open set to True. If you find any, go to the listed worksheet in the workbook, Edit | Go To that query and look at it to determine that you really want to disable auto update on that QT. Once (and only after) you have examined all of the stray, hard to find QTs, and if you decide that you really do want to disable auto update on all QTs in the workbook, go back to the VBA window with your code showing in it. Delete the single quote in front of the line that reads Q.RefreshOnFileOpen = False and also for the line that reads Debug.Print , , q.Name, "Refresh on Open? "; q.RefreshOnFileOpen. Empty the Immediate window, and then run the code again, and again check your results in the Immediate window. You should now see that all QTs in the workbook are now set to NOT update on open. If you have not already done this procedure in a copy of the original workbook, now make sure that you SAVE AS to another file name; otherwise, if this is already a copy, than just save, close, and then re-open the workbook to check. You should not get the update message. If you dont like it, you can go back to the code window and replace the word False in the q.RefreshOnFileOpen = False line with the word True, and then run the code again. Check your results in the Immediate window, if it worked ok, then save and re-open and you should have the annoying message back. If you are satisfied that the workbook will still work with all Queries not updating-on-open, then move and rename the former working copy to a backup name and location, and then rename this file to the working copy name in the working directory. Use as directed; (Lather, rinse, and repeat as necessary). Third and least safe method: Warning, this will destroy any database connection, data table linking, and record filtering and selection information that is embedded in the queries themselves. On the other hand, this will preserve any data left in the table from the last successful update. If you really do know that you will never use the queries again, might need what is left in the table, you can go to each query, use the Edit | Go To tool to select each whole QueryTable, and then paste each table onto itself using Edit | Copy then Edit | Paste Special | Values and Number Formats. This will make a normal, unlinked excel table out of whatever is left in the QueryTable, preserving formats and data but not formulas. Be careful that you do not select any formulas adjacent to the QueryTable. [Even less safe: If you know for certain that you will never need even the information available from the last good update in any of the QueryTables, if any was left, then you could just delete the whole selected table instead of copy paste special.] The following is for DDE or OLE links to external data, not queries: In 2003, XP, and maybe other versions, there is a "Links" choice under the "Edit" dropdown menu. (as in File - Edit - View at the top of the page.) If you do not have any links in the workbook, the "Links" choice will be grayed out. When you click on "Edit | Links" in a workbook that has links, you will get some choices, including the ability to convert automatic links to manual links, and the ability to turn off "Ask to update automatic links" for automatic links. You can also do other things having to do with re-focusing links to another data source. Search help for the subject "Control when links are updated" for more information. Hope this helps. Let us know if you need further help or clarification. SongBear "Vernon" wrote: The access database my excel workbook is trying to link to no longer exists. Yet each time I open the lworkbook I receive a message to enable or disable automatic refresh. How do I unlink and how do I shut off the prompting for refresh? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do break the link from my excel workbook to a access database.
I did forget one thing in the previous message. If you use Method Two (VBA
Code), when finished, you will need to delete the new, empty worksheet that you created in the workbook. This will get rid of the temporary code (which is attached to that new empty worksheet) and prevent an all-new annoyance on opening the workbook, the 'Macros Dissabled' security message. "Vernon" wrote: The access database my excel workbook is trying to link to no longer exists. Yet each time I open the lworkbook I receive a message to enable or disable automatic refresh. How do I unlink and how do I shut off the prompting for refresh? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do break the link from my excel workbook to a access datab
Option one worked great, thank you
"SongBear" wrote: I hope this doesn't post twice, I tried once and it appeared to fail, (reply window errased itself after it caused me to log in again after I clicked the Post button) so I am going to try again. Vernon, the usefulness of this may depend on which version of Excel you are using. Please note, you should only make the following changes in a copy of the working copy, not the original. Only make the modified copy the working copy after you have tested all changes to make sure you have not make a horrible, horrible mistake. Three methods below, in descending order of safety. The first one requires you to go to each worksheet and manually disable automatic update on open for each query. This one is safer because you get to look at each query as you go and will be able to be sure you actually want to do it. The second method uses VBA code to do all of the QueryTables at once, less safe only because you do not get that chance to Abort Abort if you realize, after all, that you actually need one or more of the tables to update. On the other hand, the VBA method is completely reversible. The third method destroys the queries. The following is for 'QueryTables' or queries embedded in the worksheet: First Method: For more information, search Help for refresh imported data. Do this on EVERY worksheet in the workbook in question. On each worksheet in the workbook, Right-Click inside the data area of each query. You will get a dropdown menu with the usual Right-Click suspects such as Cut, Copy, Paste, and Paste Special. If you are inside the query result area, you will also see additional options allowing you to work with the query itself, such as Edit Query, Data Range Properties, and Refresh Data. If you click Data Range Properties you will get a dialog box called "External Data Range Properties". In this box you will find many choices, including "Refresh data on file open". Likely, this box is 'checked' on one or more of your QueryTables. Uncheck that box for each embedded query that you do not want to update on open. If you do this for all of the QueryTables in the workbook, this will stop the attempt at updating on open and should stop the notifications on open. Please note that there could be more than one QueryTable in a worksheet. They can even end up overlapping, making them hard to find. If you make every QT that you can find not refreshing on open and you still get the message on open, than go to each sheet, click Edit | Go To Then look in the Go To list for things named Query_from_[Data source name]. If you find more than one on a particular sheet, Go To both and make sure that you have turned off automatic update on open for both. Second Method: If you still get the message on startup, create a new, blank worksheet in the workbook. Right click on the tab of the new worksheet and select View Code. The Visual Basic for Applications (VBA) window will open with an open code window (module) showing. Paste the following code in the module. ' code starts************** Sub List_QueryTables() Dim x As Integer x = 0 With ActiveWorkbook For Each s In .Sheets x = x + 1 y = 0 Debug.Print "Worksheet#"; x, "Worksheet Name:"; s.Name For Each q In s.QueryTables y = y + 1 Debug.Print , "QueryTable Count:"; y, "Query Name: "; q.Name, "Refresh on Open? "; q.RefreshOnFileOpen ' q.RefreshOnFileOpen = False ' Debug.Print , , q.Name, "Refresh on Open? "; q.RefreshOnFileOpen Next q Next s End With End Sub 'code ends*********** Note that there is a single quote (or an apostrophe) in front of two lines of code, leave them there for now. Make sure your blinking cursor is within any line of the code in the Sub procedure that you have pasted. Also note that the first Debug.Print line [Debug.Print , "QueryTable Count:"; y, "Query Name: "; q.Name, "Refresh on Open? "; q.RefreshOnFileOpen] word-wraps in this Discusson Goup Reply window, it should not wrap in the code window (all one line). It should paste ok, but check that it is on a single line. In the top of the VBA window, you will see the usual menus such as File, Edit, and View; you will also see the less usual items of Debug and Run. Under the VBA menu Run, and with your cursor inside the code you have pasted, click Run Sub/Userform. After you have run the List_QueryTables Subroutine, under the VBA View menu, click Immediate Window. This window is where the Debug.Print command sends output. Scan down the list in the immediate window to see if you find any QueryTables with Refresh on Open set to True. If you find any, go to the listed worksheet in the workbook, Edit | Go To that query and look at it to determine that you really want to disable auto update on that QT. Once (and only after) you have examined all of the stray, hard to find QTs, and if you decide that you really do want to disable auto update on all QTs in the workbook, go back to the VBA window with your code showing in it. Delete the single quote in front of the line that reads Q.RefreshOnFileOpen = False and also for the line that reads Debug.Print , , q.Name, "Refresh on Open? "; q.RefreshOnFileOpen. Empty the Immediate window, and then run the code again, and again check your results in the Immediate window. You should now see that all QTs in the workbook are now set to NOT update on open. If you have not already done this procedure in a copy of the original workbook, now make sure that you SAVE AS to another file name; otherwise, if this is already a copy, than just save, close, and then re-open the workbook to check. You should not get the update message. If you dont like it, you can go back to the code window and replace the word False in the q.RefreshOnFileOpen = False line with the word True, and then run the code again. Check your results in the Immediate window, if it worked ok, then save and re-open and you should have the annoying message back. If you are satisfied that the workbook will still work with all Queries not updating-on-open, then move and rename the former working copy to a backup name and location, and then rename this file to the working copy name in the working directory. Use as directed; (Lather, rinse, and repeat as necessary). Third and least safe method: Warning, this will destroy any database connection, data table linking, and record filtering and selection information that is embedded in the queries themselves. On the other hand, this will preserve any data left in the table from the last successful update. If you really do know that you will never use the queries again, might need what is left in the table, you can go to each query, use the Edit | Go To tool to select each whole QueryTable, and then paste each table onto itself using Edit | Copy then Edit | Paste Special | Values and Number Formats. This will make a normal, unlinked excel table out of whatever is left in the QueryTable, preserving formats and data but not formulas. Be careful that you do not select any formulas adjacent to the QueryTable. [Even less safe: If you know for certain that you will never need even the information available from the last good update in any of the QueryTables, if any was left, then you could just delete the whole selected table instead of copy paste special.] The following is for DDE or OLE links to external data, not queries: In 2003, XP, and maybe other versions, there is a "Links" choice under the "Edit" dropdown menu. (as in File - Edit - View at the top of the page.) If you do not have any links in the workbook, the "Links" choice will be grayed out. When you click on "Edit | Links" in a workbook that has links, you will get some choices, including the ability to convert automatic links to manual links, and the ability to turn off "Ask to update automatic links" for automatic links. You can also do other things having to do with re-focusing links to another data source. Search help for the subject "Control when links are updated" for more information. Hope this helps. Let us know if you need further help or clarification. SongBear "Vernon" wrote: The access database my excel workbook is trying to link to no longer exists. Yet each time I open the lworkbook I receive a message to enable or disable automatic refresh. How do I unlink and how do I shut off the prompting for refresh? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do break the link from my excel workbook to a access datab
Great, thanks for letting me know.
SongBear "Vernon" wrote: Option one worked great, thank you "SongBear" wrote: I hope this doesn't post twice, I tried once and it appeared to fail, (reply window errased itself after it caused me to log in again after I clicked the Post button) so I am going to try again. Vernon, the usefulness of this may depend on which version of Excel you are using. Please note, you should only make the following changes in a copy of the working copy, not the original. Only make the modified copy the working copy after you have tested all changes to make sure you have not make a horrible, horrible mistake. Three methods below, in descending order of safety. The first one requires you to go to each worksheet and manually disable automatic update on open for each query. This one is safer because you get to look at each query as you go and will be able to be sure you actually want to do it. The second method uses VBA code to do all of the QueryTables at once, less safe only because you do not get that chance to Abort Abort if you realize, after all, that you actually need one or more of the tables to update. On the other hand, the VBA method is completely reversible. The third method destroys the queries. The following is for 'QueryTables' or queries embedded in the worksheet: First Method: For more information, search Help for refresh imported data. Do this on EVERY worksheet in the workbook in question. On each worksheet in the workbook, Right-Click inside the data area of each query. You will get a dropdown menu with the usual Right-Click suspects such as Cut, Copy, Paste, and Paste Special. If you are inside the query result area, you will also see additional options allowing you to work with the query itself, such as Edit Query, Data Range Properties, and Refresh Data. If you click Data Range Properties you will get a dialog box called "External Data Range Properties". In this box you will find many choices, including "Refresh data on file open". Likely, this box is 'checked' on one or more of your QueryTables. Uncheck that box for each embedded query that you do not want to update on open. If you do this for all of the QueryTables in the workbook, this will stop the attempt at updating on open and should stop the notifications on open. Please note that there could be more than one QueryTable in a worksheet. They can even end up overlapping, making them hard to find. If you make every QT that you can find not refreshing on open and you still get the message on open, than go to each sheet, click Edit | Go To Then look in the Go To list for things named Query_from_[Data source name]. If you find more than one on a particular sheet, Go To both and make sure that you have turned off automatic update on open for both. Second Method: If you still get the message on startup, create a new, blank worksheet in the workbook. Right click on the tab of the new worksheet and select View Code. The Visual Basic for Applications (VBA) window will open with an open code window (module) showing. Paste the following code in the module. ' code starts************** Sub List_QueryTables() Dim x As Integer x = 0 With ActiveWorkbook For Each s In .Sheets x = x + 1 y = 0 Debug.Print "Worksheet#"; x, "Worksheet Name:"; s.Name For Each q In s.QueryTables y = y + 1 Debug.Print , "QueryTable Count:"; y, "Query Name: "; q.Name, "Refresh on Open? "; q.RefreshOnFileOpen ' q.RefreshOnFileOpen = False ' Debug.Print , , q.Name, "Refresh on Open? "; q.RefreshOnFileOpen Next q Next s End With End Sub 'code ends*********** Note that there is a single quote (or an apostrophe) in front of two lines of code, leave them there for now. Make sure your blinking cursor is within any line of the code in the Sub procedure that you have pasted. Also note that the first Debug.Print line [Debug.Print , "QueryTable Count:"; y, "Query Name: "; q.Name, "Refresh on Open? "; q.RefreshOnFileOpen] word-wraps in this Discusson Goup Reply window, it should not wrap in the code window (all one line). It should paste ok, but check that it is on a single line. In the top of the VBA window, you will see the usual menus such as File, Edit, and View; you will also see the less usual items of Debug and Run. Under the VBA menu Run, and with your cursor inside the code you have pasted, click Run Sub/Userform. After you have run the List_QueryTables Subroutine, under the VBA View menu, click Immediate Window. This window is where the Debug.Print command sends output. Scan down the list in the immediate window to see if you find any QueryTables with Refresh on Open set to True. If you find any, go to the listed worksheet in the workbook, Edit | Go To that query and look at it to determine that you really want to disable auto update on that QT. Once (and only after) you have examined all of the stray, hard to find QTs, and if you decide that you really do want to disable auto update on all QTs in the workbook, go back to the VBA window with your code showing in it. Delete the single quote in front of the line that reads Q.RefreshOnFileOpen = False and also for the line that reads Debug.Print , , q.Name, "Refresh on Open? "; q.RefreshOnFileOpen. Empty the Immediate window, and then run the code again, and again check your results in the Immediate window. You should now see that all QTs in the workbook are now set to NOT update on open. If you have not already done this procedure in a copy of the original workbook, now make sure that you SAVE AS to another file name; otherwise, if this is already a copy, than just save, close, and then re-open the workbook to check. You should not get the update message. If you dont like it, you can go back to the code window and replace the word False in the q.RefreshOnFileOpen = False line with the word True, and then run the code again. Check your results in the Immediate window, if it worked ok, then save and re-open and you should have the annoying message back. If you are satisfied that the workbook will still work with all Queries not updating-on-open, then move and rename the former working copy to a backup name and location, and then rename this file to the working copy name in the working directory. Use as directed; (Lather, rinse, and repeat as necessary). Third and least safe method: Warning, this will destroy any database connection, data table linking, and record filtering and selection information that is embedded in the queries themselves. On the other hand, this will preserve any data left in the table from the last successful update. If you really do know that you will never use the queries again, might need what is left in the table, you can go to each query, use the Edit | Go To tool to select each whole QueryTable, and then paste each table onto itself using Edit | Copy then Edit | Paste Special | Values and Number Formats. This will make a normal, unlinked excel table out of whatever is left in the QueryTable, preserving formats and data but not formulas. Be careful that you do not select any formulas adjacent to the QueryTable. [Even less safe: If you know for certain that you will never need even the information available from the last good update in any of the QueryTables, if any was left, then you could just delete the whole selected table instead of copy paste special.] The following is for DDE or OLE links to external data, not queries: In 2003, XP, and maybe other versions, there is a "Links" choice under the "Edit" dropdown menu. (as in File - Edit - View at the top of the page.) If you do not have any links in the workbook, the "Links" choice will be grayed out. When you click on "Edit | Links" in a workbook that has links, you will get some choices, including the ability to convert automatic links to manual links, and the ability to turn off "Ask to update automatic links" for automatic links. You can also do other things having to do with re-focusing links to another data source. Search help for the subject "Control when links are updated" for more information. Hope this helps. Let us know if you need further help or clarification. SongBear "Vernon" wrote: The access database my excel workbook is trying to link to no longer exists. Yet each time I open the lworkbook I receive a message to enable or disable automatic refresh. How do I unlink and how do I shut off the prompting for refresh? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moved database, how do I change link to the database in excel | Excel Discussion (Misc queries) | |||
Break link MS Excel 2000 | Excel Discussion (Misc queries) | |||
How do I break a link between cells in the same workbook? | Excel Discussion (Misc queries) | |||
How do i break a link to another excel workbook? | New Users to Excel | |||
link Access workbook to Excel workbook | Excel Discussion (Misc queries) |