Results 1 to 7 of 7
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts

    Find available UNC paths

    Hey Y'all,

    I've been googling, without success, for some code to return the available UNC paths.
    Just so you know what I'm trying to accomplish...I have an Access database with FE/BE setup when I move my laptop from my home network to the network in our HOA office I'd like the code to automatically find the BE database on a networked HD that is NOT mapped! In otherwords I need to discover the available UNC paths when I hookup to that network via Wi-Fi.

    I have some code, based on Computer Name and UserId that will either hookup to a network drive or the local Documents folder if the BE can not be found on the network drive (both my home network and the HOA network have NAS drives attached). However, this does not attach my computer to the HOA NAS only to my home network NAS or my Documents folder. I can do this with more logic but then it gets messy and I'm looking for a more elegant method. BTW there is also a laptop used by the Architectural Review Committee thats also needs to hookup in this manner.

    The "net view" command returns the information I want in a command window but I don't know how to do it in VBA.

    Ideas?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    33
    Thanks
    4
    Thanked 3 Times in 3 Posts
    Try http://www.j.nurick.dial.pipex.com/C.../vbPerlish.htm
    grab all the code and put it in a module.
    You'll need to download shell32 code also, the site tells you where to get it.
    (download the zip and extract it. pull the code you need from shell32.bas file)
    I loaded it on an access database it was surprised that it worked.
    Took me a bit to figure out how to define the return variable as there were no examples.
    My test (not including the above code loading into a macro)
    Sub asdf()
    Dim n As Long
    Dim myd As Variant
    myd = qx("net view")
    For n = LBound(myd) To UBound(myd)
    MsgBox myd(n)
    Next n
    End Sub

    ---
    I hope this helps.

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts
    I would think
    Code:
    createobject("WScript.Shell").Exec("Net view").StdOut.ReadAll
    would return the results of net view as a string that you can then parse with Split
    Regards,
    Rory
    Microsoft MVP - Excel.

  5. The Following 2 Users Say Thank You to rory For This Useful Post:

    orangehat (2012-11-29),RetiredGeek (2012-11-29)

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Rory,

    Thanks a bunch!
    That's just what I needed.

    Since I know what I'm looking for and what is returned is rather messy (Headings & Lines) to use SPLIT on I decided to use INSTR to search with this code.
    Code:
    Option Explicit
    
    Sub Test()
    
       Dim zNVPaths As String
       Dim iCntr    As Integer
       Dim zUNCs(2) As String
       
       zUNCs(0) = "\\WD-NETCENTER"
       zUNCs(1) = "\\GOFLEX_HOME"
       
       zNVPaths = CreateObject("WScript.Shell").Exec("Net view").StdOut.readall
       
       For iCntr = 0 To UBound(zUNCs) - 1
           If InStr(zNVPaths, zUNCs(iCntr)) > 0 Then
             MsgBox "Found"
             Exit For
           End If
       Next iCntr
          
    End Sub
    Only one of the networks will be in range at a given time so this will work nicely.
    Of course, I'll put code where the MsgBox is in actual use.

    Thanks again.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #5
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,616
    Thanks
    7
    Thanked 231 Times in 219 Posts
    Surely the NAS drives have names and you can just ping them to see which, if any, is available?

    cheers, Paul

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Orangehat,

    Thanks for the post. It looks like a little more than I need at the moment but I'll surely be studying it for methods and ideas for the future.

    Thanks again.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #7
    Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    33
    Thanks
    4
    Thanked 3 Times in 3 Posts
    Rory, Nice Code!
    I found a use for this also. I added a split to loop though it using a 'For loop' after it by adding a split to your code. Works Great!
    zNVPaths = Split(CreateObject("WScript.Shell").Exec("Net view").StdOut.readall, vbCrLf)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •