Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to see if mapped drive exists
Office 2007 SP1; XP Pro SP2
I have the following code to determine if a given path has been mapped on a user's machine: Function fn_Test_4_Drive() As Integer fn_Test_4_Drive = 0 'Drive does not exist Dim fs, d, dc, n Set fs = CreateObject("Scripting.FileSystemObject") Set dc = fs.Drives For Each d In dc ' Debug.Print d.sharename If InStr(1, d.sharename, "\\crpatlfnp03\temp") 0 Then fn_Test_4_Drive = 1 Exit Function End If Next d ret = MsgBox("It appears you do not have a connection to the drive \\crpatlfnp03\temp." & Chr(13) & _ "Please get this drive mapped for future wires.", vbOKOnly, "Missing drive!") End Function Several users have the test to fail, even if the drive is mapped. Initially I used an if fs.folderexists test, but it gave me the same erroneous results so I went to the above Function. Note the debug print that I used to confirm that the d.sharename being returned was indeed the value being tested for. Is there any reason these tests should function on some machines but not on others? All machines are running Office 2007 SP1; XP Pro SP2 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to see if mapped drive exists
I don't have a network drive to test, but are you sure that there isn't a
difference in case. I'd use: if InStr(1, d.sharename, "\\crpatlfnp03\temp", vbtextcompare) 0 then Did you see uppercase letters in any of those debug.print statements? BAC wrote: Office 2007 SP1; XP Pro SP2 I have the following code to determine if a given path has been mapped on a user's machine: Function fn_Test_4_Drive() As Integer fn_Test_4_Drive = 0 'Drive does not exist Dim fs, d, dc, n Set fs = CreateObject("Scripting.FileSystemObject") Set dc = fs.Drives For Each d In dc ' Debug.Print d.sharename If InStr(1, d.sharename, "\\crpatlfnp03\temp") 0 Then fn_Test_4_Drive = 1 Exit Function End If Next d ret = MsgBox("It appears you do not have a connection to the drive \\crpatlfnp03\temp." & Chr(13) & _ "Please get this drive mapped for future wires.", vbOKOnly, "Missing drive!") End Function Several users have the test to fail, even if the drive is mapped. Initially I used an if fs.folderexists test, but it gave me the same erroneous results so I went to the above Function. Note the debug print that I used to confirm that the d.sharename being returned was indeed the value being tested for. Is there any reason these tests should function on some machines but not on others? All machines are running Office 2007 SP1; XP Pro SP2 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to see if mapped drive exists
I can't test it because I don't have a network here, however the simple API
call listed here seems like it should do what you want... http://vbnet.mvps.org/index.html?cod...fileexists.htm -- Rick (MVP - Excel) "BAC" wrote in message ... Office 2007 SP1; XP Pro SP2 I have the following code to determine if a given path has been mapped on a user's machine: Function fn_Test_4_Drive() As Integer fn_Test_4_Drive = 0 'Drive does not exist Dim fs, d, dc, n Set fs = CreateObject("Scripting.FileSystemObject") Set dc = fs.Drives For Each d In dc ' Debug.Print d.sharename If InStr(1, d.sharename, "\\crpatlfnp03\temp") 0 Then fn_Test_4_Drive = 1 Exit Function End If Next d ret = MsgBox("It appears you do not have a connection to the drive \\crpatlfnp03\temp." & Chr(13) & _ "Please get this drive mapped for future wires.", vbOKOnly, "Missing drive!") End Function Several users have the test to fail, even if the drive is mapped. Initially I used an if fs.folderexists test, but it gave me the same erroneous results so I went to the above Function. Note the debug print that I used to confirm that the d.sharename being returned was indeed the value being tested for. Is there any reason these tests should function on some machines but not on others? All machines are running Office 2007 SP1; XP Pro SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to see if mapped drive exists
I'm not following what you're doing exactly, but I've had issues with
"mapped" drives if the WebClient service isn't started. I've put this in the ThisWorkbook module of my Personal folder to get around it. HTH, Barb Reinhardt Option Explicit Private Sub Workbook_Open() Dim objWMIService, objItem Dim colListOfServices As Object Dim objService As Object Dim strComputer, strService, intSleep strComputer = "." intSleep = 15000 strService = " 'WebClient' " Set objWMIService = GetObject("winmgmts:" _ & "{impersonationLevel=impersonate}!\\" _ & strComputer & "\root\cimv2") Set colListOfServices = objWMIService.ExecQuery _ ("Select * from Win32_Service Where Name =" _ & strService & " ") For Each objService In colListOfServices Debug.Print objService.Name, objService.State If objService.State = "Stop Pending" Then MsgBox ("You'll need to reboot your computer." & vbNewLine & _ "The WebClient Service has a stop pending.") ElseIf objService.State = "Stopped" Then Application.StatusBar = "Starting WebClient" objService.StartService Application.StatusBar = False End If Next End Sub "BAC" wrote: Office 2007 SP1; XP Pro SP2 I have the following code to determine if a given path has been mapped on a user's machine: Function fn_Test_4_Drive() As Integer fn_Test_4_Drive = 0 'Drive does not exist Dim fs, d, dc, n Set fs = CreateObject("Scripting.FileSystemObject") Set dc = fs.Drives For Each d In dc ' Debug.Print d.sharename If InStr(1, d.sharename, "\\crpatlfnp03\temp") 0 Then fn_Test_4_Drive = 1 Exit Function End If Next d ret = MsgBox("It appears you do not have a connection to the drive \\crpatlfnp03\temp." & Chr(13) & _ "Please get this drive mapped for future wires.", vbOKOnly, "Missing drive!") End Function Several users have the test to fail, even if the drive is mapped. Initially I used an if fs.folderexists test, but it gave me the same erroneous results so I went to the above Function. Note the debug print that I used to confirm that the d.sharename being returned was indeed the value being tested for. Is there any reason these tests should function on some machines but not on others? All machines are running Office 2007 SP1; XP Pro SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to see if mapped drive exists
I have found that the "fs" object does not work in XL2007 (not where I am,
anyway). Since we moved to XL2007 I have had to switch to using "Dir" when I want to search a directory in VBA code. -- Time is just the thing that keeps everything from happening all at once "BAC" wrote: Office 2007 SP1; XP Pro SP2 I have the following code to determine if a given path has been mapped on a user's machine: Function fn_Test_4_Drive() As Integer fn_Test_4_Drive = 0 'Drive does not exist Dim fs, d, dc, n Set fs = CreateObject("Scripting.FileSystemObject") Set dc = fs.Drives For Each d In dc ' Debug.Print d.sharename If InStr(1, d.sharename, "\\crpatlfnp03\temp") 0 Then fn_Test_4_Drive = 1 Exit Function End If Next d ret = MsgBox("It appears you do not have a connection to the drive \\crpatlfnp03\temp." & Chr(13) & _ "Please get this drive mapped for future wires.", vbOKOnly, "Missing drive!") End Function Several users have the test to fail, even if the drive is mapped. Initially I used an if fs.folderexists test, but it gave me the same erroneous results so I went to the above Function. Note the debug print that I used to confirm that the d.sharename being returned was indeed the value being tested for. Is there any reason these tests should function on some machines but not on others? All machines are running Office 2007 SP1; XP Pro SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links to mapped drive change to refer to local hard drive | Links and Linking in Excel | |||
pathnames to linked workbooks change from mapped drive to UNC occasionally | Links and Linking in Excel | |||
How do I get the true path to server (not mapped drive letter)? | Excel Worksheet Functions | |||
Excel 2003 - problem saving to a mapped network drive | Excel Discussion (Misc queries) | |||
Excel converts Mapped drive to UNC - How to stop? | Links and Linking in Excel |