Solved

Access VBA close excel but still exists in task manager

Posted on 2010-08-23
18
680 Views
Last Modified: 2012-06-27
Hi,

I cannot seem to close Excel propery when running the sub below from Access. It appears to close Excel but there is still an excel process running in the task manager.  Should point out that objXL is a global variable and I have declared it as an object and excel.application but the result is the same.

Any help would be great as always.

Thanks,

Mark

Sub ImportIndexData(AnalystFiles() As TypeAnalystFile)

Dim i As Integer
Dim SheetKey As String
Dim LRow As Long, HRow As Integer
Dim wb As Excel.Workbook, wbTemp As Excel.Workbook

strSQL = "SELECT Passwords.* " & _
            "FROM Passwords;"

Intilise_Connection

    Set rs = New ADODB.Recordset
    rs.Open strSQL, cn, adOpenKeyset
    SheetKey = rs![Password]
    rs.Close
    
Set objXL = CreateObject("Excel.Application")

With objXL.Application
    
    .Visible = True
    .AddIns("Bloomberg Excel Tools").Installed = False
    .AddIns("Bloomberg Excel Tools").Installed = True
    .DisplayAlerts = False
    
    Set wb = OpenExcelFile("G:\Shared\Fixed Income\Sovereign\Mark\Projects\Trade Rec.xls", FullAccess)
    
        If wb Is Nothing Then
            ProblemLog "Trade Recommendation file could not be opened, please make sure that no other user has it currently opened"
            Exit Sub
        End If
        
    For i = 1 To UBound(AnalystFiles)
        
        'filecopy()
        Set wbTemp = OpenExcelFile(AnalystFiles(i).Path & AnalystFiles(i).FileName, ReadOnly)
            
            If wbTemp Is Nothing Then
                ProblemLog AnalystFiles(i).Path & AnalystFiles(i).FileName & " not imported into the trade recommendation file, please check"
            Else
                
                DoCmd.SetWarnings False
                strSQL = "DELETE " & AnalystFiles(i).AccessTable & ".* " & _
                        "FROM " & AnalystFiles(i).AccessTable & ";"
                        
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
                
                wb.Activate
                .Sheets(AnalystFiles(i).SheetPaste).Unprotect SheetKey
                .Sheets(AnalystFiles(i).SheetPaste).Range("A" & AnalystFiles(i).HeaderRow + 1 & ":IV65536").ClearContents
                
                wbTemp.Activate
                .DisplayAlerts = False
                
                    'text to columns
                    If AnalystFiles(i).TextToColumns = True Then _
                            .Columns("A:A").TextToColumns .Range("A1"), xlDelimited, xlDoubleQuote, False, False, False, True
                                           
                    .Rows("1:" & (AnalystFiles(i).HeaderRow) - 1).Delete Shift:=xlUp
                    LRow = xlMove_Up(65536, 1)
                    .Rows(LRow & ":" & LRow).Delete
                    wbTemp.SaveAs AnalystFiles(i).Path & AnalystFiles(i).FileName & "_Delete.xls"
                            
                    'import data into access table to get over excel bug of truncanted picture to large
                    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                AnalystFiles(i).AccessTable, AnalystFiles(i).Path & AnalystFiles(i).FileName & "_Delete.xls", True
                    
                    strSQL = "SELECT " & AnalystFiles(i).AccessTable & ".* " & _
                                "FROM " & AnalystFiles(i).AccessTable & ";"
   
                    rs.Open strSQL, cn, adOpenKeyset
                            
                wb.Activate
                .Sheets(AnalystFiles(i).SheetPaste).Select
                .Cells(AnalystFiles(i).HeaderRow + 1, 1).CopyFromRecordset rs
                rs.Close
                
                'protect sheet
                .Sheets(AnalystFiles(i).SheetPaste).Protect Password:=SheetKey, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
                
                wbTemp.Close False
                Kill AnalystFiles(i).Path & AnalystFiles(i).FileName & "_Delete.xls"
            End If
    Next

    .Sheets("Total").Select
    wb.Save
    wb.Close
    .DisplayAlerts = True
    
cn.Close
Set wb = Nothing
Set wbTemp = Nothing

errHandler:
    If Err.Number <> 0 Then
        ProblemLog "Error number: " & Err.Number & "    Error Description: " & Err.Description & vbCrLf
            If (i - 1) > 0 Then
                .Sheets(AnalystFiles(i - 1).SheetPaste).Protect Password:=SheetKey, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
                ProblemLog "Error importing " & AnalystFiles(i - 1).Path & AnalystFiles(i - 1).FileName
            End If
    End If

End With

objXL.Application.Quit
Set objXL = Nothing

Set rs = Nothing
Set cn = Nothing

End Sub

Open in new window

0
Comment
Question by:mcs26
  • 7
  • 7
  • 2
  • +2
18 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33500765
You haven't posted the code for any of your called functions/subs, but I suspect that the TransferSpreadsheet is your issue. Have you tried closing the temp workbook before you do the import?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33500865
I'd be surprised if TransferSpreadsheet was the cause; that is a usually well-behaved method from Access.Are you 100% certain that there were no Excel processes running before you executed this code?  This code will create a new instance of Excel, so if there were already any Excel processes running before the code executed, those instances would be left intact.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33500883
"that is a usually well-behaved method from Access"
on an open spreadsheet?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33500917
Rory,Good point!  I had missed that the target file was actually open.  That is, shall we say, inadvisable :)Patrick
0
 

