query question
 

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.




--


Gary

query question
 

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.


Gary Keramidas wrote:
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.




--


Gary

query question
 

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

JakeyC wrote:
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.


Gary Keramidas wrote:
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.




--


Gary