Programmatically opening Excel followed by Workbooks.Open fails

[BCVolkert]

Hi,

I've been working on some VBA in MS Word to that requires access to information in an Excel workbook. I'm having trouble when Excel is closed before my procedure is called. I'd like to avoid dependence on the user opening Excel before the application is used; consequently, Excel may or may not be open at the time the Word application is used.

My current environment is Windows 7 Pro, MS Office 2010, all updates current. No compiler errors in VBE for Excel or Word.

With Excel open prior to running Test_OpenWorkbook, the code below successfully opens the workbook as intended.

However, if Excel is closed I've observed the following:

1. Prior to starting the function, I've assured that Excel is not running in the Task Manager.
2. When I run Test_OpenWorkbook, Excel is started by OpenWorkbook as intended.
3. A window in Excel does open showing MyWorkbook.xlsm but does not show any content in its window (no grid, no tabs, just the normal background color of a worksheet).
4. Output to the Immediate Window is:
   2018-06-01 10:01:53 ERROR - -196347 Method 'Open' of object 'Workbooks' failed(-2147417851)
   2018-06-01 10:01:53 FullPath = C:\Users\bvolkert\Documents\MyWorkbook.xlsm sOpenMode = Normal
   oEWKb is Nothing
5. Task Manager shows an Excel process running with the CPU at 25% while the status bar is showing CPU varying between 29% and 42% and Physical Memory varying between 39 % and 44%.
6. If I close the empty MyWorkbook window in Excel, Task Manager continues shows an Excel process running with the CPU at 25%.
7. I cannot close the Excel window normally; but, I can (and do) kill the process with Task Manager.

By stepping through the code, I know that the error triggering the ErrorHandler code is:
Set oEWkb = appExcel.Workbooks.Open(sFullPath).

After ending the Excel process in Task Manager: I can copy the constructed FullPath from the Immediate Window and paste it into the address for Windows Explorer and the file opens normally. The file has a lot of formulae that use VBA in my Personal.xlsb and it takes a few seconds for the calculating to end. This code appears to be functioning normally and I don't believe it should be any sort of contributing factor; but, may very well be.

I can open MyWorkbook.xlsm by all the normal methods (double-clicking, File / Open, etc.). So, I don't think there is anything wrong with the file other than not being small.

If I point the Function a small Workbook (14 KB), the Functions below work just fine. Opening the "real" Workbook (1358 KB) manually works pretty quickly; but, it does take a few seconds for the calculations to complete. I'm a bit concerned because I expect the "real" Workbook to grow substantially. (I'm using a workbook instead of a database because the users can deal with a workbook and will wig-out if I try getting them to be dependent on a database - that can come later.)

So, I think I'm looking for a solution that allows Excel to open a workbook in the face of all those calculations in the workbook. Since the VBA runs to the Workbooks.Open statement and does not get through it, I suspect I need something to prepare the environment for it to be successful or use some of the other arguments in the Workbooks.Open(. . . . . ) statement. I'm also open to changing the Workbooks.Open statement to something else; but, in the end, the workbook does need to be open.

I've tried the following:

• Repairing MS Office 2010
  
• Disabling all add-ins in Excel
• Not loading any Add-Ins in Word (that I know of)
• Restarting
• Shutdown
• Remove battery from laptop and cold-booting
• Latebinding = True and = False
• Adding delay statements (e.g. Sleep(5000) ) before and after the Workbooks.Open statement
• Closing one eye, tongue to left and to the right

Thanks for any direction or suggestions you can offer.

Sub Test_OpenWorkbook()


  #If  LateBinding Then
    Dim oEWkb As Object
  #Else
    Dim oEWkb As Excel.Workbook
  #End  If

  Dim sPath As String
  Dim sFile As String
  Dim sFullPath As String

  sPath = "C:\Users\bvolkert\Documents"
  sFile = "MyWorkbook.xlsm"
  sFullPath = sPath & "" & sFile


  Set oEWkb = OpenWorkbook(sFullPath, "Normal", "True") ' fails in OpenWorkbook
  If oEWkb Is Nothing Then
    Debug.Print "oEWKb is Nothing"
  Else
    Debug.Print oEWkb.Name
  End If

End Sub


Public Function OpenWorkbook(ByVal FullPath As String, _
  Optional ByVal sOpenMode As String = "Normal", _
  Optional ByVal bVisibilityIfExcelClosed As Boolean = True) As Object 

  ' Returns the workbook specified by FullName.
  '
  '     If Excel is open, OpenWorkbook uses the existing instance of Excel.
  '       The visibility of Excel is left unchanged.
  '
  '     If Excel is not open, a new instance is started.
  '       The visibility of Excel is set to bVisibilityIfExcelClosed.


  '
  '   The manner of opening the file is as indicated by the sOpenMode parameter.
  '     "Normal"
  '     "ReadOnly"
  '     "Repair"


  #If  LateBinding Then
    Dim appExcel As Object
    Dim oEWkb As Object
    Const xlExtractData As Long = 2 ' Workbook is opened in extract data mode.
    Const xlNormalLoad = 0 '  Workbook is opened normally.
    Const xlRepairFile = 1 ' Workbook is opened in repair mode.
  #Else
    Dim appExcel As Excel.Application
    Dim oEWkb As Excel.Workbook
  #End  If

  Dim sFullPath As String
  Dim bExcelWasRunning As Boolean
  Dim bWorkbookWasOpen As Boolean
  Dim bDebugging As Boolean

  bDebugging = True
  sFullPath = FullPath

  On Error Resume Next
  Set appExcel = GetObject(, "Excel.Application")
  If Err Then
    bExcelWasRunning = False
    Set appExcel = CreateObject("Excel.Application")
  Else
    bExcelWasRunning = True
  End If
  On Error GoTo 0

  DoEvents ' did not help

  On Error GoTo ErrorHandler


  If Not WorkbookIsOpen(sFullPath, oEWkb) Then
    Select Case sOpenMode
      Case "Normal"
        DoEvents
        appExcel.Visible = True
        DoEvents
        ' Fails on following step if Excel is closed before this function is called _
          (otherwise works).
        Set oEWkb = appExcel.Workbooks.Open(sFullPath) 
        ' Sleep (5000) ' this was a suggestion from another site and did not help
        DoEvents
      Case "ReadOnly"
        DoEvents
        Set oEWkb = appExcel.Workbooks.Open(sFullPath, ReadOnly:=True)
        DoEvents
      Case "Repair"
        DoEvents
        Set oEWkb = appExcel.Workbooks.Open(sFullPath, CorruptLoad:=xlRepairFile)
        DoEvents
    End Select
  End If
  appExcel.Visible = bVisibilityIfExcelClosed

  Set OpenWorkbook = oEWkb

