Menu
Can an Excel VBA macro, running in one instance of Excel, access the workbooks of another running instance of Excel? For example, I would like to create a list of all workbooks that are open in any running instance of Excel.
499
Click Insert > Module, and paste the following macro in the Module Window. 4. And then click OK button, the desired macro will be executed at once from one workbook to others.
Question 3: Can You Access Information in Another Workbook with a Macro in a Different Workbook? The answer is yes. When you start to refer to other worksheets and workbooks, you are moving beyond the types of macros that the macro recorder provides and moving on to a powerful stage in your macro building progression.
VBA can be licensed, and there are quite a few pproducts outside office that use VBA. MS no longer issues new licenses. There are non-MS VBA implementations, like from Summit software. In any case you need to have your own product that would host VBA.
Microsoft is finally planning to block Visual Basic for Applications (VBA) macros by default in a variety of Office apps. The change will apply to Office files that are downloaded from the internet and include macros, so Office users will no longer be able to enable certain content with a simple click of a button.
Cornelius' answer is partially correct. His code gets the current instance and then makes a new instance. GetObject only ever gets the first instance, no matter how many instances are available. The question I believe is how can you get a specific instance from among many instances.
For a VBA project, make two modules, one code module, and the other as a form with one command button named Command1. You might need to add a reference to Microsoft.Excel.
This code displays all the name of each workbook for each running instance of Excel in the Immediate window.
'------------- Code Module -------------- Option Explicit 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 Declare Function GetClassName Lib "User32" Alias "GetClassNameA" (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long Declare Function IIDFromString Lib "ole32" (ByVal lpsz As Long, ByRef lpiid As UUID) As Long Declare Function AccessibleObjectFromWindow Lib "oleacc" (ByVal hWnd As Long, ByVal dwId As Long, ByRef riid As UUID, ByRef ppvObject As Object) As Long Type UUID 'GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(7) As Byte End Type '------------- Form Module -------------- Option Explicit Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}" Const OBJID_NATIVEOM As Long = &HFFFFFFF0 'Sub GetAllWorkbookWindowNames() Sub Command1_Click() On Error GoTo MyErrorHandler Dim hWndMain As Long hWndMain = FindWindowEx(0&, 0&, "XLMAIN", vbNullString) Do While hWndMain <> 0 GetWbkWindows hWndMain hWndMain = FindWindowEx(0&, hWndMain, "XLMAIN", vbNullString) Loop Exit Sub MyErrorHandler: MsgBox "GetAllWorkbookWindowNames" & vbCrLf & vbCrLf & "Err = " & Err.Number & vbCrLf & "Description: " & Err.Description End Sub Private Sub GetWbkWindows(ByVal hWndMain As Long) On Error GoTo MyErrorHandler Dim hWndDesk As Long hWndDesk = FindWindowEx(hWndMain, 0&, "XLDESK", vbNullString) If hWndDesk <> 0 Then Dim hWnd As Long hWnd = FindWindowEx(hWndDesk, 0, vbNullString, vbNullString) Dim strText As String Dim lngRet As Long Do While hWnd <> 0 strText = String$(100, Chr$(0)) lngRet = GetClassName(hWnd, strText, 100) If Left$(strText, lngRet) = "EXCEL7" Then GetExcelObjectFromHwnd hWnd Exit Sub End If hWnd = FindWindowEx(hWndDesk, hWnd, vbNullString, vbNullString) Loop On Error Resume Next End If Exit Sub MyErrorHandler: MsgBox "GetWbkWindows" & vbCrLf & vbCrLf & "Err = " & Err.Number & vbCrLf & "Description: " & Err.Description End Sub Public Function GetExcelObjectFromHwnd(ByVal hWnd As Long) As Boolean On Error GoTo MyErrorHandler Dim fOk As Boolean fOk = False Dim iid As UUID Call IIDFromString(StrPtr(IID_IDispatch), iid) Dim obj As Object If AccessibleObjectFromWindow(hWnd, OBJID_NATIVEOM, iid, obj) = 0 Then 'S_OK Dim objApp As Excel.Application Set objApp = obj.Application Debug.Print objApp.Workbooks(1).Name Dim myWorksheet As Worksheet For Each myWorksheet In objApp.Workbooks(1).Worksheets Debug.Print " " & myWorksheet.Name DoEvents Next fOk = True End If GetExcelObjectFromHwnd = fOk Exit Function MyErrorHandler: MsgBox "GetExcelObjectFromHwnd" & vbCrLf & vbCrLf & "Err = " & Err.Number & vbCrLf & "Description: " & Err.Description End Function If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
