[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[_4_]]

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?

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


.

[Don Guillett[_4_]]

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?


.

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

--

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?


.

[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


Don Guillett wrote in message
...
I know Tom. I tried it. Also, as I said, I went to the site and couldn't
determine how he got to that url?

--
Don Guillett
SalesAid Software
Granite Shoals, TX

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


.