I'm just wondering, in order to try and rule out possibilities in my
troubleshooting, whether I should experience any problems in accessing
files on a mapped drive using BWS and VBA. I wouldn't think so, but I need
to be sure.
When I set a data path constant to 'x:\bws\selfpay\data', I get an
invalid directory error. I have a function that loops through files in a
directory and returns the file names in an array; this allows me to import
multiple files at once. I've copied the function below. Based on my
debugging, I believe the error is generated because the GetAttr(DataDir)
does not equal vbDirectory. What I don't know is why. I don't know if
there's something inherent in a directory on a mapped drive that will
change the number of the attribute, or if something else is going on.
Anyone have any ideas?
Function GetAllFilesInDir(ByVal DataDir As String) As Variant
' Loop through the directory specified in strDirPath and save each
' file name in an array, then return that array to the calling
' procedure.
' Return False if strDirPath is not a valid directory.
Dim strTempName As String
Dim varFiles() As Variant
Dim lngFileCount As Long
On Error GoTo GetAllFiles_Err
' Make sure that strDirPath ends with a "\" character.
If right$(DataDir, 1) <> "\" Then
DataDir = DataDir & "\"
End If
' Make sure strDirPath is a directory.
If GetAttr(DataDir) = vbDirectory Then
strTempName = Dir(DataDir, vbDirectory)
Do Until Len(strTempName) = 0
' Exclude ".", "..".
If (strTempName <> ".") And (strTempName <> "..") And
(right(strTempName, 3) <> "rnd") Then
' Make sure we do not have a sub-directory name.
If (GetAttr(DataDir & strTempName) _
And vbDirectory) <> vbDirectory Then
' Increase the size of the array
' to accommodate the found filename
' and add the filename to the array.
ReDim Preserve varFiles(lngFileCount)
varFiles(lngFileCount) = strTempName
lngFileCount = lngFileCount + 1
End If
End If
' Use the Dir function to find the next filename.
strTempName = Dir()
Loop
' Return the array of found files.
GetAllFilesInDir = varFiles
End If
GetAllFiles_End:
Exit Function
GetAllFiles_Err:
GetAllFilesInDir = False
Resume GetAllFiles_End
End Function
Henry Taylor
Applications Analyst
Lutheran Health Network
Ph. (260) 425-3914
