Web Query

  • Thread starter Thread starter DM
  • Start date Start date
D

DM

I am running into a problem with my web query. When ever
I attempt to run a web query with a url that is greater
than about 200 characters, the query fails.

There is a 218 character limit on the url length. Is
there anyway to modify this limit?
 
Why is your url so long?
How are you using the url?
can you post it? and your code?
 
The URL is so long because it is part of a large phone
directory.

I am using the url to get excel to query the page and copy
a table onto a worksheet. I visit several pages
individually, and run the macro for each one. All the
pages are formatted identically, but with different data.

Here is an example of a long url:
http://direct.srv.gc.ca/cgi-bin/direct500/REcn=Abraham%
5c%2c%20Michael%2cou%3dMC2586-MC2586%2cou%3dMC-MC%2cou%
3dOPS-OPS%2cou%3dASCDM-SMD%c9L%2cou%3dDM-SM%2cou%3dNCR-RCN%
2cou%3dIC-IC%2co%3dGC%2cc%3dCA

The code I am using is below:

Sub import()

'import Macro

Dim mybrowser As SHDocVw.InternetExplorer
Set mybrowser = GetObject(, "InternetExplorer.Application")
a = "URL;" & mybrowser.LocationURL
With ActiveSheet.QueryTables.Add(Connection:= _
a, Destination:=ActiveCell)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "2"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

There is more code, but this is where the problem occurs,
once final line of the with statement is executed.
 
I went to the web site and could not find any combination that would give me
that url.
You have to find a url that will work independently of excel and then modify
it to suit your needs.
Here is a sample that works. Feel free to send me a SMALL workbook to try.

Sub getit()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
'Set datasheet = Sheets("GetNumbers")
Set datasheet = ActiveSheet
With datasheet
Range("a3:a200").EntireRow.Delete
End With

qurl = "http://table.finance.yahoo.com/k?s=ibm&g=d"

With datasheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=datasheet.Range("B7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub
 
Might help if he gets rid of all the hex codes.
%3d=char(61) "="
%2c=char(44)= ,
%20=char(32)[space]
No idea what the "ou" is but there is a bunch of them.
 
I suspect he went to the top level query window and entered the person's
name. That is how I got the second URL since there were two entries for
this person. I didn't have any trouble getting it. It appears to be the
URL returned by selecting the bottom entry in the intermediate results.

Regards,
Tom Ogilvy
 
Back
Top