Open/Close an Excel from Outlook Macro.

Question

I have to open and close an excel workbook from a Outlook Macro. 

The below code is not closing the excel application, instead throwing errors as well as leaving all the excel applications open when each time this function is called. Can some one help me clean the code.  

Regards,

Diwakar

=============================

Function GetConfigItem(FileName As String, ConfigItem As String) As String

    Dim xlApp As Excel.Application

    Dim xlWorkbooks As Excel.Workbooks

    Dim xlSheet As Excel.Worksheet

    Dim ExcelRunning As Boolean

    Dim iCount As Integer

    

    On Error GoTo ErrGetConfigItem

    

'    If ExcelRunning = IsExcelRunning Then

'        Set xlApp = GetObject(, Excel.Application)

'    Else

'        Set xlApp = CreateObject("Excel.Application")

'    End If

    

'    If Not xlApp = GetObject(, "Excel.Application") Is Nothing Then

        Set xlApp = CreateObject("Excel.Application")

'    End If

    

    xlApp.Workbooks.Open (FileName)

    Set xlSheet = ActiveSheet

    iCount = 1

    Do While Cells(iCount, 1).Value <> ""

        If Cells(iCount, 1).Value = ConfigItem Then

           GetConfigItem = Cells(iCount, 2).Value

        End If

    iCount = iCount + 1

    Loop

    '=====================================================================================

    '

    '   The config file is still open. The Excel.exe will run even after the app is done.

    '   Code to be added to close !!!

    '   CRACK: go to task manager and close Excel.exe at the end.

    '

    '=====================================================================================

    If IsExcelRunning() Then

        xlApp.Workbooks.Close

        Set xlApp = Nothing

    End If

ErrGetConfigItem:

    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number

    Exit Function

End Function

=============================

Answer 1

Hi Diwakar Killamsetty,

Since marco in Office is related to Visual Basic for Applications, I'll move this thread to VBA forum for better support.


Best Regards
Yichun Feng

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Below code modifications worked:

Regards,

Diwakar

=========================================

Function GetConfigItem(FileName As String, ConfigItem As String) As String

    Dim xlWorkbook As Excel.Workbook

    Dim xlSheet As Excel.Worksheet

    Dim iCount As Integer

    

    On Error GoTo ErrGetConfigItem

    

    Set xlWorkbook = GetObject(FileName)

    If Not xlWorkbook Is Nothing Then

        Set xlWorkbook = CreateObject(FileName)

    End If

    

    Set xlSheet = xlWorkbook.ActiveSheet

    iCount = 1

    Do While xlSheet.Cells(iCount, 1).Value <> ""

        If xlSheet.Cells(iCount, 1).Value = ConfigItem Then

           GetConfigItem = xlSheet.Cells(iCount, 2).Value

        End If

    iCount = iCount + 1

    Loop

        

    xlWorkbook.Close SaveChanges:=False

    Set xlWorkbook = Nothing

    Set xlSheet = Nothing

     

    Exit Function

ErrGetConfigItem:

    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number

    Err.Clear

    

    xlWorkbook.Close SaveChanges:=False

    Set xlWorkbook = Nothing

    Set xlSheet = Nothing

    Exit Function

End Function

=========================================