I am getting run time error 462.

Question

HI,

I have the following piece of code in Access.

Dim objSht As excel.Worksheet
Dim objexcel As New excel.Application
Dim wbexcel As excel.Workbook
Dim wbExists As Boolean
Dim objRange As excel.Range
Dim isFileAlreadyPresent As Boolean

Set objexcel = CreateObject("excel.Application")

Set wbexcel = objexcel.Workbooks.Open(file_name)
Set objSht = wbexcel.Worksheets(table_name)
isFileAlreadyPresent = True

objSht.Activate
objSht.Range(Range_para).Select
Charts.Add
ActiveChart.chartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets(table_name).Range(Range_para), _
                            PlotBy:= xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.HasLegend = False

With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.text = CHart_title
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = False
End With

If isFileAlreadyPresent = True Then
    wbexcel.Save
Else
    wbexcel.SaveAs (file_name)
End If
objexcel.Visible = True
wbexcel.Close

I am having two problems. Every second time I run the code I get an run time error 462 (The remote server machine does not exist or is unavailable ) at line Charts.add.

I know that I am not using the objexcel property correctly but I am not sure where I am going wrong.

Also after the code is run, even though excel closes. The process runs in the background and this interferes with the next run of the code. How do I close excel and get rid of it from task manager processes also?

Answer 1

Just as a quick fix trying setting everything you declare/use to nothing at the end of your code to ensure that nothing is left open or active.

Set objSht = Nothing

Let me know if that fixes the problem

Answer 2

I think you will need to create the chart object like this, since your using late binding it won't know what "Charts" is unless you call it from the parent object.

objexcel.Charts.Add

Error 462 usually means something isn't qualified right, even though the message is sort of cryptic.

Answer 3

As you your question

How do I close excel and get rid of it from task manager processes also?

You should be able to use the Application.Quit command at the end of your code as long as you aren't running the sub from another application other than Excel. Or, you should be able to do an objexcel.Quit command. Another alternative method is to delegate this to a shell command: Shell "taskkill /f /im excel.exe".

I hope this helps. Did you get a working piece of code yet?