Web query causes excel crash!

[God Itself]

Hi, excel 2007 crashes while following web query (via VBA) is about to start:

========
a = URL;http://www.bankier.pl/inwestowanie/...FI-jednostkowy-raport-kwartalny.html?offset=0
b = any_name_01
With Sheets("query").QueryTables.Add(Connection:=a, Destination:=Sheets("query").Range("A1"))
.Name = b
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False 'ERROR LINE ???
End With
========
month ago this module worked perfectly (in loop) - this time something from the site may cause this crash.
could you create and check web query via VBA and commonly via query creator and pull data from the link:
(link is safe, it is Polish daily financial e-bulletin)
http://www.bankier.pl/inwestowanie/...FI-jednostkowy-raport-kwartalny.html?offset=0

i do not have idea what may cause "application shutdown and windows error reporting"

regards

 

[Don Guillett]

2 problems.
1. You did NOT have " " around your url.
2. Crashed on me also (xl2003). I think it has to do with getting the WHOLE
page and the graphics? See fix below to just get the data table.

3. When refreshing, do NOT add another fetch UNLESS you delete the first one
and the defined name that goes along. Use the Refresh macro below,
especially if in a LOOP.

Sub getquery()
a =
"URL;http://www.bankier.pl/inwestowanie/...FI-jednostkowy-raport-kwartalny.html?offset=0"
b = any_name_01
With Sheets("query").QueryTables.Add(Connection:=a,
Destination:=Sheets("query").Range("A1"))
.Name = b
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0

'.WebSelectionType = xlEntirePage
'.WebFormatting = xlWebFormattingNone

.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9"

.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False 'ERROR LINE ???
End With

End Sub
Sub RefreshQuery()
With Sheets("Query").QueryTables(1)
.Connection = _
"URL;http://www.bankier.pl/inwestowanie/...FI-jednostkowy-raport-kwartalny.html?offset=0"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi, excel 2007 crashes while following web query (via VBA) is about to
start:

========
a =
URL;http://www.bankier.pl/inwestowanie/...FI-jednostkowy-raport-kwartalny.html?offset=0
b = any_name_01
With Sheets("query").QueryTables.Add(Connection:=a,
Destination:=Sheets("query").Range("A1"))
.Name = b
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False 'ERROR LINE ???
End With
========
month ago this module worked perfectly (in loop) - this time something from
the site may cause this crash.
could you create and check web query via VBA and commonly via query creator
and pull data from the link:
(link is safe, it is Polish daily financial e-bulletin)
http://www.bankier.pl/inwestowanie/...FI-jednostkowy-raport-kwartalny.html?offset=0

i do not have idea what may cause "application shutdown and windows error
reporting"

regards

 

[God Itself]

Hi,

1. " " are already used.

2. i know the fix with "xlSpecifiedTables" but there is only 1 available table from the given link.

try:

.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1"

and excel still crashes...

The problem is that i cannot use download data from the URL while i could download the same data previously (month ago).
Might they have changed on the site to prevent from data downloading?

 

[Don Guillett]

Sent a file to your address shown
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi,

1. " " are already used.

2. i know the fix with "xlSpecifiedTables" but there is only 1 available
table from the given link.

try:

.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1"

and excel still crashes...

The problem is that i cannot use download data from the URL while i could
download the same data previously (month ago).
Might they have changed on the site to prevent from data downloading?