Excel Not Closing 1

[mtompkins]

I'm read many of the threads on Excel not closing - and just can't get it to sort my coding...

Public Function dataExcel(Item As String, Topic As String)

Dim oExcel As Excel.Application Dim objActiveWkb As Excel.Workbook
Dim objActiveSht As String

Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oExcel.Workbooks.Open "C:\GoldenWaves\mktData.xls"
objActiveSht = "Sheet1"
    Set objActiveWkb = oExcel.ActiveWorkbook
    oExcel.Worksheets(1).Activate
    objActiveWkb.Worksheets(objActiveSht).Cells(2, 6).Value = Topic
    objActiveWkb.Worksheets(objActiveSht).Cells(2, 7).Value = Item
    RANGE("A1:E301").Select
    Selection.ClearContents
    objActiveWkb.Application.Run ("updateBars")
    objActiveWkb.Worksheets(objActiveSht).Cells(2, 6).Value = ""
    objActiveWkb.Worksheets(objActiveSht).Cells(2, 7).Value = ""
    objActiveWkb.Worksheets(objActiveSht).Cells(1, 1).Value = "DATE"
    objActiveWkb.Close savechanges:=True
oExcel.Quit
Set objActiveSheet = Nothing
Set objActiveWkb = Nothing

Set oExcel = Nothing

End Function

Any help in sorting this would be greatly appreciated.
The code can only run once as Excel isn't closing properly and that is causing difficulty.

 

[RoyVidar]

You may find some explanations here Excel automation fails second time code runs.

To be explicit - you have some implicit referencing here. Both the Range object and the Selection object is left unqualified, and is the most likely culprit. Prefix with the application object:

[tt]oExcel.RANGE("A1:E301").Select
oExcel.Selection.ClearContents[/tt]

Though I'm more a fan of also using a worksheet object, and referencing throuhg that:

[tt]set wrs = objActiveWkb.Worksheets("Sheet1")
wrs.RANGE("A1:E301").Select
oExcel.Selection.ClearContents[/tt]

I'm also making sure my naming convention doesn't conflict with reserved words etc, Item is a property lot's of objects, but I'm not sure it's creating any problems here. My function declaration would probably look something like his:

[tt]Public Function dataExcel(Byval v_strItem As String, _
Byval v_strTopic As String) as <some datatype>[/tt]

Since you're not returning anything, consider redusing the overhead by making it a sub?

Roy-Vidar

 

[mtompkins]

Thanks Roy -

I totally overlooked those two lines, and they were the culprit.

Coding at 2am to get the job done ... happens.

Thanks for the assist.