Solved: Shared network drive issue

[slang]

Hello once again.
I have an application that has datafiles on a shared network drive that it needs to access. For the people in the field the code checks to see if they are connected to is and if not connects to a specific drive "I" so that seems to work for them.

My problem is that people in the office are connected to many shares and changing them around will cause havock for sure.

How would I reference these files depending which drive letter they have it mapped to?

In my code I have references like
activityworkbook = "I:\BDMSFA\BDMdatafiles\" & x & "activity.xls"
which the "I" drive refers to the share called Field$

Should I have code that checks to see if the Field$ is mapped and if it is have that drive as a variable that I use in the reference?

I dont know the best rout for this but I know it must be simple so I do not cause havock in the office.
That could be a "CLM"
Career Limiting Move
</IMG></IMG></IMG></IMG>

[Kenneth Hobs]

[VBA]Dim f as String
f = "I:\BDMSFA\BDMdatafiles\" & x & "activity.xls"
If Dir(f)="" Then
MsgBox f & " does not exist."
Else
MsgBox f & " exists."
End If[/VBA]

[slang]

Could I search the users computer to see if the share is mapped and if it is use that drive in the reference?

Kind of like
for x = A to Z
if true drive=x

reference file as "drive:\BDMSFA\BDMdatafiles\" & x & "activity.xls""

UUUGGGLLYYY I know!

[Kenneth Hobs]

If you have WScript installed and enabled, scrrun.dll:
[VBA]Sub ShowNetworkDrives()
Dim d As Object
Dim msg As String
Dim dDriveType As String

For Each d In CreateObject("Scripting.FileSystemObject").Drives
dDriveType = d.DriveType
'3=Newtwork Drive, 6=Network CDROM with CDFS file system
If d.IsReady = True And (dDriveType = 3 Or dDriveType = 6) Then
msg = msg & d.driveletter & vbNewLine
End If
Next
If msg = "" Then msg = "No network drive found."
MsgBox msg
End Sub[/VBA]

[malik641]

There's code to find the UNC path of a mapped drive letter and you can use that to check to see if any of the drive letters point to the specific folder on your network like this:

[vba]Option Explicit

Const networkFolder As String = "\\Server\Folder1"

Public Sub NetworkMapDrive()
' code modified from:
' http://www.dailydoseofexcel.com/arch...6/21/unc-path/

Dim WshNetwork As Object
Dim oDrives As Object
Dim DrivesStr As String
Dim i As Long

Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
DrivesStr = "Network drive Mappings:" & Chr(13)

For i = 0 To oDrives.Count - 1 Step 2
'DrivesStr = DrivesStr & "Drive " & oDrives.Item(i) & " = " & oDrives.Item(i + 1) & Chr(13)
If (oDrives.Item(i + 1) = networkFolder) Then
MsgBox "You have the drive mapped!"
End If
Next

MsgBox DrivesStr
End Sub[/vba]
So if they have the folder on the server that you're looking for, you know that they have that path mapped to a drive that they can access.

This is untested because I don't have a network at home to test it with, but I think that will do it.

Hope this helps.

[slang]

So this should work when referencing files on this share regardless of the letter the user has it mapped to?

[vba]Public Sub NetworkMapDrive()

Dim WshNetwork As Object
Dim oDrives As Object
Dim searchdrive As String
Dim userdrive As String
Dim i As Long
searchdrive = "\\apwfs01\ho_region$"
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
For i = 0 To oDrives.Count - 1 Step 2
If (oDrives.Item(i + 1) = searchdrive) Then
userdrive = oDrives.Item(i)
End If
Next
MsgBox "You have the drive mapped! to " & oDrives.Item(i) & Chr(13) & userdrive

'ChDir userdrive & "\BDMSFA\BDMdatafiles\backups"
'Workbooks.Open Filename:=userdrive & "\BDMSFA\BDMdatafiles\backups\1activity.xls"
End Sub[/vba]

Is this the correct way to reference these files or is there a better way?

Thanks again all.....

[Kenneth Hobs]

Run it to see if it works. If it does, you will want to remove the MsbBox part.

You could just use the UNC paths without a drive letter. I like drive letters though. Of course some functions don't work with UNC paths like DIR().

I typically use DIR() to determine if a file or folder exists. Here is a method to check for a UNC file's existence.
[vba]
Sub Test()
Dim f As String, rc As Boolean
f = "\\matpc10\excelvbaexamples\_vba express\txt\txt.xls"
rc = FileExists(f)
Debug.Print f, rc
If rc Then Workbooks.Open f

f = ThisWorkbook.FullName
Debug.Print f, FileExists(f)
End Sub

Function FileExists(sFilename As String) As Boolean
Dim fso As Object, tf As Boolean
Set fso = CreateObject("Scripting.FileSystemObject")
tf = fso.FileExists(sFilename)
Set fso = Nothing
FileExists = tf
End Function
[/vba]