CODEWITHSUNDEEP
excelvbaoutlook
NAlexP
NAlexP

Reputation: 183

How to open Excel if not already open

I'm trying to find a way to open Excel using Outlook VBA, but only if it's not already open. I managed to find some code on the internet that opens Excel, does changes and then closes it, but it doesn't behave nicely if the Excel workbook is already open(it does apply the changes, but it no longer closes the Excel workbook, and it simply leaves it with a grey interior; also, sometimes it doesn't even show in the explorer anymore and I have to close it from the task manager). I would also greatly appreciate if someone could explain what most of the code does.

Public xlApp As Object
Public xlWB As Object
Public xlSheet As Object
Sub ExportToExcel()
    Dim enviro As String
    Dim strPath As String
     'Get Excel set up
enviro = CStr(Environ("USERPROFILE"))
'the path of the workbook
 strPath = enviro & "\Documents\test2.xlsx"
     On Error Resume Next
     Set xlApp = GetObject(, "Excel.Application")
     If Err <> 0 Then
         Application.StatusBar = "Please wait while Excel source is opened ... "
         Set xlApp = CreateObject("Excel.Application")
         bXStarted = True
     End If
     On Error GoTo 0
     'Open the workbook to input the data
     Set xlWB = xlApp.Workbooks.Open(strPath)
     Set xlSheet = xlWB.Sheets("Sheet1")
    ' Process the message record
    On Error Resume Next
     xlWB.Close 1
     If bXStarted Then
         xlApp.Quit
     End If
End Sub

I know what the xlWb and xlSheet objects do, and how they're declared, and I also understand what the environ function and strPath string do, but I don't undestand why we need the bXStarted boolean, what Set xlApp = GetObject does, why the Application.StatusBar message doesn't get displayed, the difference between GetObject and CreateObjectand why so many error tests are needed. Thanks in advance.

Upvotes: 0

Views: 661

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

The difference between get object and create object is in the title, one will get open excel.application, if there is an error err<>0 then it creates an excel.application. I think you'll be getting a saveas message, as the file may not be saving, but open, and you're instructing it to save, the error resume next is skipping it . Try saving before just a .close If you remove the on error resume next the error wont be skipped and will be shown.

Sub explaination()

Dim blnDidICreateExcel As Boolean           '   Please read MSDN on boolean
Dim objToHoldExcelCreatedOrNot As Object    '   Please read MSDN on objects create/get

'   Does the user have Excel open, if i try to get it, then there will be an error logically if not
Set objToHoldExcelCreatedOrNot = GetObject(, "Excel.Application")

'   Was there an error
If Err <> 0 Then
    '   There was, so i need to create one
    Set objToHoldExcelCreatedOrNot = CreateObject("Excel.Application")
    blnDidICreateExcel = True       '   Yes, i created it
End If

'   Do the neccessary

'   CLose the workbook

'   Did i create this Excel, if so tidy up

If blnDidICreateExcel Then objToHoldExcelCreatedOrNot.Quit

End Sub

Upvotes: 1

Related Questions