VBA Using Outlook to open excel not opening

Question

Hi Thanks for your help.

I have the follow code in Outlook 2010 & 2007:

Sub Openexcel()
    Dim xlApp As Object
    Dim sourceWB As Workbook
    Dim sourceSH As Worksheet
    Dim strFile As String
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
        .EnableEvents = False
    End With

    strFile = "E:\All documents\susan\work\Excel projects\saving files to directory Clean.xls"

    Set sourceWB = Workbooks.Open(strFile, , False, , , , , , , True)
    Set sourceSH = sourceWB.Worksheets("Sheet2")
    sourceWB.Activate
End Sub

This code works the first time I use it after opening outlook but if I then close the excel file I can not use it again. I need to reopen this work book about 3 times

The Question at
Outlook VBA open excel seen to have the same problem but I did not understand the answer.

"I got it figured out. I was opening a different workbook and then closing it before I try to open the second one and that was interfering with it. To fix this I kept the excel app open and reset the workbook object to the new workbook i wanted"

If some someone could help with the additional code that would be great.

Answer 1

You need to declare the Excel Applicaton at the global scope and use it to open another workbooks. Not to create a new Excel instance for opening new files. You may find the How to automate Microsoft Excel from Visual Basic article helpful.

For example, declare the Application object outside the event handler:

Dim oXL As Excel.Application

Private Sub Command1_Click()
   Dim oWB As Excel.Workbook

Thus, you will be able to re-use it for closing and opening new workbooks.

Answer 2

Great code found at Excel interactions don't work after Excel file opened.

why I could not find this last week who knows.

Sub Openexcel()
' change
  Dim xlApp As Excel.Application
  Dim sourceWB As Excel.Workbook
  Dim sourceSH As Excel.Worksheet
'change
  Set xlApp = New Excel.Application
      With xlApp
      .Visible = True
      .EnableEvents = False
      '.UserControl = False
       '.DisplayAlerts = False
       '.AskToUpdateLinks = False

End With

strFile = "E:\All documents\susan\work\Excel projects\saving files to directory Clean.xls"
'change
Set sourceWB = xlApp.Workbooks.Open(strFile, , False, , , , , , , True)
Set sourceSH = sourceWB.Worksheets("Sheet2")
sourceWB.Activate

End Sub

Thanks guys for all your thoughts.