help with script for internet download

N

nastech

hi, I have a script used in a utility sheet to download 200 (190) lines or
less, that have to copy-paste results into main sheet, is there a way to
combine this with my main sheet. The first script is a utility for dates
used in my main sheet. thanks.
The problem is the rest of my sheet gets wiped out if try to use in main
sheet.
xxxxx

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Target.Row < 76 Then Exit Sub
If Me.Cells(.Row, "A").Value = "." Then Exit Sub
If Not Intersect(Me.Range("BQ:BR"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "BO")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


XXXXXXXXXXXXXXXXXXXXX


Sub GetData()

Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet

Range("C4").CurrentRegion.ClearContents
i = 4
qurl = "http://finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("A1")
'place string in cell:
Range("v1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range("C4"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range("C4").CurrentRegion.TextToColumns
Destination:=Range("C4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False

'turn calculation on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Columns("C:C").ColumnWidth = 5.14
'place cursor in cell:
Range("C4").Select

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top