Get Data from wireless networks available to me

[johnnyL]

I have decided to post this code here because the OP that it was originally written for has not responded back.

This code, I hope, is an open source project that I hope many coders will contribute to make it even better than what I have come up with thus far.

The code I have thus far:
Takes the data received from a cmd prompt window command of 'netsh wlan show networks mode=BSSID' and saves much of the data received into an array and at the end it displays the array data to a sheet.

The data gathered from that will display the SSID Name, Signal level, Band used, Channel used, Radio type, Mac Address, Authorization Algorithm, Encryption, & NetworkType.

I am hoping that with the code I am originally providing, people will chime in with how to add additional data for each of the available WIFI networks.


Ok, now on to the code that I am providing for starters ... I will have to post this over two different posts due to the limitations here on this site

Part 1 of code...

Please Login or Register  to view this content.

[johnnyL]

Part 2 of code...

Please Login or Register  to view this content.

Please post your comments, suggestions, concerns, etc.

[johnnyL]

Here is the original code saved as a file.

[AliGW]

Moved to the appropriate forum section. Thanks for sharing.

[Bilb0_]

I'd post a screen shot but it won't let me attach any pictures...

Throws a Run-time error '5':
Invalid procedure call or argument

Here's the offending line:

Please Login or Register  to view this content.

Hovering over the variable reveals...

ArrayRow = 1
AvailableWirelessNetworksData = (shows a long name of a wireless network being detected)
IncrementalStartPosition = 139
IncrementalEndPosition = 0

This suggests it's not picking up a valid "IncrementalEndPosition' because the equation IncrementalEndPosition {0} - IncrementalStartPosition {139} would result in a negative number and the length portion of the Mid$ function can't be negative.

[Bilb0_]

I decided to step through the code and actually it throws an earlier error

explorer.exe
There is no program associated to perform the requested action. Please install a program or, if one is already installed, create an association in the Default Programs control panel.

Here's the offending line:

Please Login or Register  to view this content.

[johnnyL]

Which version of windows & which version of excel are you using?

[Bilb0_]

Windows 7 x64 and Office 2010 x64

[johnnyL]

Do you have a newer version of Office to test with?

[Bilb0_]

I have 2013 on my Windows 10 laptop but that's not where the issue is.
But I can certainly test it.

stand by...

[Bilb0_]

OK, well this is unpleasant...

I could see it was running (opened Windows Wi-Fi from the taskbar) but then it threw another invalid procedure call and now the only think work is this browser window.
I can ALT-TAB through the applications but can't do anything in them.
Suspect explorer.exe is hung on something...

[Bilb0_]

OK, that was odd.

Mouse moved but had no effect.
Only keyboard was working

OK, finally back to the VBE and it threw the error on this box at the same line as the other. In the Save the RadioType section on the ResultArray line.

[johnnyL]

Please try a reboot on that machine and test it again.

[Bilb0_]

lol, mouse still only works in this browser window...

[johnnyL]

The code has been tested several times on my end successfully, so if you can try rebooting the windows 10 computer and try it again?

[johnnyL]

In the mean time, I am creating a new version that will actually work on Excel 2007.

[Bilb0_]

ok, well this has been interesting but I have to restart again...

I'm left-handed so it dawned on me that my alternate buttons might be causing some issues with your code.
So, the 2nd time I restarted, I switched the buttons back to wrong-handed and tested it again.

It does NOT throw the initial 'explorer.exe' error.

However, it DOES still throw the Run-time error on the ResultArray step of the 'Save the Radiotype' section.
Somehow, the 'IncrementalEndPosition' variable winds up being less than the 'IncrementalStartPosition' variable.
This results in a negative result for the [Lebgth] element of the 'Mid' function on that line.

I'm unfamiliar, (so far) with the contents of the string you're working with but it seems somehow the math gets negative on the length ofthe string.

EDIT: The above references running on Win 10 with Office 2013 (the W540 and not problem PC).

[johnnyL]

Your buttons should have nothing to do with the code.

The following attachment has been tested in excel 2013, It doesn't use that initial code that was throwing the error for you, try it out and see if it works for you:

[Bilb0_]

OK, sorry for the lapse.
I was looking at the code and the string but had a distraction.

In your original code, your variable 'IncrementalEndPosition' tries to get a value from an InStr that's looking for the string "Band"
But the word "Band" isn't in the string, anywhere.
So it naturally resolves to 0 (zero).

