I'm opening a new Excel spreadsheet with CSV data that includes stock
prices. I've created a sample macro here, which is suppose to create
a graph with an overlay of additional data (a 5 day moving average).
I realize this can be done on one chart at the same time, but I'm
using this as an example of something more complex I'm working on.
The trouble is, when I go to open another CSV data file and run the
macro, I get stopped out at the activechart.setsourcedata
source:=Sheets("AAA") <- the name of the csv file.
The range will always be the same. Is there another macro where I can
have a window pop up asking me to select a particular stock symbol
from a folder of data? And how would I change the code to incoporate
that? Here is a sample of the code I have thus far, below:

Sub FivedayMaSample()
'
' FivedayMaSample Macro
' Macro recorded 7/27/2004 by HAL
'

'
Columns("A:A").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("A1:A25,E1:E25").Select
Range("E1").Activate
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData
Source:=Sheets("AAA").Range("A1:A25,E1:E25"), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Close"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasDataTable = False
Sheets("AAA").Select
Range("A1:A25,H1:H25").Select
Range("H1").Activate
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
ActiveChart.SeriesCollection(2).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(2).AxisGroup = 2
ActiveChart.PlotArea.Select
End Sub

Bob -

Here's a simple function that opens a dialog to help you select a CSV file

Function GetCSVFile()
' includes path and file name, or "False" if user cancels
Dim sFileName As String
sFileName = Application.GetOpenFilename("CSV files (*.csv),*.csv", _
, "Select a CSV File")
GetCSVFile = sFileName
End Function

Call it like this from your main program:

'...
Dim MyFileName As String
MyFileName = GetCSVFile
If MyFileName = "False" Then Exit Sub
Workbooks.Open MyFileName
'...

I would put this early in your macro. Then I'd define a range when the
CSV file is still the active sheet:

Dim rngChart as Range
Set rngChart = ActiveSheet.Range("A1:A25,E1:E25")

Delete these two lines:

Range("A1:A25,E1:E25").Select
Range("E1").Activate

and this one:

ActiveChart.Location Whe=xlLocationAsNewSheet

and make a minor mod to this group:

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=rngChart, _
PlotBy:=xlColumns

and proceed with the rest of your macro.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Bob R wrote:

I'm opening a new Excel spreadsheet with CSV data that includes stock
prices. I've created a sample macro here, which is suppose to create
a graph with an overlay of additional data (a 5 day moving average).
I realize this can be done on one chart at the same time, but I'm
using this as an example of something more complex I'm working on.
The trouble is, when I go to open another CSV data file and run the
macro, I get stopped out at the activechart.setsourcedata
source:=Sheets("AAA") <- the name of the csv file.
The range will always be the same. Is there another macro where I can
have a window pop up asking me to select a particular stock symbol
from a folder of data? And how would I change the code to incoporate
that? Here is a sample of the code I have thus far, below:

Sub FivedayMaSample()
'
' FivedayMaSample Macro
' Macro recorded 7/27/2004 by HAL
'

'
Columns("A:A").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("A1:A25,E1:E25").Select
Range("E1").Activate
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData
Source:=Sheets("AAA").Range("A1:A25,E1:E25"), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Close"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasDataTable = False
Sheets("AAA").Select
Range("A1:A25,H1:H25").Select
Range("H1").Activate
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
ActiveChart.SeriesCollection(2).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(2).AxisGroup = 2
ActiveChart.PlotArea.Select
End Sub

Much thanks to all for the great information.

Bob R

(Bob R) wrote in message om...
I'm opening a new Excel spreadsheet with CSV data that includes stock
prices. I've created a sample macro here, which is suppose to create
a graph with an overlay of additional data (a 5 day moving average).
I realize this can be done on one chart at the same time, but I'm
using this as an example of something more complex I'm working on.
The trouble is, when I go to open another CSV data file and run the
macro, I get stopped out at the activechart.setsourcedata
source:=Sheets("AAA") <- the name of the csv file.
The range will always be the same. Is there another macro where I can
have a window pop up asking me to select a particular stock symbol
from a folder of data? And how would I change the code to incoporate
that? Here is a sample of the code I have thus far, below:

Sub FivedayMaSample()
'
' FivedayMaSample Macro
' Macro recorded 7/27/2004 by HAL
'

'
Columns("A:A").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("A1:A25,E1:E25").Select
Range("E1").Activate
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData
Source:=Sheets("AAA").Range("A1:A25,E1:E25"), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Close"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasDataTable = False
Sheets("AAA").Select
Range("A1:A25,H1:H25").Select
Range("H1").Activate
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
ActiveChart.SeriesCollection(2).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(2).AxisGroup = 2
ActiveChart.PlotArea.Select
End Sub