Query refreshing inside a loop - out of memory
 

Hi,

Can anybody help? As the loop goes, things are happening slower and slower and it never ends before consuming whole memory (1MB).
The query getting data from and putting to the same workbook which has a macro running this loop.
Here's my code:
For x = 1 To 25
---'some irrelevant to the issue code---

Range("F1").QueryTable.Refresh BackgroundQuery:=False

---This part may have some significance though I don't think so----
---Copying query results to another place---
Dim wiersz As Integer
wiersz = 2
Do
'Cells(Wiersz, 6).Select
'Selection.Copy
Range("FIRST_FR").Select
ActiveCell.FormulaR1C1 = Sheets("Arkusz2").Cells(wiersz, 6).Value
Selection.Offset(1, 0).Select
ActiveWorkbook.Names.Add Name:="FIRST_FR", RefersToR1C1:=Selection
wiersz = wiersz + 1
Loop Until Sheets("Arkusz2").Cells(wiersz, 6).Value = ""

Next

As I said it starts executing the rest of the code very quickly and then, after every round of refreshing query is getting worse.
Thank you in advance
Jarek

Query refreshing inside a loop - out of memory
 

I don't use named ranges a lot but i hear the cause a lot of slowdowns in
code. Try adding this to the beginning and end of your code.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'code
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"JU" wrote:

Hi,

Can anybody help? As the loop goes, things are happening slower and slower and it never ends before consuming whole memory (1MB).
The query getting data from and putting to the same workbook which has a macro running this loop.
Here's my code:
For x = 1 To 25
---'some irrelevant to the issue code---

Range("F1").QueryTable.Refresh BackgroundQuery:=False

---This part may have some significance though I don't think so----
---Copying query results to another place---
Dim wiersz As Integer
wiersz = 2
Do
'Cells(Wiersz, 6).Select
'Selection.Copy
Range("FIRST_FR").Select
ActiveCell.FormulaR1C1 = Sheets("Arkusz2").Cells(wiersz, 6).Value
Selection.Offset(1, 0).Select
ActiveWorkbook.Names.Add Name:="FIRST_FR", RefersToR1C1:=Selection
wiersz = wiersz + 1
Loop Until Sheets("Arkusz2").Cells(wiersz, 6).Value = ""

Next

As I said it starts executing the rest of the code very quickly and then, after every round of refreshing query is getting worse.
Thank you in advance
Jarek

Query refreshing inside a loop - out of memory
 

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'code
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Thank you John - good tip for my future tries. It helped a bit at the beginning, but did not solve the problem.
I finally found what's hapening. It has nothing to do with VBA and Excel itself. It is ODBC and MsQuery issue. As the query is "left outer join" it cannot be handled and stored somehow as a table (view) but stays at memory. Every time I refresh it a new view is added to the existing one. This is how I imagine in simplfied manner as I am not a programmer.
So I would now define the problem in other terms:
Assuming that I cannot use such a query how to compare two columns using VBA to have as a result only cells from the right column not existing in the left one?

Regards
Jarek