Dir Function in VBA (Visual Basic)

Dir or Dir$ Function can be used in Microsoft Visual Basic (Applications) to get the list of directories or files

sDir = Dir$(sPath & "*.Doc", vbNormal)
Do Until LenB(sDir) = 0
Set oWB = Documents.Open(sPath & sDir)
‘ Do some stuff
oWB.close
sDir = Dir$
Loop

The above will open all Word Documents under a particular directory.
Cheers,
Chithu

Shared Name For the Drive

QueryDosDeviceW API Function can be used to get the device name.

To get the Shared Name of the drive, use the following function:-

Public Function ConvertDrive2ServerName(ByVal sFullPath As String) As String

' --- Replaces the DriveName with ShareName in a given string

Dim FSO As FileSystemObject
Dim sDrive As String
Dim drvName As Drive
Dim sShare As String
On Error GoTo Err_TrapSet
FSO = New FileSystemObject
sDrive = FSO.GetDriveName(sFullPath)
Set drvName = FSO.GetDrive(sDrive)
sShare = drvName.ShareName
If LenB(sShare) <> 0 Then
ConvertDrive2ServerName = Replace(sFullPath, sDrive, sShare, 1, 1, vbTextCompare)
Else
ConvertDrive2ServerName = sFullPath
End If
If Not FSO Is Nothing Then Set FSO = Nothing
' ---------------------------------------
' Error Handling'
'----------------------------------------
Err_Trap:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Function

The function returns the DriveName with ShareName in a given string. It is advisable for programmers to store all the file locations using Sharename instead of DriveName.

Cheers,
Chithu