Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting list of open workbooks
How do I get a list of all open Excel workbooks in WinXP?
How do I get a list of all open applications in WinXP? I basically want to get a list of open Excel workbooks and copy this list into a sheet for a user to choose from. I may eventually wish to add a list of all open applications (Specifically, SPSS applications). Lance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting list of open workbooks
The following would supply you with a list of workbooks
(alter the code to write into your sheet): Dim wrkBook As Workbook Dim wrkBooks As Workbooks Dim strMsg As String For Each wrkBook In Workbooks strMsg = strMsg & wrkBook.Name & vbCr Next wrkBook MsgBox strMsg A list of Apps is do-able, but I'm sorry, I don't know that. Hope this helps at least 50%. -----Original Message----- How do I get a list of all open Excel workbooks in WinXP? How do I get a list of all open applications in WinXP? I basically want to get a list of open Excel workbooks and copy this list into a sheet for a user to choose from. I may eventually wish to add a list of all open applications (Specifically, SPSS applications). Lance . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting list of open workbooks
Lance,
Here's a sample from the Excel side: Sub AddListOfWorkbooksToNewWorkbook() Dim wb As Workbook Dim wbOpen As Workbook Dim nRow As Integer Set wb = Workbooks.Add For Each wbOpen In Excel.Workbooks nRow = nRow + 1 wb.Worksheets(1).Cells(nRow, 1).Value = wbOpen.Name Next End Sub -- Charles www.officezealot.com wrote in message ... How do I get a list of all open Excel workbooks in WinXP? How do I get a list of all open applications in WinXP? I basically want to get a list of open Excel workbooks and copy this list into a sheet for a user to choose from. I may eventually wish to add a list of all open applications (Specifically, SPSS applications). Lance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting list of open workbooks
The following will list all Apps/Processes currently
running. Copy all to a module and let-her-rip (note: you may need to do some clean up due to line wrapping in this editor): Sub List_All_Processes_Running() 'Prepare destination Cells.Clear [A1].Select 'Dimension arrays Dim aProcessName() Dim aBelongsTo() Dim aProcessID() 'Create object variables Set xWMIService = GetObject("WINMGMTS: {IMPERSONATIONLEVEL=IMPERSONATE}!\\.\ROOT\CIMV2") 'Run query against WMI Set xProcesses = xWMIService.ExecQuery("SELECT * FROM WIN32_PROCESS") 'Initialize loop for each item in xProcesses For Each xProcess In xProcesses 'Determine if the owner of the process can be identified If xProcess.GetOwner(User, Domain) = 0 Then 'Able to identify process owner x = x + 1 ReDim Preserve aProcessName(x) ReDim Preserve aBelongsTo(x) ReDim Preserve aProcessID(x) aProcessName(x) = xProcess.Caption aBelongsTo(x) = Domain & "\" & User aProcessID(x) = xProcess.ProcessID Else 'Unable to identify process owner x = x + 1 ReDim Preserve aProcessName(x) ReDim Preserve aBelongsTo(x) ReDim Preserve aProcessID(x) aProcessName(x) = xProcess.Caption aBelongsTo(x) = "Owner Unknown " & Domain & "\" & User aProcessID(x) = xProcess.ProcessID End If Next 'Write results For x = 1 To UBound(aProcessName) ActiveCell.Offset(x, 0).FormulaR1C1 = aProcessName(x) ActiveCell.Offset(x, 1).FormulaR1C1 = aBelongsTo(x) ActiveCell.Offset(x, 2).FormulaR1C1 = aProcessID(x) ActiveCell.Offset(x, 3).FormulaR1C1 = UCase (aProcessName(x)) Next x 'Format results Range("A1:D1").Value = Array("PROCESS", "BELONGS TO", "PROCESSID", "NAME") Rows(1).Font.Bold = True Rows(1).HorizontalAlignment = xlCenter ActiveWindow.SplitRow = 1 ActiveWindow.FreezePanes = True Cells.Columns.AutoFit For Each xCol In ActiveSheet.Columns If xCol.ColumnWidth 50 Then xCol.ColumnWidth = 50 Next xCol 'Clear objects from memory Set xWMIService = Nothing Set xProcesses = Nothing End Sub -----Original Message----- How do I get a list of all open Excel workbooks in WinXP? How do I get a list of all open applications in WinXP? I basically want to get a list of open Excel workbooks and copy this list into a sheet for a user to choose from. I may eventually wish to add a list of all open applications (Specifically, SPSS applications). Lance . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting list of open workbooks
"pk" wrote in message
... The following will list all Apps/Processes currently running. Copy all to a module and let-her-rip (note: you may need to do some clean up due to line wrapping in this editor): Sub List_All_Processes_Running() 'Prepare destination Cells.Clear [A1].Select 'Dimension arrays Dim aProcessName() Dim aBelongsTo() Dim aProcessID() 'Create object variables Set xWMIService = GetObject("WINMGMTS: {IMPERSONATIONLEVEL=IMPERSONATE}!\\.\ROOT\CIMV2") 'Run query against WMI Set xProcesses = xWMIService.ExecQuery("SELECT * FROM WIN32_PROCESS") 'Initialize loop for each item in xProcesses For Each xProcess In xProcesses 'Determine if the owner of the process can be identified If xProcess.GetOwner(User, Domain) = 0 Then 'Able to identify process owner x = x + 1 ReDim Preserve aProcessName(x) ReDim Preserve aBelongsTo(x) ReDim Preserve aProcessID(x) aProcessName(x) = xProcess.Caption aBelongsTo(x) = Domain & "\" & User aProcessID(x) = xProcess.ProcessID Else 'Unable to identify process owner x = x + 1 ReDim Preserve aProcessName(x) ReDim Preserve aBelongsTo(x) ReDim Preserve aProcessID(x) aProcessName(x) = xProcess.Caption aBelongsTo(x) = "Owner Unknown " & Domain & "\" & User aProcessID(x) = xProcess.ProcessID End If Next 'Write results For x = 1 To UBound(aProcessName) ActiveCell.Offset(x, 0).FormulaR1C1 = aProcessName(x) ActiveCell.Offset(x, 1).FormulaR1C1 = aBelongsTo(x) ActiveCell.Offset(x, 2).FormulaR1C1 = aProcessID(x) ActiveCell.Offset(x, 3).FormulaR1C1 = UCase (aProcessName(x)) Next x 'Format results Range("A1:D1").Value = Array("PROCESS", "BELONGS TO", "PROCESSID", "NAME") Rows(1).Font.Bold = True Rows(1).HorizontalAlignment = xlCenter ActiveWindow.SplitRow = 1 ActiveWindow.FreezePanes = True Cells.Columns.AutoFit For Each xCol In ActiveSheet.Columns If xCol.ColumnWidth 50 Then xCol.ColumnWidth = 50 Next xCol 'Clear objects from memory Set xWMIService = Nothing Set xProcesses = Nothing End Sub Hi. I tried to run this code and obtained the following error message: "Run-time error 432 File name or class name not found during Automation operation" with the Set xWMIService = GetObject("WINMGMTS:{IMPERSONATIONLEVEL=IMPERSONAT E}!\\.\ROOT\CIMV2") line highlighted when I hit Debug. Do you have to check anything in Tools | References to make certain a library available? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting list of open workbooks
Could be, sorry about that. I will have to check my code
tomorrow and see. I will post another response to this one tomorrow, so please check back. -----Original Message----- "pk" wrote in message ... The following will list all Apps/Processes currently running. Copy all to a module and let-her-rip (note: you may need to do some clean up due to line wrapping in this editor): Sub List_All_Processes_Running() 'Prepare destination Cells.Clear [A1].Select 'Dimension arrays Dim aProcessName() Dim aBelongsTo() Dim aProcessID() 'Create object variables Set xWMIService = GetObject("WINMGMTS: {IMPERSONATIONLEVEL=IMPERSONATE}!\\.\ROOT\CIMV2") 'Run query against WMI Set xProcesses = xWMIService.ExecQuery("SELECT * FROM WIN32_PROCESS") 'Initialize loop for each item in xProcesses For Each xProcess In xProcesses 'Determine if the owner of the process can be identified If xProcess.GetOwner(User, Domain) = 0 Then 'Able to identify process owner x = x + 1 ReDim Preserve aProcessName(x) ReDim Preserve aBelongsTo(x) ReDim Preserve aProcessID(x) aProcessName(x) = xProcess.Caption aBelongsTo(x) = Domain & "\" & User aProcessID(x) = xProcess.ProcessID Else 'Unable to identify process owner x = x + 1 ReDim Preserve aProcessName(x) ReDim Preserve aBelongsTo(x) ReDim Preserve aProcessID(x) aProcessName(x) = xProcess.Caption aBelongsTo(x) = "Owner Unknown " & Domain & "\" & User aProcessID(x) = xProcess.ProcessID End If Next 'Write results For x = 1 To UBound(aProcessName) ActiveCell.Offset(x, 0).FormulaR1C1 = aProcessName (x) ActiveCell.Offset(x, 1).FormulaR1C1 = aBelongsTo(x) ActiveCell.Offset(x, 2).FormulaR1C1 = aProcessID(x) ActiveCell.Offset(x, 3).FormulaR1C1 = UCase (aProcessName(x)) Next x 'Format results Range("A1:D1").Value = Array("PROCESS", "BELONGS TO", "PROCESSID", "NAME") Rows(1).Font.Bold = True Rows(1).HorizontalAlignment = xlCenter ActiveWindow.SplitRow = 1 ActiveWindow.FreezePanes = True Cells.Columns.AutoFit For Each xCol In ActiveSheet.Columns If xCol.ColumnWidth 50 Then xCol.ColumnWidth = 50 Next xCol 'Clear objects from memory Set xWMIService = Nothing Set xProcesses = Nothing End Sub Hi. I tried to run this code and obtained the following error message: "Run-time error 432 File name or class name not found during Automation operation" with the Set xWMIService = GetObject("WINMGMTS:{IMPERSONATIONLEVEL=IMPERSONA TE}! \\.\ROOT\CIMV2") line highlighted when I hit Debug. Do you have to check anything in Tools | References to make certain a library available? . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting list of open workbooks
In reference to my earlier post that includes code to list
the processes currently running: You will also need to reference the "Microsoft Forms 2.0 Object Library" from the Visual Basic Editor to get that code to work. Just in case- to do this, open the Visual Basic Editor, then from the main menu select [Tools] then [References] then scroll to locate it and check it. Hope this does the trick for you. -----Original Message----- "pk" wrote in message ... The following will list all Apps/Processes currently running. Copy all to a module and let-her-rip (note: you may need to do some clean up due to line wrapping in this editor): Sub List_All_Processes_Running() 'Prepare destination Cells.Clear [A1].Select 'Dimension arrays Dim aProcessName() Dim aBelongsTo() Dim aProcessID() 'Create object variables Set xWMIService = GetObject("WINMGMTS: {IMPERSONATIONLEVEL=IMPERSONATE}!\\.\ROOT\CIMV2") 'Run query against WMI Set xProcesses = xWMIService.ExecQuery("SELECT * FROM WIN32_PROCESS") 'Initialize loop for each item in xProcesses For Each xProcess In xProcesses 'Determine if the owner of the process can be identified If xProcess.GetOwner(User, Domain) = 0 Then 'Able to identify process owner x = x + 1 ReDim Preserve aProcessName(x) ReDim Preserve aBelongsTo(x) ReDim Preserve aProcessID(x) aProcessName(x) = xProcess.Caption aBelongsTo(x) = Domain & "\" & User aProcessID(x) = xProcess.ProcessID Else 'Unable to identify process owner x = x + 1 ReDim Preserve aProcessName(x) ReDim Preserve aBelongsTo(x) ReDim Preserve aProcessID(x) aProcessName(x) = xProcess.Caption aBelongsTo(x) = "Owner Unknown " & Domain & "\" & User aProcessID(x) = xProcess.ProcessID End If Next 'Write results For x = 1 To UBound(aProcessName) ActiveCell.Offset(x, 0).FormulaR1C1 = aProcessName (x) ActiveCell.Offset(x, 1).FormulaR1C1 = aBelongsTo(x) ActiveCell.Offset(x, 2).FormulaR1C1 = aProcessID(x) ActiveCell.Offset(x, 3).FormulaR1C1 = UCase (aProcessName(x)) Next x 'Format results Range("A1:D1").Value = Array("PROCESS", "BELONGS TO", "PROCESSID", "NAME") Rows(1).Font.Bold = True Rows(1).HorizontalAlignment = xlCenter ActiveWindow.SplitRow = 1 ActiveWindow.FreezePanes = True Cells.Columns.AutoFit For Each xCol In ActiveSheet.Columns If xCol.ColumnWidth 50 Then xCol.ColumnWidth = 50 Next xCol 'Clear objects from memory Set xWMIService = Nothing Set xProcesses = Nothing End Sub Hi. I tried to run this code and obtained the following error message: "Run-time error 432 File name or class name not found during Automation operation" with the Set xWMIService = GetObject("WINMGMTS:{IMPERSONATIONLEVEL=IMPERSONA TE}! \\.\ROOT\CIMV2") line highlighted when I hit Debug. Do you have to check anything in Tools | References to make certain a library available? . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting list of open workbooks
wrote in message
... In reference to my earlier post that includes code to list the processes currently running: You will also need to reference the "Microsoft Forms 2.0 Object Library" from the Visual Basic Editor to get that code to work. Just in case- to do this, open the Visual Basic Editor, then from the main menu select [Tools] then [References] then scroll to locate it and check it. Hope this does the trick for you. Hi. I tried to run this code and obtained the following error message: "Run-time error 432 File name or class name not found during Automation operation" with the Set xWMIService = GetObject("WINMGMTS:{IMPERSONATIONLEVEL=IMPERSONA TE}! \\.\ROOT\CIMV2") line highlighted when I hit Debug. Do you have to check anything in Tools | References to make certain a library available? I tried this, but still got the same error. Wouldn't GetObject be a Windows API call? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when i open an Excell file 3 workbooks open.Why? | Excel Discussion (Misc queries) | |||
When I open Excel, workbooks open automatically. How can I stop t | Excel Discussion (Misc queries) | |||
List Open Workbooks in VBA | Excel Programming | |||
Workbooks.Open closes other workbooks | Excel Programming | |||
Workbooks.Open / .Open Text - How do you stop the .xls addition? | Excel Programming |