I've downloaded the replacement file and will test it now...

[Bilb0_]

Yeah, the problem, or at least the problem which is currently hanging me up is that variable looking for a string that isn't there.
InStr(IncrementalStartPosition, AvailableWirelessNetworksData, "Band")
This resolves to zero (0) because "Band" is not in the string.

There error happens with both files on the same line of code.

[johnnyL]

Do you know how to open up a Command prompt window as administrator?

[Bilb0_]

So I edited the line for the 'IncrementalEndPosition' to look for "Channel" instead of "Band"

Then I remarked out the whole section for capturing the "Band" value.

And it ran fine from there,
The "Band" column in the table is empty.

Yes, I'm able to open a command prompt as admin

[johnnyL]

Ok, DISCONNECT from any WIFI connection that you are able to connect to.

Then open up a Command Prompt window as Admin
then enter the following command:

netsh wlan show networks mode=BSSID

Then copy/paste the results that you get here.

[Bilb0_]

Interface name : Wi-Fi
There are 21 networks currently visible.

SSID 1 : ATTAEM9WFi
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : f8:2d:c0:da:7a:70
Signal : 93%
Radio type : 802.11n
Channel : 10
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156
BSSID 2 : e6:2d:c0:da:7a:73
Signal : 76%
Radio type : 802.11ac
Channel : 157
Basic rates (Mbps) : 58.5
Other rates (Mbps) : 12 18 24 36 48 54 526.5

SSID 2 : Middle-Earth-5GHz
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : 28:74:f5:bc:64:b8
Signal : 73%
Radio type : 802.11ax
Channel : 48
Basic rates (Mbps) : 24 58.5
Other rates (Mbps) : 12 18 36 48 54 526.5
BSSID 2 : 28:74:f5:bc:64:bc
Signal : 75%
Radio type : 802.11ax
Channel : 149
Basic rates (Mbps) : 24 58.5
Other rates (Mbps) : 12 18 36 48 54 526.5

SSID 3 : ATT3hyr2ex
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : 78:6a:1f:c7:73:e0
Signal : 65%
Radio type : 802.11n
Channel : 5
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156
BSSID 2 : 66:6a:1f:c7:73:e3
Signal : 48%
Radio type : 802.11ac
Channel : 40
Basic rates (Mbps) : 58.5
Other rates (Mbps) : 12 18 24 36 48 54 526.5

SSID 4 :
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : f8:2d:c0:da:7a:73
Signal : 76%
Radio type : 802.11ac
Channel : 157
Basic rates (Mbps) : 58.5
Other rates (Mbps) : 12 18 24 36 48 54 526.5

SSID 5 : DIRECT-EC-HP DeskJet 2700 series
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : f8:0d:ac:84:a6:ed
Signal : 61%
Radio type : 802.11n
Channel : 1
Basic rates (Mbps) : 39
Other rates (Mbps) : 18 19.5 24 36 48 54 156

SSID 6 : ATTN3qsitC
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : e0:1f:2b:94:25:8c
Signal : 31%
Radio type : 802.11ax
Channel : 112
Basic rates (Mbps) : 24 58.5
Other rates (Mbps) : 12 18 36 48 54 526.5
BSSID 2 : e0:1f:2b:94:25:88
Signal : 41%
Radio type : 802.11ax
Channel : 48
Basic rates (Mbps) : 24 58.5
Other rates (Mbps) : 12 18 36 48 54 526.5
BSSID 3 : e0:1f:2b:94:25:84
Signal : 65%
Radio type : 802.11n
Channel : 6
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 7 : ATTHiJwT8d
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : d0:fc:d0:b0:c1:c4
Signal : 21%
Radio type : 802.11n
Channel : 11
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 8 : B speaker.o,
Network type : Infrastructure
Authentication : Open
Encryption : None
BSSID 1 : fa:8f:ca:7c:02:7c
Signal : 36%
Radio type : 802.11n
Channel : 6
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 9 : ATTQ4yEsys
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : 18:b8:1f:04:1a:90
Signal : 48%
Radio type : 802.11n
Channel : 2
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156
BSSID 2 : 06:b8:1f:04:1a:93
Signal : 21%
Radio type : 802.11ac
Channel : 149
Basic rates (Mbps) : 58.5
Other rates (Mbps) : 12 18 24 36 48 54 526.5

