I have the following VB 6 code (w/Office 2000) which is to load an XLS file and create a chart of the data in column F, rows 10 to 85. When I launch the program and run the SubProgram once it works, but if I try to run it a second time I get the following error message:
Run-time error '1004' Method 'Charts' of object '_Applicaiton' failed. What am I missing??
Private Sub Form_Click()
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlWrkSht As Excel.Worksheet
Dim xlChart As Excel.Chart
Dim xlRange As Excel.Range
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWkb = xlApp.Workbooks.Open("C:\Summary.xls")
Set xlWrkSht = xlWkb.Sheets(1)
Set xlRange = xlWrkSht.Range("F10:F85")
Set xlChart = Application.Charts.Add
With xlChart
.ChartType = xlLine
.SetSourceData Source:=xlRange
.Location Where:=xlLocationAsObject, Name:=xlWrkSht.Name
End With
xlApp.Application.SaveWorkspace "C:\Summary1.xls"
xlApp.Workbooks.Close
Set xlRange = Nothing
Set xlChart = Nothing
Set xlWrkSht = Nothing
Set xlWkb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
http://www.pcreview.co.uk/forums/thread-980201.php