Display in cell not dialogue box

[sparx]

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

[Dave Peterson]

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

[Jim May]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

[Jim May]

Thanks for the clarification.
Jim
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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