Workbook Open bypasses Query Refresh
 

I have a workbook "B.xls" that when opened, I need a linked query
refreshed. This is now manually done by choosing the "Enable automatic
refresh" button on the Query refresh dialogue box that pops up when
the file is opened manually.

I have added code in workbook A.xls behind a button that opens
workbook B.xls. Works fine EXCEPT that the query DOES NOT get
refreshed and no dialogue box pops up.

Can anyone suggest code to add to my workbook.open that will run the
query refresh?

Dim wkbk As Workbook

On Error Resume Next
Set wkbk = Workbooks("B.xls")
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open(Filename:="C:\Data\ClientName\B.xls ")
End If

Thanks
Dennis

Workbook Open bypasses Query Refresh
 

hi
never done it that way before but try adding this after open......

RANGE("A1").QueryTable.Refresh BackgroundQuery:=False

regards
FSt1

"ssGuru" wrote:

I have a workbook "B.xls" that when opened, I need a linked query
refreshed. This is now manually done by choosing the "Enable automatic
refresh" button on the Query refresh dialogue box that pops up when
the file is opened manually.

I have added code in workbook A.xls behind a button that opens
workbook B.xls. Works fine EXCEPT that the query DOES NOT get
refreshed and no dialogue box pops up.

Can anyone suggest code to add to my workbook.open that will run the
query refresh?

Dim wkbk As Workbook

On Error Resume Next
Set wkbk = Workbooks("B.xls")
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open(Filename:="C:\Data\ClientName\B.xls ")
End If

Thanks
Dennis

Workbook Open bypasses Query Refresh
 

hi
afterthought
make sure your range in within the query table. otherwise it wont refresh.

regards
FSt1

"FSt1" wrote:

hi
never done it that way before but try adding this after open......

RANGE("A1").QueryTable.Refresh BackgroundQuery:=False

regards
FSt1

"ssGuru" wrote:

I have a workbook "B.xls" that when opened, I need a linked query
refreshed. This is now manually done by choosing the "Enable automatic
refresh" button on the Query refresh dialogue box that pops up when
the file is opened manually.

I have added code in workbook A.xls behind a button that opens
workbook B.xls. Works fine EXCEPT that the query DOES NOT get
refreshed and no dialogue box pops up.

Can anyone suggest code to add to my workbook.open that will run the
query refresh?

Dim wkbk As Workbook

On Error Resume Next
Set wkbk = Workbooks("B.xls")
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open(Filename:="C:\Data\ClientName\B.xls ")
End If

Thanks
Dennis

Workbook Open bypasses Query Refresh
 

Thanks FSt1,
I gave that a try but apparently I don't know how to implement
correctly.
I get an error though the Range appears to be valid.
I have named a cell "LicActiveQuery" that is in the header of the area
on a sheet that the Db query updates.
I also tried a specific Range cell $G$1 but it wasn't happy with that
either.

My query to an external .xls file works just fine and is used to
update and repopulate a list in a table in the a local worksheet in
B.xls workbook.
This is the query that I referred to in my opening post that works
fine IF manually refreshed.

UPDATED Code:
On Error Resume Next
Set wkbk = Workbooks("B.xls")
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open(Filename:="C:\Data\ClientName\B.xls ")

'RANGE ERROR Creates an "Run-time error '1004':
' "Method 'Range' of object'_Worksheet' failed
Range("LicActiveQuery").QueryTable.Refresh
BackgroundQuery:=False

End If

Any other thoughts appreciated.
Dennis


On Feb 12, 4:21*am, FSt1 wrote:
hi
afterthought
make sure your range in within the query table. otherwise it wont refresh.

regards
FSt1



"FSt1" wrote:
hi
never done it that way before but try adding this after open......

RANGE("A1").QueryTable.Refresh BackgroundQuery:=False

regards
FSt1

"ssGuru" wrote:

I have a workbook "B.xls" that when opened, I need a linked query
refreshed. This is now manually done by choosing the "Enable automatic
refresh" button on the Query refresh dialogue box that pops up when
the file is opened manually.

I have added code in workbook A.xls behind a button that opens
workbook B.xls. *Works fine EXCEPT that the query DOES NOT get
refreshed and no dialogue box pops up.

Can anyone suggest code to add to my workbook.open that will run the
query refresh?

Dim wkbk As Workbook

On Error Resume Next
* * Set wkbk = Workbooks("B.xls")
On Error GoTo 0
* * If wkbk Is Nothing Then
* * * *Set wkbk = Workbooks.Open(Filename:="C:\Data\ClientName\B.xls ")
* * End If

Thanks
Dennis- Hide quoted text -

- Show quoted text -