Thread
:
Web Query
View Single Post
#
4
Posted to microsoft.public.excel.programming
Don Guillett[_4_]
external usenet poster
Posts: 2,337
Web Query
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?
.
Reply With Quote
Don Guillett[_4_]
View Public Profile
Find all posts by Don Guillett[_4_]