g'afternoon; it has been awhile since i've posted; but i am stuck as i am not familar with macros and queries.
I downloade a file from the web which pulls stock data from Yahoo.
when I open the file I get a message;
previously saved in an earlier version of Excel. "the following data may have been lost" -query table
when I try to run query, I get"run time error 13; "type mismatch"
debug takes me to Module 1 Dim Pic as String and highlights
Pic= Picture & Range ("Y5")
is there something I can do to get this thing running?
I am using Excel 2003; the results come back ##NUM
below is the full code
Thanks in advance, 2rrs
EDIT - Added Code tags - Moderator
I downloade a file from the web which pulls stock data from Yahoo.
when I open the file I get a message;
previously saved in an earlier version of Excel. "the following data may have been lost" -query table
when I try to run query, I get"run time error 13; "type mismatch"
debug takes me to Module 1 Dim Pic as String and highlights
Pic= Picture & Range ("Y5")
is there something I can do to get this thing running?
I am using Excel 2003; the results come back ##NUM
below is the full code
Thanks in advance, 2rrs
Code:
Sub GetData()
' thanks to Ron McEwan :^)
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim EndDate As Date
Dim StartDate As Date
Dim Symbol As String
Dim qurl As String
Dim nQuery As Name
Application.ScreenUpdating = True
Application.DisplayAlerts = False
' Application.Calculation = xlCalculationManual
Set DataSheet = ActiveSheet
StartDate = DataSheet.Range("B2").Value
EndDate = DataSheet.Range("B3").Value
Symbol = DataSheet.Range("B4").Value
Range("C7").CurrentRegion.ClearContents
'construct the URL for the query
qurl = "http://chart.yahoo.com/table.csv?s=" & Symbol
qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
"&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("C3") & "&q=q&y=0&z=" & _
Symbol & "&x=.csv"
Range("b5") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Range(Range("C7"), Range("C7").End(xlDown)).NumberFormat = "mmm d/yy"
Range(Range("D7"), Range("G7").End(xlDown)).NumberFormat = "0.00"
Range(Range("H7"), Range("H7").End(xlDown)).NumberFormat = "0,000"
Range(Range("I7"), Range("I7").End(xlDown)).NumberFormat = "0.00"
With ThisWorkbook
For Each nQuery In Names
If IsNumeric(Right(nQuery.Name, 1)) Then
nQuery.Delete
End If
Next nQuery
End With
'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Range("C7:I2000").Select
Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C1").Select
Selection.ColumnWidth = 12
UpdateScale
TrafficLight
Range("B4").Select
End Sub
Sub UpdateScale()
Dim ChartVar As chart
Dim lMax As Long, lMin As Long
On Error GoTo ScalingProblem
'Assigns the values in the Min and Max ranges to variables.
With Sheet1
lMax = .Range("Max").Value
lMin = .Range("Min").Value
'Creates chart object.
Set ChartVar = .ChartObjects("Chart 48").chart
With ChartVar.Axes(xlValue, xlPrimary) 'Adjusts the price axis
.MinimumScale = lMin
.MaximumScale = lMax
End With
End With
Exit Sub
ScalingProblem:
RetrievalProblem:
' MsgBox "Unable to update chart scale.", vbCritical + vbOKOnly, "Scaling Error"
End Sub
Sub FourStocks()
'
' FourStocks Macro
' Macro recorded 7/24/06 by Ponzo
Dim i As Integer, j As Integer, stock As String, Pic As String
Sheets("4-Stocks").Select
Range("A2:AB300").Select
Selection.ClearContents
j = 2
For i = 2 To 5
Sheets("4-Stocks").Select
stock = Cells(1, i)
Sheets("1-Stock").Select
Application.ScreenUpdating = True
Range("F4") = "working on"
Range("G4") = stock
Range("B4") = stock
GetData
Range("N9:O300").Select ' get close and paste values
Selection.Copy
Sheets("4-Stocks").Select
Cells(2, j).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("1-Stock").Select ' get other stuff and paste formulas
Range("P9:S300").Select
Selection.Copy
' Range("A1").Select
Sheets("4-Stocks").Select
Cells(2, j + 2).Select
ActiveSheet.Paste
If i = 2 Then ' initially, get dates and paste values
Sheets("1-Stock").Select
Range("C9:C300").Select
Selection.Copy
' Range("A1").Select
Sheets("4-Stocks").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
j = j + 7
Sheets("1-Stock").Select
Range("F4") = ""
Range("G4") = ""
Range("A1").Select
' Pic = "Picture " & Range("Y5")
' ActiveSheet.Shapes(Pic).Select
' Selection.Copy
Sheets("4-Stocks").Select
' Cells(2, i).Select
' ActiveSheet.Paste
Next i
UpdateScales
Range("A1").Select
End Sub
Sub UpdateScales()
Dim ChartVar As chart
Dim lMax As Long, lMin As Long
On Error GoTo ScalingProblem
ActiveSheet.ChartObjects("Chart 2069").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("AN1")
.MaximumScale = Range("AM1")
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 2087").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("AN2")
.MaximumScale = Range("AM2")
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 2088").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("AN3")
.MaximumScale = Range("AM3")
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 2089").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("AN4")
.MaximumScale = Range("AM4")
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 66").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("AC6")
.MaximumScale = Range("AB6")
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ScalingProblem:
End Sub
Sub TrafficLight()
'
' TrafficLight Macro
' Macro recorded 7/25/2006 by Ponzo
'
Dim Pic As String
Pic = "Picture " & Range("Y5")
Range("Y6") = Pic
ActiveSheet.Shapes("Chart 48").Select
Selection.ShapeRange.ZOrder msoBringToFront
ActiveSheet.Shapes(Pic).Select
Selection.ShapeRange.ZOrder msoBringToFront
scaleChart
Range("G1").Select
End Sub
Sub VolWgt_y()
'
' VolWgt Macro
' Macro recorded 7/25/2006 by Ponzo
Range("P2") = "y"
TrafficLight
End Sub
Sub VolWgt_n()
'
' VolWgt Macro
' Macro recorded 7/25/2006 by Ponzo
Range("P2") = "n"
TrafficLight
End Sub
Sub Nil()
'
' Nil Macro
' Macro recorded 7/26/2006 by Ponzo
'
'
End Sub
Sub scaleChart()
On Error GoTo ScalingProblem
ActiveSheet.ChartObjects("Chart 66").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("AC6")
.MaximumScale = Range("AB6")
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ScalingProblem:
End SubEDIT - Added Code tags - Moderator