Author Comment

by:mcs26
ID: 33500953
Hi,

Thanks for the replies. I have tried closing the workbook before the TransferSpreadsheet and debugs with the message "External table is not in the expected format".

I have not posted the other subs/functions as they do not reference the excel application.

I have closed all the excel process in the task manager before I run my code and once it has finished running there is one excel process in the task manager.

Thanks,
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33500976
How do OpenExcelFile and xlMove_Up not reference Excel?
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33501001
try changing:
objXL.Application.Quit

to:
objXL.Quit
objXL is already set to Excel.Application, which the prior code would make it "Excel.Application.Application.Quit"
Not sure if it makes a difference but worth a shot
0
 

Author Comment

by:mcs26
ID: 33501015
Good point!

[code]
Function OpenExcelFile(FileToOpen As String, AccessType As EnumOpenExcel) As Excel.Workbook

With objXL.Application

    If AccessType = FullAccess Then
   
        On Error Resume Next
            Set OpenExcelFile = .Workbooks.Open(FileName:=FileToOpen, UpdateLinks:=3, _
                                                            ReadOnly:=False, IgnoreReadOnlyRecommended:=True)
        If Err <> 0 Then
            On Error GoTo 0
            Set OpenExcelFile = Nothing
            ProblemLog FileToOpen & " workbook could not be opened."
        Else
            If .ActiveWorkbook.ReadOnly = True Then
                OpenExcelFile.Close False
                Set OpenExcelFile = Nothing
                ProblemLog FileToOpen & " is currently open. Either another user or yourself has the file open, please close. Code will now exit."
            End If
        End If

    ElseIf AccessType = ReadOnly Then
       
        On Error Resume Next
            Set OpenExcelFile = .Workbooks.Open(FileName:=FileToOpen, UpdateLinks:=3, _
                                                            ReadOnly:=True)
        If Err <> 0 Then
            On Error GoTo 0
            Set OpenExcelFile = Nothing
            ProblemLog FileToOpen & " workbook could not be opened."
        End If
   
    ElseIf AccessType = ReadOnlyNoUpdate Then
       
        .DisplayAlerts = False
        On Error Resume Next
            Set OpenExcelFile = .Workbooks.Open(FileName:=FileToOpen, UpdateLinks:=2, _
                                                            ReadOnly:=True)
        If Err <> 0 Then
            On Error GoTo 0
            Set OpenExcelFile = Nothing
            ProblemLog FileToOpen & " workbook could not be opened."
        End If
        .DisplayAlerts = True
       
    End If

End With

End Function

'***********************************************************************************************************************

Function xlMove_Up(RowNum As Long, ColNum As Integer)

With objXL.Application
        .Range(.Cells(RowNum, ColNum), .Cells(RowNum, ColNum)).End(xlUp).Select
        xlMove_Up = .ActiveCell.Row
End With

End Function
[/code]

0
 

Author Comment

by:mcs26
ID: 33501057
tried changing objXL.Application.Quit to objXL.Quit but still the same result.

Thanks
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33501093
Those both look OK, so I still think it's the fact you are using TransferSpreadsheet on an open workbook. If you are reunning this code in 2007, changing line 65 to:
wbTemp.SaveAs AnalystFiles(i).Path & AnalystFiles(i).FileName & "_Delete.xls", 56


and then close the workbook before doing the import.
0
 

Author Comment

by:mcs26
ID: 33501156
Ok, I'm just trying to close the workbook before the TransferSpreadsheet but it only works if the file is open for some reason. When the file is close it debugs saying "External table is not in the expected format"?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33501214
Did you change the saveas line?
0
 

Author Comment

by:mcs26
ID: 33501234
Sorry no we only have access 2000 at my work place!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33501252
Are you running Excel 2000 too?
0
 

Author Comment

by:mcs26
ID: 33501262
yeah excel 2000
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33501295
No idea then I'm afraid. It would have made sense if you have Excel 2007 installed.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33512248
Are you still having problems with a 'ghost' instance of Excel persisting?

If so it might be worth taking a look at the original code, in particular how you are referencing (or not referencing) things.

In the code here what workbook is meant to being referenced for the Sheets?
[code]
wb.Activate
.Sheets(AnalystFiles(i).SheetPaste).Unprotect SheetKey
.Sheets(AnalystFiles(i).SheetPaste).Range("A" & AnalystFiles(i).HeaderRow + 1 & ":IV65536").ClearContents
[/code]
Activating wb isn't going to ensure it's going to be referenced.

As far as I can see the reference will go all the way back to the only With in the code.
[code]
With objXL.Application
[/code]
You should also try to avoid using Select and Activate in this code and Excel VBA.

For the above code you could simply add wb.
[code]

wb.Sheets(AnalystFiles(i).SheetPaste).Unprotect SheetKey
wb.Sheets(AnalystFiles(i).SheetPaste).Range("A" & AnalystFiles(i).HeaderRow + 1 & ":IV65536").ClearContents[/code]
0
 

Accepted Solution

by:
mcs26 earned 0 total points
ID: 33540596
Hiya,

To get round this problem I have saved the excel file that I was importing into my table into a csv file and everything works fine now, no ghost excels in my task manager!

Thanks for the help
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now