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.
--
John
johnf202 at hotmail dot com
"Tom Ogilvy" wrote in message
...
That didn't work with his URL
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://direct.srv.gc.ca/cgi-bin/direct500/REcn%3dAbraham%5c%2c%20Michael%2c
ou%3dMC2586-MC2586%2cou%3dMC-MC%2cou%3dOPS-OPS%2cou%3dASCDM-SMD%c9L%2cou%3dD
M-SM%2cou%3dNCR-RCN%2cou%3dIC-IC%2co%3dGC%2cc%3dCA"
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
A shorter URL at the same site does work - and it works with his code as
well.
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://direct.srv.gc.ca/cgi-bin/direct500/
SEo%3dGC%2cc%3dCA?SV=Abraham%2C+M
ichael&SF=Surname%2C+Given+name&ST=exact&x=43&y=13 "
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
worked fine.
His question has to do with overcoming the length limitation.
Regards,
Tom Ogilvy
"Don Guillett" wrote in message
...
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
--
Don Guillett
SalesAid Software
Granite Shoals, TX
"DM" wrote in message
...
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/dire...Ecn%3dAbraham%
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.
-----Original Message-----
Why is your url so long?
How are you using the url?
can you post it? and your code?
--
Don Guillett
SalesAid Software
Granite Shoals, TX
"DM" wrote in message
...
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?
.