View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Mike Fogleman Mike Fogleman is offline
external usenet poster
 
Posts: 1,092
Default hyperlinks.follow result saving

Then use the Text Import method of external data:

Sub GetData()
Dim hypadr As String

hypadr = "http://example.microsoft.com"
With Worksheets("Sheet1").QueryTables.Add(Connection:=" TEXT;" & hypadr _
, Destination:=Worksheets("Sheet1").Range("C1"))
.Name = ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1 'import from this row
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True 'uses the comma
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1) 'need a 1 for each column
to import
.Refresh BackgroundQuery:=False
End With
End Sub

Mike F
"ezil" wrote in message
...
Thanks for your suggestion

My aim is to download .csv files. For that when I use web query method all
data get downloaded in single column and again i have to convert it into
multiple column. But if i use hyperlinks.follow method the result download
automatically
comes in multiple columns. So there is no need for conversion.

"Mike Fogleman" wrote:

You will want to do a Web Query. Much easier to get the data to a
specific
worksheet area.

Sub GetData()
Dim hypadr As String

hypadr = "http://example.microsoft.com"
With Worksheets("Sheet1").QueryTables.Add(Connection:=" URL;" & hypadr _
, Destination:=Worksheets("Sheet1").Range("C1"))
.Name = ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub

Mike F

"ezil" wrote in message
...
I am using "hyperlinks.follow" method to download data from website.
The example for this method shows saving the result in new window as

ActiveWorkbook.FollowHyperlink Address:="http://example.microsoft.com",
_
NewWindow:=True

But I require to paste or save the result in some range like (range c1
to
H1)
of the active worksheet. New window not required.
How to do this? Please help me.