Excel queries

[Ben]

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


--

 

[Jake Marx]

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]

Jake,

I took off the auto refresh and just use the code you provided. It worked
very nicely. Thanks so much.

Ben

--

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]

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

 

[Jake Marx]

Hi Ben,

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]

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