Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
queries in excel | Excel Discussion (Misc queries) | |||
Web Queries Excel 97 | Excel Discussion (Misc queries) | |||
Web queries and Excel | Excel Programming | |||
excel queries | Excel Programming | |||
Excel-Web Queries | Excel Programming |