|
Web query causes excel crash!
Hi, excel 2007 crashes while following web query (via VBA) is about to start:
======== a = URL;http://www.bankier.pl/inwestowanie/p....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/p....html?offset=0 i do not have idea what may cause "application shutdown and windows error reporting" regards |
Web query causes excel crash!
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/profile/wynikifinansowe/01NFI-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/profile/wynikifinansowe/01NFI-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 "God Itself" wrote in message ... Hi, excel 2007 crashes while following web query (via VBA) is about to start: ======== a = URL;http://www.bankier.pl/inwestowanie/p....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/p....html?offset=0 i do not have idea what may cause "application shutdown and windows error reporting" regards |
Web query causes excel crash!
Sent a file to your address shown -- Don Guillett Microsoft MVP Excel SalesAid Software "God Itself" wrote in message ... 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? Użytkownik "Don Guillett" napisał w wiadomości ... 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/profile/wynikifinansowe/01NFI-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/profile/wynikifinansowe/01NFI-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 |
| All times are GMT +1. The time now is 07:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com