1

I am trying to open an existing excel sheet from outlook. I can see the workbook open and then it imediately closes again. I have the Excel.Application set to visible. Any ideas? Here is the code. 

Function openNewForm(toDoSubject)
    MsgBox ("Called")
    Dim xlApp As Object
    Dim sourceWB As Workbook
    Dim sourceSH As Worksheet

    Set xlApp = CreateObject("Excel.Application")

    With xlApp
        .Visible = True
        .EnableEvents = False
    End With


    strFile = "C:\Users\Peter\Documents\ASI\OrderSystem\NewOrderSheet.xlsm"

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

Again this code is running in outlook. I want to keep the file open once it opens.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
loveforvdubs
  • 337
  • 4
  • 6
  • 9
  • 1
    I just tried this exact code in Outlook and it opened Excel just fine and kept it open (Office 2007). I called the function in the immediate window, maybe there's something else going on where the macro's being called from to interrupt it? – tordal Feb 23 '11 at 18:07
  • Weird that is frustrating. I cannot think of what would interrupt it. I put the code into its own module and I still have the same problem. Any ideas? – loveforvdubs Feb 23 '11 at 18:14
  • 1
    Why is this in a `Function`? Change it to a `Sub`. – RBarryYoung May 04 '13 at 21:42
  • See http://stackoverflow.com/questions/28970516/vba-using-outlook-to-open-excel-not-opening/ – Eugene Astafiev Mar 11 '15 at 10:17

1 Answers1

1

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.

loveforvdubs
  • 337
  • 4
  • 6
  • 9
  • 3
    If you have an answer to your question please mark it as the answer, even if you answered it yourself :) – JimmyPena Nov 09 '11 at 20:59