Passing File Name when creating ChartSeriesCollection

[MRTX7005]

When I create the Chart on a worksheet, the Excel file name is being used in creating the Chart SeriesCollection. I am struggling with figuring out how to pass the Excel file name as a variable when creating the chart. I want the user to be able to call the Excel file whatever they want vs what I programmed the first time.

The Excel file is currently called "Electric_DCApp". This is what I want to make a variable instead.

Charts.Add
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Sheets("Dashboard").Range("A1")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=Electric_DCApp.xlsm!ChtTime"
    ActiveChart.SeriesCollection(1).Values = "=Electric_DCApp.xlsm!ChtDSRPM"
    ActiveChart.SeriesCollection(1).Name = "=Data!R1C8"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Dashboard"

I have tried using something like:

Dim fName As String
fName = ThisWorkbook.FullName

Then replaced the "=Electric_DCApp.xsm!ChtTime" with "=fName!ChtTime" as an example but that does not work.

Any help would be appreciated.
Mike

 

[DanteAmor]

Try this

ActiveChart.SeriesCollection(1).XValues = "='" & fName & "'!ChtTime"

 

[MRTX7005]

That worked. I never would have gotten the syntax correct.

Appreciate it.

Mike

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.