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

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

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" <(E-Mail Removed)> wrote in message
news:9B8D2CE3-C321-477C-8A78-(E-Mail Removed)...
> 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
>
>

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
>
>

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
>
>