Hi,

I builted 4-5 years ago a massive program that download many web pages
from a web site, compile the data and cruch it on a daily basis. It
worked very well as long as I would never change, update or upgrade my
computer. Each time I would change my office or windows I have to
ajust my program. Anyway, here I am again with you know what. Excel
2007.

here is the bug:

I have a VBA that load an excel page, and then load a .IQY to get data
from the web. then process the info, copy it to a master file, clear
the excel sheet and load another one. I could do this up to a 100
times depending of the size of the data to download.

the bug i have in 2007 is that it seem to keep in memory the
FIRST .IQY and keep loading the same one over and over even if I see
excel load the right .IQY. The results however is always identical
the the FIRST .IQY.

the only way for me to make this work is to completely CLOSE exel
(not just the file but also excel) and THEN load the second .IQY.
This is unthinkable manualy. In excel 2003 there is no problem!

this is a section of my VBA:

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;\\pontiac\N-vma\Queries\Buick\high\2007 - 4BK26 - " &
CStr(I) & ".iqy", Destination:=Range("A1"))
.Name = "2007 - 4BK26 - " & CStr(I)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With



Could someone tell me how to diable something like the memory of
dataconnection of excel 2007 ?

I'm not sure if this will work, but it might help to delete the connection
(to the .iqy file) before clearing the sheet or before creating the next
QueryTable?

ActiveSheet.QueryTables(1).WorkbookConnection.Dele te


"alex" wrote:

Hi,

I builted 4-5 years ago a massive program that download many web pages
from a web site, compile the data and cruch it on a daily basis. It
worked very well as long as I would never change, update or upgrade my
computer. Each time I would change my office or windows I have to
ajust my program. Anyway, here I am again with you know what. Excel
2007.

here is the bug:

I have a VBA that load an excel page, and then load a .IQY to get data
from the web. then process the info, copy it to a master file, clear
the excel sheet and load another one. I could do this up to a 100
times depending of the size of the data to download.

the bug i have in 2007 is that it seem to keep in memory the
FIRST .IQY and keep loading the same one over and over even if I see
excel load the right .IQY. The results however is always identical
the the FIRST .IQY.

the only way for me to make this work is to completely CLOSE exel
(not just the file but also excel) and THEN load the second .IQY.
This is unthinkable manualy. In excel 2003 there is no problem!

this is a section of my VBA:

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;\\pontiac\N-vma\Queries\Buick\high\2007 - 4BK26 - " &
CStr(I) & ".iqy", Destination:=Range("A1"))
.Name = "2007 - 4BK26 - " & CStr(I)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With



Could someone tell me how to diable something like the memory of
dataconnection of excel 2007 ?

Nice try, look promising but it does not work. even manualy i canot
clear the memory somehow. even when a delet the worksheet and start a
new one , clear all the connection. the ONLY way seem to close
completely excel!

is this a bug?

On Jul 25, 9:48 pm, Michelle
wrote:
I'm not sure if this will work, but it might help to delete the connection
(to the .iqy file) before clearing the sheet or before creating the next
QueryTable?

ActiveSheet.QueryTables(1).WorkbookConnection.Dele te



"alex" wrote:
Hi,

I builted 4-5 years ago a massive program that download many web pages
from a web site, compile the data and cruch it on a daily basis. It
worked very well as long as I would never change, update or upgrade my
computer. Each time I would change my office or windows I have to
ajust my program. Anyway, here I am again with you know what. Excel
2007.

here is the bug:

I have a VBA that load an excel page, and then load a .IQY to get data
from the web. then process the info, copy it to a master file, clear
the excel sheet and load another one. I could do this up to a 100
times depending of the size of the data to download.

the bug i have in 2007 is that it seem to keep in memory the
FIRST .IQY and keep loading the same one over and over even if I see
excel load the right .IQY. The results however is always identical
the the FIRST .IQY.

the only way for me to make this work is to completely CLOSE exel
(not just the file but also excel) and THEN load the second .IQY.
This is unthinkable manualy. In excel 2003 there is no problem!

this is a section of my VBA:

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;\\pontiac\N-vma\Queries\Buick\high\2007 - 4BK26 - " &
CStr(I) & ".iqy", Destination:=Range("A1"))
.Name = "2007 - 4BK26 - " & CStr(I)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

Could someone tell me how to diable something like the memory of
dataconnection of excel 2007 ?- Hide quoted text -

- Show quoted text -