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