How To detect EXCEL Is in editing Mode from ACTIVE MS WORD document in VBA

0

Greetings,

I am having instance when EXCEL was in edit mode and have to switch to MS Word for editing a document.

Is there any VBA code that detect EXCEL is in EDITING Mode from other ACTIVE Application (Other Application software is in actve running or editing mode).

Answer
Discuss

Answers

0

Excel will not permit VBA code to run while it is in edit mode. Hence it is impossible for VBA code to detect that mode.

The need for such detection would arise when a user starts running VBA code in another application which refers to the Excel workbook which he didn't finish editing. Such a condition is more likely to occur during testing than in real life. You might exclude the possibility by creating a new instance of Excel (not using any existing one in which the workbook might be open) and open a fresh copy of the workbook in that new instance. You might use parts of the following code.

Option Explicit

Private Sub GetExcel()
    
    ' ==============================================================
    ' for this code to run, a reference to
    ' Miscrosoft Excel Object Library must be set
    ' ==============================================================
    
    Const Ffn As String = "D:\My Documents\Workbook.xlsx"
    Dim XlApp As Excel.Application
    Dim Wb As Excel.Workbook
    
    ' there will be no error here if the Object Library is available
    Set XlApp = CreateObject("Excel.Application")
'    Set Wb = XlApp.Workbooks.Add(xlWBATWorksheet)       ' single sheet only
'    Wb.Sheets(1).Name = "New Tab"
'    ' The name will be "Sheet1" which is confusing:
'    '   The workbook has no name until it is saved.
'    '   Therefore it takes the name of its first sheet.
'    '   However, the "Name" of the first sheet is "New Tab"
'    '   The name given to the nameless workbook is the worksheet's "CodeName".
'    MsgBox Wb.Name
    
    On Error Resume Next
    Set Wb = XlApp.Workbooks.Open(Ffn, ReadOnly:=True)
    If Err Then
        MsgBox "I couldn't open the workbook" & vbCr & _
               Ffn & vbCr & _
               "Check the specified name and path.", _
               vbCritical, "File not found"
    Else
        MsgBox Wb.Name
        ' do what you want to do with the workbook here
        ' you probably will prefer to keep it invisible.
        ' Therefore never Activate it and never make any Selection in it.

        ' For example:
    '        Wb.Worksheets(1).Activate
    '        Cells(1, "A").Select
    '        ActiveCell.Value = 123
        ' all of the above has the simple meaning of:
    '        Wb.Worksheets(1).Cells(1, "A").Value = 123
        
        Wb.Close SaveChanges:=False
    End If
    Set XlApp = Nothing
End Sub

The remmed-out part of the code shows how to add a new workbook with a sinlke sheet (like your own attempt does). Below that is the code for opening an existing workbook. In my tests the workbook opened regardless of whether it was already open or closed and even if it was in edit mode.

Discuss

Discussion

Thank you Variatus.  I will try your suggestion then.  Have a great day.
TRN Jan 12, '18 at 6:55 am
Eventually, please let us know how you solved the problem.
Variatus (rep: 1218) Jan 12, '18 at 7:33 am
Hi Variatus, thanks again so much for your idea.
Below is my code to create another excel instance it works so far and might need more attention, however, it does what I expected.

Public Sub ExcelNewInstance(XlsServer As Object, XlsWorkBook As Object)
    '------------------------------------------------------------------------------------
    ' Try to Open Excel with attempt to create a new instance
    '------------------------------------------------------------------------------------
    Err.Clear
    On Error Resume Next
    Set XlsServer = CreateObject("Excel.Application")
    If Err.Number <> 0 Then
        Err.Clear                                   'Clear Err in case error occurred.
        MsgBox "Could Not Load Excel!" & vbCrLf & vbCrLf & _
 "ProcessTerminated." & vbCrLf & vbCrLf & gsContact, vbExclamation
        GoTo HandleExit
    End If    
    On Error GoTo HandleError
    XlsServer.SheetsInNewWorkbook = 1
    Set XlsWorkBook = XlsServer.Workbooks.Add
   
    XlsServer.Visible = True
    XlsWorkBook.Activate
    XlsWorkBook.Sheets(1).Activate

HandleExit:
    Exit Sub
HandleError:
    Msgbox "Error "
    Exit Sub
End Sub
TRN Jan 12, '18 at 8:10 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login