Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display in cell not dialogue box
Hello All - how do I make the result of the following macro display its result in an excel cell not the dialogue box? Sub GetPhysAddress() Dim oWMIService As Object Dim oColAdapters As Object Dim oObjAdapter As Object Set oWMIService = GetObject("winmgmts:" & "!\\.\root\cimv2") Set oColAdapters = oWMIService.ExecQuery _ ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True") For Each oObjAdapter In oColAdapters MsgBox "Adapter Physical address: " & oObjAdapter.MACAddress Next Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=524275 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display in cell not dialogue box
This worked ok for me:
Option Explicit Sub GetPhysAddress() Dim oWMIService As Object Dim oColAdapters As Object Dim oObjAdapter As Object Dim DestCell As Range Set DestCell = ActiveSheet.Range("a1") Set oWMIService = GetObject("winmgmts:" & "!\\.\root\cimv2") Set oColAdapters = oWMIService.ExecQuery _ ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True") For Each oObjAdapter In oColAdapters DestCell.Value = oObjAdapter.MACAddress Set DestCell = DestCell.Offset(1, 0) Next Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub Adjust the worksheet and range to what you want. sparx wrote: Hello All - how do I make the result of the following macro display its result in an excel cell not the dialogue box? Sub GetPhysAddress() Dim oWMIService As Object Dim oColAdapters As Object Dim oObjAdapter As Object Set oWMIService = GetObject("winmgmts:" & "!\\.\root\cimv2") Set oColAdapters = oWMIService.ExecQuery _ ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True") For Each oObjAdapter In oColAdapters MsgBox "Adapter Physical address: " & oObjAdapter.MACAddress Next Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=524275 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display in cell not dialogue box
I see from time to time code (like this ending in)
Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub Doesn't the End Sub line drop the values of these Objects from memory? I only understand that each of the 3 (above) are Object Variables and were previously set in the code, but evidently excluding the "Nothing" either 1) keeps the values in memory, or 2)although the values are no longer in memory some amt of space is being taken up and makes things inefficient.. Confused.. Help appreciated. TIA, Jim. "Dave Peterson" wrote in message ... This worked ok for me: Option Explicit Sub GetPhysAddress() Dim oWMIService As Object Dim oColAdapters As Object Dim oObjAdapter As Object Dim DestCell As Range Set DestCell = ActiveSheet.Range("a1") Set oWMIService = GetObject("winmgmts:" & "!\\.\root\cimv2") Set oColAdapters = oWMIService.ExecQuery _ ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True") For Each oObjAdapter In oColAdapters DestCell.Value = oObjAdapter.MACAddress Set DestCell = DestCell.Offset(1, 0) Next Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub Adjust the worksheet and range to what you want. sparx wrote: Hello All - how do I make the result of the following macro display its result in an excel cell not the dialogue box? Sub GetPhysAddress() Dim oWMIService As Object Dim oColAdapters As Object Dim oObjAdapter As Object Set oWMIService = GetObject("winmgmts:" & "!\\.\root\cimv2") Set oColAdapters = oWMIService.ExecQuery _ ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True") For Each oObjAdapter In oColAdapters MsgBox "Adapter Physical address: " & oObjAdapter.MACAddress Next Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=524275 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display in cell not dialogue box
From what I understand, if it's straight old VBA code, then the variables
disappear when they go out of scope--and that'll be when the procedure runs if the variables are declared within that procedure. From a personal preference, I don't usually use this type of clean-up code. Jim May wrote: I see from time to time code (like this ending in) Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub Doesn't the End Sub line drop the values of these Objects from memory? I only understand that each of the 3 (above) are Object Variables and were previously set in the code, but evidently excluding the "Nothing" either 1) keeps the values in memory, or 2)although the values are no longer in memory some amt of space is being taken up and makes things inefficient.. Confused.. Help appreciated. TIA, Jim. "Dave Peterson" wrote in message ... This worked ok for me: Option Explicit Sub GetPhysAddress() Dim oWMIService As Object Dim oColAdapters As Object Dim oObjAdapter As Object Dim DestCell As Range Set DestCell = ActiveSheet.Range("a1") Set oWMIService = GetObject("winmgmts:" & "!\\.\root\cimv2") Set oColAdapters = oWMIService.ExecQuery _ ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True") For Each oObjAdapter In oColAdapters DestCell.Value = oObjAdapter.MACAddress Set DestCell = DestCell.Offset(1, 0) Next Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub Adjust the worksheet and range to what you want. sparx wrote: Hello All - how do I make the result of the following macro display its result in an excel cell not the dialogue box? Sub GetPhysAddress() Dim oWMIService As Object Dim oColAdapters As Object Dim oObjAdapter As Object Set oWMIService = GetObject("winmgmts:" & "!\\.\root\cimv2") Set oColAdapters = oWMIService.ExecQuery _ ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True") For Each oObjAdapter In oColAdapters MsgBox "Adapter Physical address: " & oObjAdapter.MACAddress Next Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=524275 -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display in cell not dialogue box
Thanks for the clarification.
Jim "Dave Peterson" wrote in message ... From what I understand, if it's straight old VBA code, then the variables disappear when they go out of scope--and that'll be when the procedure runs if the variables are declared within that procedure. From a personal preference, I don't usually use this type of clean-up code. Jim May wrote: I see from time to time code (like this ending in) Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub Doesn't the End Sub line drop the values of these Objects from memory? I only understand that each of the 3 (above) are Object Variables and were previously set in the code, but evidently excluding the "Nothing" either 1) keeps the values in memory, or 2)although the values are no longer in memory some amt of space is being taken up and makes things inefficient.. Confused.. Help appreciated. TIA, Jim. "Dave Peterson" wrote in message ... This worked ok for me: Option Explicit Sub GetPhysAddress() Dim oWMIService As Object Dim oColAdapters As Object Dim oObjAdapter As Object Dim DestCell As Range Set DestCell = ActiveSheet.Range("a1") Set oWMIService = GetObject("winmgmts:" & "!\\.\root\cimv2") Set oColAdapters = oWMIService.ExecQuery _ ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True") For Each oObjAdapter In oColAdapters DestCell.Value = oObjAdapter.MACAddress Set DestCell = DestCell.Offset(1, 0) Next Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub Adjust the worksheet and range to what you want. sparx wrote: Hello All - how do I make the result of the following macro display its result in an excel cell not the dialogue box? Sub GetPhysAddress() Dim oWMIService As Object Dim oColAdapters As Object Dim oObjAdapter As Object Set oWMIService = GetObject("winmgmts:" & "!\\.\root\cimv2") Set oColAdapters = oWMIService.ExecQuery _ ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True") For Each oObjAdapter In oColAdapters MsgBox "Adapter Physical address: " & oObjAdapter.MACAddress Next Set oObjAdapter = Nothing Set oColAdapters = Nothing Set oWMIService = Nothing End Sub -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=524275 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display contents of a specific cell even if a group of cells is mo | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
formatting a cell to display the correct date. | Excel Discussion (Misc queries) | |||
Cell display is 38727.4101273148 Formula Bar is 1/10/2006 9:50 | Excel Discussion (Misc queries) | |||
Display a row if a cell value is greater than zero | Excel Worksheet Functions |