vba - How to connect to OPEN workbook in another instance of Excel -

currently can run 2 excel vba processes simultaniously within 2 seperate excel instances on 1 pc.

my goal import data excel instance 2 excel instance 1 every minute.

unfortunately not possible connect workbook in excel instance 1 open workbook in excel instance 2.

since can connect saved workbook, solution save workbook in instance 2 every minute , retrieve new data saved workbook.

although rather heavy method. there better solution connect open workbook in instance of excel?

(to open workbook in same instance no solution since in case can no longer run 2 vba processes simultaniously.)

thanks!

short version

---

option explicit  public sub getdatafromexternalxlinstance()     dim instancefile object, ur variant, lr long      'if not open, getobject() open in new instance      set instancefile = getobject("c:\tmp\testdata2.xlsx")  '(code running testdata1)     ur = instancefile.worksheets(2).usedrange              'get used range 2nd worksheet      activesheet         lr = .cells(.rows.count, "a").end(xlup).row + 1    'last row on active sheet         .range(.cells(lr, "a"), .cells(ubound(ur) + lr - 1, ubound(ur, 2))) = ur     end      'instancefile.close     'set instancefile = nothing end sub 

---

long version using api calls (from excel file getobject())

---

option explicit  #if vba7   'or: #if win64  'win64=true, win32=true, win16= false     private declare ptrsafe function findwindow lib "user32" alias "findwindowa" (byval lpclassname string, byval lpwindowname string) long     private declare ptrsafe function sendmessage lib "user32" alias "sendmessagea" (byval hwnd long, byval wmsg long, byval wparam long, lparam any) long #else     declare function findwindow lib "user32" alias "findwindowa" (byval lpclassname string, byval lpwindowname long) long     declare function sendmessage lib "user32" alias "sendmessagea" (byval hwnd long,byval wmsg long, byval wparam long, byval lparam long) long #end if  public sub getdatafromexternalxlinstanceapi()     dim xlapp object     dim xlnotrunning boolean 'flag final reference release      on error resume next        'check if excel running; defer error trapping         set xlapp = getobject(, "excel.application")    'if it's not running error occurs         xlnotrunning = (err.number <> 0)         err.clear               'clear err object in case of error     on error goto 0             'reset error trapping      detectexcel                 'if excel running enter running object table     set xlapp = getobject("c:\tmp\testdata2.xlsx")      'set object reference file      'show excel through application property     xlapp.application.visible = true     'show actual window of file using windows collection of xlapp object ref     xlapp.parent.windows(1).visible = true      '... process file      'if excel not running when started, close using app's quit method     if xlnotrunning = true xlapp.application.quit     set xlapp = nothing    'release reference application , spreadsheet end sub 

---

public sub detectexcel()    'this procedure detects running excel app , registers     const wm_user = 1024     dim hwnd long      hwnd = findwindow("xlmain", 0)  'if excel running api call returns handle     if hwnd = 0 exit sub       '0 means excel not running      'else excel running use sendmessage api function     'to enter in running object table      sendmessage hwnd, wm_user + 18, 0, 0 end sub