One opened workbook does not see other opened workbook

Question

We have this oldschool software which opens new Excel workbook and fills it with data. It does not do anything more. The workbook stays unsaved and opened.

Now I have created a macro which looks for this newly opened workbook by name (using Workbooks.Count and Application.Workbooks(i).Name) and when it finds it, it copies some data from that.

Unfortunately since upgrading to Office 365, the Excel does not see the unsaved workbook so the macro stopped working. The unsaved workbook is not even counted in Workbooks.Count.

Is there any way for me to make this work again in newest version of Excel?

Two opened workbooks

Only one visible in VBA

EDIT: Unfortunately I cannot edit the code of the oldschool program. What bothers me, that when I was using 2013 excel, it worked flawlessly.

Sub zkopirujPoctyZeSesitu()

rozdilPoctuZbozi = 0
rozdilUZbozi = 0
Dim aktualne As Integer
Dim bylo As Integer
Dim franta As String
idSesit = -1
Dim hledas As String

'nalezeni ID Sešitu z NAV
For i = 1 To Workbooks.Count

    If InStr(Application.Workbooks(i).Name, "Sešit") > 0 Then
        idSesit = i
    ElseIf InStr(Application.Workbooks(i).Name, "GENERATOR") > 0 Then
        idGenerator = i
    End If
Next i

'kontrola zda je otevřenej stav skladu
If idSesit = -1 Then
    MsgBox ("Nelze načíst stav skladu z NAV!!!!")
    End
ElseIf Not (Workbooks(idSesit).Worksheets(1).Cells(1, 1).Text = "Číslo zboží" And Workbooks(idSesit).Worksheets(1).Cells(1, 2).Text = "Varianta zboží") Then
    MsgBox ("Je třeba zavřít všechny Excel soubory s názvem" & Chr(34) & "Sešit" & Chr(34) & vbNewLine & "(kromě výstupního DatSkladu z NAV)")
    End

End If



For i = 1 To List1.Cells(Rows.Count, 1).End(xlUp).Row

    If Len(List1.Cells(i, 1).Text) = 5 And Left$(List1.Cells(i, 1).Text, 1) = "0" Then
        hledas = Right(List1.Cells(i, 1).Text, Len(List1.Cells(i, 1).Text) - 1)
    Else
        hledas = List1.Cells(i, 1).Value
    End If
    Set FoundCell = Workbooks(idSesit).Worksheets(1).Range("A:A").Find(What:=hledas, After:=Workbooks(idSesit).Worksheets(1).Cells(1, 1), LookIn:=xlFormulas, _
                                                                       LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                                       MatchCase:=False, SearchFormat:=False)
    
    If Not FoundCell Is Nothing Then
       
        'ochrana proti Mertens lagerSchuette
        If (Workbooks(idSesit).Worksheets(1).Cells(FoundCell.Row, 6).Value < 0) Then
            MsgBox ("Stav skladu je z Mertensu!!!!")
            End
        End If
        
        'o kolik se lisi stav
        aktualne = Workbooks(idSesit).Worksheets(1).Cells(FoundCell.Row, 6).Value
        bylo = List1.Cells(i, 20).Value
        
        rozdilPoctuZbozi = rozdilPoctuZbozi + aktualne - bylo
        'pocet zbozi u kteryho je rozdilny stav
        If aktualne <> bylo Then
            rozdilUZbozi = rozdilUZbozi + 1
        End If
        
        'prepis poctu ks
        List1.Cells(i, 20).Value = Workbooks(idSesit).Worksheets(1).Cells(FoundCell.Row, 6).Value
        
    End If

Next i

End Sub

Do you understand what different Excel sessions means? Please show us the code you use. I am afraid it opens a new session and this is not related to Office version. Is the created workbook saved? But this is only a supposition, without seeing the code in discussion. Even if it is "oldschool" type... And is the macro you recently created of a secret type? Please, edit your question and show it to us. — FaneDuru, Mar 24 '22 at 14:39
How is the new workbook opened? If you open it manually eg by double click a file, it can be that Excel starts a new session (a second Excel). You can avoid that by using your first Excel (the one with the macro) to open the file eg via File > Open dialog. • [Without code, it is very hard to help you](http://idownvotedbecau.se/nocode/). — Pᴇʜ, Mar 24 '22 at 14:44
`Application.Workbooks` is the key here. `Application`, in which the VBA session you are running, is the entirety of that VBA session's scope. It can't see out and beyond it (well... it can but it's ugly, link below). It's very likely that the workbook opened by the software is running inside another instance of `Application`, and there is no `For each application in Applications` but there are some interesting approaches [here](https://stackoverflow.com/questions/30363748/having-multiple-excel-instances-launched-how-can-i-get-the-application-object-f) that give you similar functionality. — JNevill, Mar 24 '22 at 14:44
Please, try answering this clarification question, too: **Is the created workbook saved?** From what you say I can deduce that it is, but not so sure. So, do you have it **its full name**, or only part of its name ("Sešit" or "GENERATOR")? Knowing that, I can try helping with a solution able to find the workbook in any open session/instance, close it there, open in the one where your code runs and then processing as you try/need. — FaneDuru, Mar 25 '22 at 08:11
One of the approaches from JNevill's answer helped me solve it. Thank you all. — Vojtěch K., Mar 26 '22 at 11:09

score 2 · Answer 1 · answered Mar 24 '22 at 17:57

First of all, you need to check whether another Excel instance is running in memory, i.e. whether another excel.exe process is running. If so, you may consider connecting to the running Excel instance and get the Workbooks collection from there. However, you need to be aware that two processes can be run under different security contexts, in that case there is no way to reach one from another.

You may find the GetObject and CreateObject behavior of Office automation servers article helpful.

Yes, another excel.exe process is running. Thank you. – Vojtěch K. Mar 25 '22 at 07:11 — Vojtěch K., Mar 25 '22 at 07:11

One opened workbook does not see other opened workbook

1 Answers1