Hello, Maybe somebody ran into this error and knows a solution. I am writing data to excel and then I plot it. This works fine. I then try to run the same routine again on a new excel sheet (I even close the other sheet) and that is when I get the following error.
Run-time Error '1004'
Method 'Location' of Object'_Chart' failed
Here is how I create the excel application:
---------------------------------------------------
Set oExcel = New Excel.Application
oExcel.Visible = True 'make Excel visible to the user
'open up a template sheet stored in the string variable "template"
oExcel.Workbooks.Open filename:=template
filepath = "C:\"
filename = "UnitNo_" & & "_" & Month(Date) & "_" & Day(Date) & "_" & Year(Date) & "_" & Hour(Time) & "_" & Minute(Time) & ".xls" oExcel.ActiveWorkbook.SaveAs filename:=filepath & filename
Set xlSh = oExcel.Workbooks(1).Worksheets(1)
---------------------------------------------------
Here is where I get the error msg while plotting (error occurs on the last line):
---------------------------------------------------
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("raw data").Range("K154")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='raw data'!R197C1:R223C1"
ActiveChart.SeriesCollection(1).Values = "='raw data'!R197C2:R223C2"
ActiveChart.SeriesCollection(1).Name = "='raw data'!R196C2"
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"plot1"
---------------------------------------------------
If I quit my program and restart it I don't get the error. So I looked at what I do when ending my program which is:
Set oExcel = Nothing
Set xlSh = Nothing
I tried adding that to the end of my plotting routine (to try to mimic what happens when I quit and restart but of course that doesn't work.
Quitting and restarting is annoying because I do a lot of setup stuff in the beginning that I would like to avoid redoing if I could. Does anyone know what I am doing wrong?
Thanks for any help.
Gregg
Method 'Location' of Object'_Chart' failed
Run-time Error '1004' Method 'Location' of Object'_Chart' failed
Linear Mode
