Excel queries
Hi all
I have set up queries in Excel using: Data-Import External Data-New Database Query After I saved the workbook with the queries, I close it and re-open the workbook. That's when a prompt keeps popping up every time I re-open the workbook asking me if I want to do a query refresh. It gave me two options: [Enable automatic refresh] [Disable automatic refresh] I want it to retrieve the data every time I open the workbook, but is there any way to disable this pop up prompt by changing certain setting or is there a programmatic way to disable it at run time? Thanks for sharing your suggestions. Ben -- |
Excel queries
Hi Ben,
I don't know that you can get rid of the warning dialog that comes up if you've selected automatic refresh on open via the query table properties. However, you could add this code to the ThisWorkbook object's code pane in the VBE: Private Sub Workbook_Open() Dim ws As Worksheet Dim qt As QueryTable For Each ws In Worksheets For Each qt In ws.QueryTables qt.Refresh Next qt Next ws End Sub This will refresh all of the query tables in your workbook. Now, you'll see the macro security warning instead, so maybe it's not any better. But to get around that, you could sign the code so you don't get the macro security warning. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Ben wrote: Hi all I have set up queries in Excel using: Data-Import External Data-New Database Query After I saved the workbook with the queries, I close it and re-open the workbook. That's when a prompt keeps popping up every time I re-open the workbook asking me if I want to do a query refresh. It gave me two options: [Enable automatic refresh] [Disable automatic refresh] I want it to retrieve the data every time I open the workbook, but is there any way to disable this pop up prompt by changing certain setting or is there a programmatic way to disable it at run time? Thanks for sharing your suggestions. Ben |
Excel queries
Jake,
I took off the auto refresh and just use the code you provided. It worked very nicely. Thanks so much. Ben -- "Jake Marx" wrote: Hi Ben, I don't know that you can get rid of the warning dialog that comes up if you've selected automatic refresh on open via the query table properties. However, you could add this code to the ThisWorkbook object's code pane in the VBE: Private Sub Workbook_Open() Dim ws As Worksheet Dim qt As QueryTable For Each ws In Worksheets For Each qt In ws.QueryTables qt.Refresh Next qt Next ws End Sub This will refresh all of the query tables in your workbook. Now, you'll see the macro security warning instead, so maybe it's not any better. But to get around that, you could sign the code so you don't get the macro security warning. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Ben wrote: Hi all I have set up queries in Excel using: Data-Import External Data-New Database Query After I saved the workbook with the queries, I close it and re-open the workbook. That's when a prompt keeps popping up every time I re-open the workbook asking me if I want to do a query refresh. It gave me two options: [Enable automatic refresh] [Disable automatic refresh] I want it to retrieve the data every time I open the workbook, but is there any way to disable this pop up prompt by changing certain setting or is there a programmatic way to disable it at run time? Thanks for sharing your suggestions. Ben |
Excel queries
Hi Ben,
Ben wrote: I took off the auto refresh and just use the code you provided. It worked very nicely. Thanks so much. Great - glad to help! -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Hi Ben, I don't know that you can get rid of the warning dialog that comes up if you've selected automatic refresh on open via the query table properties. However, you could add this code to the ThisWorkbook object's code pane in the VBE: Private Sub Workbook_Open() Dim ws As Worksheet Dim qt As QueryTable For Each ws In Worksheets For Each qt In ws.QueryTables qt.Refresh Next qt Next ws End Sub This will refresh all of the query tables in your workbook. Now, you'll see the macro security warning instead, so maybe it's not any better. But to get around that, you could sign the code so you don't get the macro security warning. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Ben wrote: Hi all I have set up queries in Excel using: Data-Import External Data-New Database Query After I saved the workbook with the queries, I close it and re-open the workbook. That's when a prompt keeps popping up every time I re-open the workbook asking me if I want to do a query refresh. It gave me two options: [Enable automatic refresh] [Disable automatic refresh] I want it to retrieve the data every time I open the workbook, but is there any way to disable this pop up prompt by changing certain setting or is there a programmatic way to disable it at run time? Thanks for sharing your suggestions. Ben |
All times are GMT +1. The time now is 06:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com