query question

[Gary Keramidas]

i am opening a workbook from another workbook with workbooks.open. that workbook
has a query to an as400. i have never been onsite to see what happens, but when
i open it here with the code i don't see any dialog. but when i just do a file
open, it shows a dialog stating it has queries to external references that
refresh automatically. i click disable here just to be open it.

so i have a few questions.

the workbooks.open code is:
Workbooks.Open Filename:=fPath & fName1, _
ReadOnly:=True, UpdateLinks:=3

1. is the read only keeping the dialog from popping up when the code opens the
file?

2. do i need to add a line of code to refresh the query?
Wbk2.QueryTable.Refresh or Wbk2.RefreshAll

3. if the client runs the code that opens it, how can i tell how long the query
takes to run so i know i will be copying the current data?
can i use QueryTable_AfterRefresh when calling from another workbook?

it's kind of hard when i've never seen it import the data.

 

[JakeyC]

To the best of my knowledge:


1) The dialogue will still appear in read-only, but if you are able to
manually change the properties of the Query in the other workbook, you
can remove 'Refresh Data on File Open' permanently, otherwise
Application.DisplayAlerts = False MIGHT work.

2) I think the QueryTable.Refresh method is specific to a Range,
i.e. Wbk2.Range("A1").QueryTable.Refresh

3) By setting the BackgroundQuery property of QueryTable.Refresh to
False, code execution will pause until the refresh is complete, so the
duration of the refresh will become irrelevant.

 

[JakeyC]

Of course, 2) should be Wbk2.Wksht1.Range("A1").QueryTable.Refresh