Selection.QueryTable.Refresh BackgroundQuery:=False error

[Rhadida]

Why oh why won't this work?????

Sheets("DATA1").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

I use this all the time, so what can cause the problem here. BTW I get the Application-Defined or Object-Defined Error (1004).

Could it be that the Refrsh isn't finished when exceuting an other macro??? I use the DoEvents commandline after I exceute the refresh-macro. Shouldn't this force the macro to finish before it continues???

 

[Joe Was]

Re: Selection.QueryTable.Refresh BackgroundQuery:=False erro

Did you try to force the selection first with: "With?"\


With Worksheets(1).QueryTables(1)
If .Refreshing Then
GoTo myEnd

Else

.Refresh BackgroundQuery := False
.(your)ResultRange.Select
End If
End With
myEnd:
Exit Sub

 

[Rhadida]

Re: Selection.QueryTable.Refresh BackgroundQuery:=False erro

Nope this won't work either????

Here is the failing part of the macro:

Sub Programma_A()
'Deze macro voert het programma uit!!!!
Module8.DATA1_Verversen
DoEvents
Module8.DA_Filteren
DoEvents

Dim DA_NAAM As String
Dim VERZAMEL_NAAM As String

It fails on the first part of the macro. When I run the filter.
But this is not the only strange thing happening on my workbook. When I try to add text to a cells, where a macro adds data is starts to excecute the macro?

 

[Rhadida]

Re: Selection.QueryTable.Refresh BackgroundQuery:=False erro

I have discovered the problem:

It seems that when I copy data from a query, Excel wants to excecute the ComboBox again, that is why the tbles can't be refreshed cause it is in a loop. I have solved this by using the ComboBox in combination with a CommanButton. After I select what I want I use the commandButton and the macro works just fine.

However this is a really strange event, and I have used the ComboBoxes many times before and there they work just fine.
Even when I start a new Excel-document and I create a Query from where I copy data I get the same problem. THIS IS A NEW EVENT, Yesterday everything worked fien.

THANX for all of your thinking, any tips are still appreciated.

/Rhadida

 

[Joe Was]

Re: Selection.QueryTable.Refresh BackgroundQuery:=False erro

Open a workbook that you know works correctly.
If it still runs correctly now?
Check its references (in code editor: Tools - References) note what references have been selected, then close and open one of the workbooks that are giving you a problem. Check its references and add any that are gone. Also check for some that are listed as "Missing."

If a good workbook now does not work well:
From "Help" Do a "Detect and Repair" and see if that fixes it.

I have one PC setup the same as the others that will not run code unless every command is conscripted with the sheet and range after a sheet select and it will also fail on some types of User Forms?

If I take the code to a good PC and change the syntax to normal syntax or not the code works if I put it back on the bad PC good code fails. I have not been able to find the problem, so I hope you don't have the same problem?

 

[ceelly]

I am replying to this question a couple of years late.

I encountered the same problem today. When I was altering an excel application that use to run in Excel 97 so it can run in Excel 07. ( yes we are that much out of date at work) There was a querytable in the range A48 in the excel spreadsheet I wanted to refresh using the following VB code.

Range("A48").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

"Selection.QueryTable.Refresh BackgroundQuery:=False" would not work in Excel 07.. after doing a far bit of googling I found a few people with the same porblem but not solution. So I recorded a macro in excel with me manually refreshing the the query (right click with the mouse on the selection "A48" and hit 'refresh' option on the drop down menu that appears) and looking at the macro code I got my code to work.

By changing

Selection.QueryTable.Refresh BackgroundQuery:=False

to

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

The above piece of code worked!!!!

 

[che_epy]

Dear Ceelly,

Your reply was never late for me. Thank, your code did solved my all day long misery.
Thank a lot. I knew there was something bout Excel '07 that halt my edited Excel '03 file.
Strange though I add new sheet called Data4, I copy previous code and change sheet parameter to "Data4" as below:-
1. Sheets("Data1").Range("A1").QueryTable.Refresh BackgroundQuery:=False
2. Sheets("Data2").Range("A2").QueryTable.Refresh BackgroundQuery:=False
3. Sheets("Data3").Range("A2").QueryTable.Refresh BackgroundQuery:=False
4. Sheets("Data4").Range("A2").QueryTable.Refresh BackgroundQuery:=False

The new line is line 4, file was save as .XLS but executed using Excel '07. Line 1 to 3 was ok but it encounter error at line 4. It is exactly the same code!...

I use Ceelly's solution and changed 'SELECTION' to specific sheets range to prevent sheet from switch to other sheets while refreshing query refer below

Sheets("Data4").Range("A2").ListObject.QueryTable.Refresh BackgroundQuery:=False

and it works!! Thank again Ceelly.

 

[Jorgepue]

Dear all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I've a macro that before work, and now does not. The error is the same you explained before -->Run - time error 1004 (Genral erroe ODBC):<o></o>
<o></o>
Sheets("Export").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

<o></o>
<o></o>
I tried the solution that was suggested before:<o></o>
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False but now the error is error 91 (Object variable or with non implemented) <o></o>
<o></o>
After reading excel help I tried with <o></o>

Dim Export As Worksheet
With Thisworksheet
Set Export = Worksheets("Export")
End With
'MyCount = Export.Count
Sheets("Export").Select
Range("A2").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False<o></o>
<o></o>
But it still not working with the same problem. I'm starting with the macro... Can somebody help me please?<o></o>
<o></o>
Thank you<o></o>
Jorge<o></o>

 

[Vis]

Just wanted to say I had a similar problem. I had 4 access queries that were supposed to refresh on open and it worked perrfectly for weeks then mysteriously stopped working.

I suspect this was because I may have changed the title on one of the columns or possibly the database.

In any matter, I was able to solve the problem by clearing out all the data on the sheet of the query (but don't delete the query). Then let it refresh itself with fresh data. it will put things back where they're supposed to go, give that a try

 

[Jorgepue]

Thank you. Yes the problem was the name of the file... I' changed the name by adding the date (database10-10-2010). when I realized the problem i recall the file database and it restart working as before.