SSID 10 : Middle-Earth-2.4GHz
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : 28:74:f5:bc:64:b4
Signal : 88%
Radio type : 802.11n
Channel : 11
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 11 : ATTvMmrm7i
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : f4:17:b8:dc:a1:a8
Signal : 25%
Radio type : 802.11ax
Channel : 153
Basic rates (Mbps) : 12 24 58.5
Other rates (Mbps) : 18 36 48 54 526.5
BSSID 2 : e6:ae:34:2d:4d:a3
Signal : 58%
Radio type : 802.11ac
Channel : 153
Basic rates (Mbps) : 58.5
Other rates (Mbps) : 12 18 24 36 48 54 526.5
BSSID 3 : f4:17:b8:dc:a1:a7
Signal : 38%
Radio type : 802.11ax
Channel : 52
Basic rates (Mbps) : 24 58.5
Other rates (Mbps) : 12 18 36 48 54 526.5
BSSID 4 : fc:ae:34:2d:4d:a0
Signal : 71%
Radio type : 802.11n
Channel : 3
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156
BSSID 5 : f4:17:b8:dc:a1:a6
Signal : 53%
Radio type : 802.11n
Channel : 6
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 12 : ATTWIxaZur
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : d0:fc:d0:c2:eb:dc
Signal : 20%
Radio type : 802.11ax
Channel : 132
Basic rates (Mbps) : 24 58.5
Other rates (Mbps) : 12 18 36 48 54 526.5
BSSID 2 : d0:fc:d0:c2:eb:d8
Signal : 20%
Radio type : 802.11ax
Channel : 52
Basic rates (Mbps) : 24 58.5
Other rates (Mbps) : 12 18 36 48 54 526.5

SSID 13 : DivyaB
Network type : Infrastructure
Authentication : WPA3-Personal
Encryption : CCMP
BSSID 1 : c4:eb:42:84:3a:b6
Signal : 30%
Radio type : 802.11ax
Channel : 1
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 14 : ATT2Y3DAtc
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : 0c:7c:28:ac:4e:94
Signal : 13%
Radio type : 802.11ax
Channel : 161
Basic rates (Mbps) : 24 58.5
Other rates (Mbps) : 12 18 36 48 54 526.5

SSID 15 : ATTJ7BE57i
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : 60:d2:48:3b:c5:30
Signal : 46%
Radio type : 802.11n
Channel : 5
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 16 : Middle-Earth-Guest
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : 28:74:f5:bc:64:b5
Signal : 88%
Radio type : 802.11n
Channel : 11
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 17 : WASHINGTON-1144
Network type : Infrastructure
Authentication : WPA3-Personal
Encryption : CCMP
BSSID 1 : b0:fc:88:d3:c9:76
Signal : 23%
Radio type : 802.11ax
Channel : 11
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 18 : ATTUixfQ5J
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : 80:ab:4d:91:3b:74
Signal : 28%
Radio type : 802.11n
Channel : 1
Basic rates (Mbps) : 24 39 156
Other rates (Mbps) : 18 19.5 36 48 54

SSID 19 : Williamson1966
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : 6c:4b:b4:83:ae:f4
Signal : 25%
Radio type : 802.11n
Channel : 6
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 20 : ATTuVq4NuI
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : 6c:4b:b4:2e:46:64
Signal : 31%
Radio type : 802.11n
Channel : 6
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

SSID 21 : Cano
Network type : Infrastructure
Authentication : WPA2-Personal
Encryption : CCMP
BSSID 1 : e0:1f:2b:35:38:c4
Signal : 26%
Radio type : 802.11n
Channel : 1
Basic rates (Mbps) : 6.5 16 19.5 117
Other rates (Mbps) : 18 19.5 24 36 39 48 54 156

[Bilb0_]

This is run on the W540.

These are my SSIDs:
SSID 2 : Middle-Earth-5GHz
SSID 10 : Middle-Earth-2.4GHz
SSID 16 : Middle-Earth-Guest

As a side note, notice there is no "Band" category between "Radio Type" and "Channel."

[Bilb0_]

I'll run this on the subject laptop (T520) a little later as it is currently in use elsewhere.

[johnnyL]

Let me know when you are available to test again.

[aurelien_vba]

thank you for the code