2

For reporting purposes, I'm importing data from various sources on the net into an Excel Workbook.

Problem is: while some of the stuff I want is on my company's private network (requiring cable connection), some of the URL/webservices I want to connect to are not allowed by my company's proxy (via cable connection).

Thus, I have to use another connection without proxy (via WiFi) to get those to work. This WiFi connection is always active on my computer, but it requires me to manually unplug the cable for half of my imports and to plug it back to access files on my company's network for the other half...

I wonder if there is a way with VBA to tell the computer to use cable / WiFi according to what I need.

I am working on a Windows XP Pro computer with Excel 2010/VBA. Please help!

Arthur
  • 174
  • 1
  • 1
  • 10
  • 3
    AFAIK, Using native VBA code you can't. Perhaps using C++, you can create a library for VBA and then you can. – Siddharth Rout Oct 30 '14 at 09:01
  • Thanks for this, don't know if I can go as far as trying to create a library for VBA (I'm not a developer), but I'll know where to start looking... Thanks again @SiddharthRout – Arthur Oct 31 '14 at 08:16
  • If it's really `important` then post a project in sites like freelancer.com/elance.com/odesk.com. There are lot of C++ Gurus out there. You might actually find someone to write the lib for you :) – Siddharth Rout Oct 31 '14 at 08:18
  • 1
    @Arthur found a way to enable or disable connection through vbs, but dont know if that serve your purpose. And do you have to check continuously if line is active or not? – ZAT Oct 31 '14 at 13:44
  • Hey @ZAT, maybe that would help, do you have that somewhere? I don't need to check continuously, just use one or another connection... Let me know. – Arthur Nov 03 '14 at 10:27
  • @Arthur see posted answer. Tested in windows 7 though. Perhaps will work for you. – ZAT Nov 03 '14 at 18:22

1 Answers1

2

Found Two ways to enable or disable i.e change internet adapter connection from VBA:

'adjust these according to your need i.e change adapter name, change enabled or disabled parameter, change path, change file name etc. VBS approach is long but gives more control. Bat approach is short but limited IMO

1 Using vbs Source link :

'file name: asdf.vbs
'Get and disable all physical wired adapters (AdapterTypeID=0) (Ethernet 802.3)
'Wscript.Echo "test"
Set wmi = GetObject("winmgmts:root\CIMV2")
Set adapters = wmi.ExecQuery("Select * from Win32_NetworkAdapter where _
AdapterTypeId=0 AND NetConnectionID IS NOT NULL",,48)

For Each adapter in adapters
   With adapter 
   WScript.Echo "available: " & .Availability & " enabled: " & .NetEnabled & " netconStatus: " & .NetConnectionStatus & " status: " & .Status & " netconnID: " & .NetConnectionID & " adType: " & .AdapterType

   'adapter.disable()
   'adapter.enable()
   End With
Next

' Get and disable all physical wireless adapters (AdapterTypeID=9) (wireless)

2 Using bat Ref link1 and Ref link2 :

'file name: switch_lan6.bat
@echo off
start /MIN cmd.exe /c "netsh interface set interface name="Local Area Connection 6" admin=disabled"

Running these inside excel vba:

Shell "wscript ""C:\Users\USER\Desktop\asdf.vbs""", vbNormalFocus

or,

Call Shell(ThisWorkbook.Path & "\switch_lan6.bat", 0)
Community
  • 1
  • 1
ZAT
  • 1,347
  • 7
  • 10
  • Great! I can list all available connections, but can't `disable()` them yet... I will try on later this week. Thanks a million! – Arthur Nov 04 '14 at 08:19
  • @Arthur Welcome. You can disable connection by `adapter.disable` in vbs and by `..admin=disabled` in bat. If purpose served, please mark as answer later. – ZAT Nov 04 '14 at 08:53