ErrorExit:
  If oEWkb Is Nothing Then
    If bDebugging Then Debug.Print format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & _
      "FullPath = " & FullPath & "   sOpenMode = " & sOpenMode
  Else
    If bDebugging Then Debug.Print format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & _
      "oEWKB.Name = " & oEWkb.Name
    If bDebugging Then Debug.Print format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & _
      "OpenWorkbook.Name = " & OpenWorkbook.Name & " After Set oEWkb = Nothing"
  End If
  Set oEWkb = Nothing
  Set appExcel = Nothing
  Exit Function

ErrorHandler:
  Debug.Print format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " _
    ; "ERROR - " & CStr(Err.Number - vbObjectError) & "  " & Err.Description & "(" & CStr(Err.Number) & ")  "
  Resume ErrorExit
End Function


Public Function WorkbookIsOpen(ByVal FullPath As String, Optional ByRef OutputExcelWorkbook As Object = Nothing) As Boolean

  #If  LateBinding Then
    Dim appExcel As Object
    Dim oEWkb As Object
  #Else
    Dim appExcel As Excel.Application
    Dim oEWkb As Excel.Workbook
  #End  If

  Dim sFullPath As String
  sFullPath = LCase(FullPath)

  On Error Resume Next
  Set appExcel = GetObject(, "Excel.Application")
  If Err Then
    ' Excel was not running
    WorkbookIsOpen = False
    Set OutputExcelWorkbook = Nothing
    Exit Function
  End If
  On Error GoTo 0

  WorkbookIsOpen = False
  For Each oEWkb In appExcel.Workbooks
    If LCase(oEWkb.FullName) = sFullPath Then
      WorkbookIsOpen = True
      Set OutputExcelWorkbook = oEWkb
      Exit For
    End If
  Next
End Function

 

[RoryA]

If you start Excel programmatically, no startup workbooks will be loaded unless you used Shell to start Excel.

 

[BCVolkert]

Rory,

Wow. That was fast. I really appreciate it. I wish I had posted my question a week ago.

If I understand you correctly, programmatically starting Excel with CreateObject will not start my Personal.xlsb and consequently, the formulae in the spreadsheet are not getting any response from the functions in the Personal.xlsb. I'm a little new to the "Shell" game and want something that will be likely to work for anyone having MS Office installed.

Based on your suggestion, this seems to be working:

  On Error Resume Next
  Set appExcel = GetObject(, "Excel.Application")
  If Err Then
    bExcelWasRunning = False
    Shell ("Excel.exe")
    WaitAFewSeconds 3
    Set appExcel = GetObject(, "Excel.Application")
  Else
    bExcelWasRunning = True
  End If
  
  On Error GoTo ErrorHandler

Note: It did not work until I added the brief wait after the Shell statement. WaitAFewSeconds is provided below and does just what it seems to do. I also tried DoEvents etc. But no joy.

Public Sub WaitAFewSeconds(Optional ByVal Seconds As Long = 3, Optional ByVal ReportInterval As Long = 0)

  Dim vStart As Variant
  Dim lSecondsRemaining As Long
  Dim lReportTime As Long
  Dim lEndTime As Long
  Dim bReporting As Boolean
  
  vStart = Timer
  lEndTime = vStart + Seconds
  lSecondsRemaining = Seconds
  If ReportInterval = 0 Then
    lReportTime = lEndTime
    bReporting = False
  Else
    lReportTime = vStart + ReportInterval
    bReporting = True
  End If
  
  Do While Timer <= lEndTime
    If bReporting Then Debug.Print CStr(lSecondsRemaining) & " seconds remaining."
    Do While Timer <= lReportTime
      DoEvents
    Loop
    lReportTime = Min(lReportTime + ReportInterval, lEndTime)
    lSecondsRemaining = lEndTime - lReportTime
  Loop
  If bReporting Then Debug.Print "WaitAFewSeconds is finished."
    
End Sub

 

[RoryA]

You can also use createobject and then explicitly open the personal macro workbook prior to opening your main workbook.

 

[BCVolkert]

I think the Shell is the better option for me. I'm working on one set of modules that I use as a basis for my work in all MS Office applications. I suspect that the Shell approach is likely to be similar from one application to the next. Ultimately, I plan to have a Global template for each application that I support and I'm hoping the code can be the same in all of them. Right now it is in Personal.xlsb when I run Excel; but will end up elsewhere. I also want to minimize the confusion of users that might have their own Personal.xlsb. Your advice was perfect. Thanks again.