Hi all,
First help post here for me (well, on behalf of a colleague as I'm signed up here). Just to advise we work for a rather large company with Office 2002 & Access 2002 (only just upgrading this year to 2007!!!). We, as a team have 2007 & 2002, for re-building reports for future use.
Anyway, my colleague has the below query.......
I have set up a macro to refresh a query in excel 2007 and i am using
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
which gives this error
Run time error 91
Object variable or With block variable not set
I have also tried to use:-
Selection.QueryTable.Refresh BackgroundQuery:=False
which gives this error
Run time error 1004
Application-defined or object-defined error
I know that the first line of code uses .ListObject. and this needs to be their to work in excel 2007 but i also know that without the .ListObject. in the code it will work in excel 2002/3
At the moment most users of this report will be using 2002 (Just waiting on the office getting upgraded) I will be adding an error trap. Then only call the refresh line of code depending on the version of excel that is being used.
I have done this before and all was OK it worked a treat.
This time however neither of the lines of code are working and to add insult when i record a macro to see what excel 2007 wants to use i get this:-
Range("I4").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
but then after recording the macro and just running it as is
I get the error:-
Run time error 1004
Application-defined or object-defined error
At first i thought it could be a problem with the references at the moment i am using
Visualbasic for applications
Microsoft excel 12.0 object library
OLE automation
Microsoft office 12.0 object library
Microsoft forms 2.0 object library
Microsoft windows common controls-2 6.0(SP4)
Microsoft ActiveX data objects 2.6 library
Microsoft access 10.0 object library
Is their anything else that i can try?
Also when i open the workbook it will happily refresh all the queries in the workbook using this bit of code but when it is running i only want to refresh the 1 query depending on what the user is doing on my form
Dim ws As Worksheet, q As QueryTable
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = False
Application.EnableEvents = False
For Each ws In ActiveWorkbook.Worksheets
For Each q In ws.QueryTables
q.Refresh (False)
Next
Next
Application.EnableEvents = True
Any help would be much appreciated.
Many thanks,
Jerry
First help post here for me (well, on behalf of a colleague as I'm signed up here). Just to advise we work for a rather large company with Office 2002 & Access 2002 (only just upgrading this year to 2007!!!). We, as a team have 2007 & 2002, for re-building reports for future use.
Anyway, my colleague has the below query.......
I have set up a macro to refresh a query in excel 2007 and i am using
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
which gives this error
Run time error 91
Object variable or With block variable not set
I have also tried to use:-
Selection.QueryTable.Refresh BackgroundQuery:=False
which gives this error
Run time error 1004
Application-defined or object-defined error
I know that the first line of code uses .ListObject. and this needs to be their to work in excel 2007 but i also know that without the .ListObject. in the code it will work in excel 2002/3
At the moment most users of this report will be using 2002 (Just waiting on the office getting upgraded) I will be adding an error trap. Then only call the refresh line of code depending on the version of excel that is being used.
I have done this before and all was OK it worked a treat.
This time however neither of the lines of code are working and to add insult when i record a macro to see what excel 2007 wants to use i get this:-
Range("I4").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
but then after recording the macro and just running it as is
I get the error:-
Run time error 1004
Application-defined or object-defined error
At first i thought it could be a problem with the references at the moment i am using
Visualbasic for applications
Microsoft excel 12.0 object library
OLE automation
Microsoft office 12.0 object library
Microsoft forms 2.0 object library
Microsoft windows common controls-2 6.0(SP4)
Microsoft ActiveX data objects 2.6 library
Microsoft access 10.0 object library
Is their anything else that i can try?
Also when i open the workbook it will happily refresh all the queries in the workbook using this bit of code but when it is running i only want to refresh the 1 query depending on what the user is doing on my form
Dim ws As Worksheet, q As QueryTable
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = False
Application.EnableEvents = False
For Each ws In ActiveWorkbook.Worksheets
For Each q In ws.QueryTables
q.Refresh (False)
Next
Next
Application.EnableEvents = True
Any help would be much appreciated.
Many thanks,
Jerry
