Web Query

[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?

 

[Don Guillett]

Why is your url so long?
How are you using the url?
can you post it? and your code?

 

[DM]

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.

 

[Don Guillett]

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

 

[jaf]

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.

 

[Tom Ogilvy]

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