Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you have multiple Excel instances open then this is what I suggest.</p> <p><strong>Logic</strong></p> <ol> <li>Check if your workbook is open or not. If not open, then open it.</li> <li>If it is open then it could be in any Excel instance.</li> <li>Find the Excel instance and bind with the relevant workbook.</li> </ol> <p><code>GetObject</code> unfortunately will return the same instance every time unless you close that Excel instance. Also there is no reliable way to get it to loop through all Excel instances. Talking of reliability, I would turn your attention towards APIs. The 3 APIs that we will use is <code>FindWindowEx</code> , <code>GetDesktopWindow</code> and <code>AccessibleObjectFromWindow&amp;</code></p> <p>See this example (<strong>TRIED AND TESTED in EXCEL 2010</strong>)</p> <pre><code>Option Explicit Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _ (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _ ByVal lpsz2 As String) As Long Private Declare Function GetDesktopWindow Lib "user32" () As Long Private Declare Function AccessibleObjectFromWindow&amp; Lib "oleacc" _ (ByVal hwnd&amp;, ByVal dwId&amp;, riid As GUID, xlWB As Object) Private Const OBJID_NATIVEOM = &amp;HFFFFFFF0 Private Type GUID lData1 As Long iData2 As Integer iData3 As Integer aBData4(0 To 7) As Byte End Type Sub Sample() Dim Ret Dim oXLApp As Object, wb As Object Dim sPath As String, sFileName As String, SFile As String, filewithoutExt As String Dim IDispatch As GUID sPath = "C:\Users\Chris\Desktop\" sFileName = "Data.xlsx": filewithoutExt = "Data" SFile = sPath &amp; sFileName Ret = IsWorkBookOpen(SFile) '~~&gt; If file is open If Ret = True Then Dim dsktpHwnd As Long, hwnd As Long, mWnd As Long, cWnd As Long SetIDispatch IDispatch dsktpHwnd = GetDesktopWindow hwnd = FindWindowEx(dsktpHwnd, 0&amp;, "XLMAIN", vbNullString) mWnd = FindWindowEx(hwnd, 0&amp;, "XLDESK", vbNullString) While mWnd &lt;&gt; 0 And cWnd = 0 cWnd = FindWindowEx(mWnd, 0&amp;, "EXCEL7", filewithoutExt) hwnd = FindWindowEx(dsktpHwnd, hwnd, "XLMAIN", vbNullString) mWnd = FindWindowEx(hwnd, 0&amp;, "XLDESK", vbNullString) Wend '~~&gt; We got the handle of the Excel instance which has the file If cWnd &gt; 0 Then '~~&gt; Bind with the Instance Call AccessibleObjectFromWindow(cWnd, OBJID_NATIVEOM, IDispatch, wb) '~~&gt; Work with the file With wb.Application.Workbooks(sFileName) ' '~~&gt; Rest of the code ' End With End If '~~&gt; If file is not open Else On Error Resume Next Set oXLApp = GetObject(, "Excel.Application") '~~&gt; If not found then create new instance If Err.Number &lt;&gt; 0 Then Set oXLApp = CreateObject("Excel.Application") End If Err.Clear On Error GoTo 0 Set wb = oXLApp.Workbooks.Open(SFile) ' '~~&gt; Rest of the code ' End If End Sub Private Sub SetIDispatch(ByRef ID As GUID) With ID .lData1 = &amp;H20400 .iData2 = &amp;H0 .iData3 = &amp;H0 .aBData4(0) = &amp;HC0 .aBData4(1) = &amp;H0 .aBData4(2) = &amp;H0 .aBData4(3) = &amp;H0 .aBData4(4) = &amp;H0 .aBData4(5) = &amp;H0 .aBData4(6) = &amp;H0 .aBData4(7) = &amp;H46 End With End Sub '~~&gt; Function to check if file is open Function IsWorkBookOpen(FileName As String) Dim ff As Long, ErrNo As Long On Error Resume Next ff = FreeFile() Open FileName For Input Lock Read As #ff Close ff ErrNo = Err On Error GoTo 0 Select Case ErrNo Case 0: IsWorkBookOpen = False Case 70: IsWorkBookOpen = True Case Else: Error ErrNo End Select End Function </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCheck to see if Excel is open (from another Office 2010 App)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USSiddharth Rout
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCheck to see if Excel is open (from another Office 2010 App)
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COOkay, awesome. I'll give that a try. Thanks so much for your assistance Siddharth.
      singulars
      1. PO
      1. USChris Day
    2. COThis worked perfectly once I'd changed `cWnd = FindWindowEx(mWnd, 0&, "EXCEL7", filewithoutExt)` to use sFileName as the last parameter. Thanks again.
      singulars
      1. PO
      1. USChris Day
    3. COOne last thing though... After `Call AccessibleObjectFromWindow(cWnd, OBJID_NATIVEOM, IDispatch, wb)` I would link to a worksheet object depending on the value of DocNo (using `Set xlSheet = ...`). I'm not sure how to do this from within the `With` construct. Is there a way to do this, or link directly to the workbook object (like with `Set xlBook = xlApp.Workbooks("File Index.xlsx")` or something. Thanks! I'm learning a lot here. :)
      singulars
      1. PO
      1. USChris Day
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload