hyperlinks.follow result saving
 

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.

hyperlinks.follow result saving
 

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.

hyperlinks.follow result saving
 

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.

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.