DFS wrote:
I need to scan various network folders and capture all the filenames and
create dates.
================================================== =====
1st effort used the typical drive scan
cFile = Dir(sharedPath)
Do While cFile <""
db.Execute("INSERT INTO Table (FileName, blah blah)
cFile = Dir
Loop
But it's very slow when iterating 25,000 files across a slow corporate
network.
================================================== =====
2nd effort - shell out to DOS, run .bat to capture folder
contents in text file, import text file to Access
nwFolder = "dir """ & sharedPath & """ /t:w " & fileListing & ""
Open "C:\docPath.bat" For Output As #1
Print #1, nwFolder
Close #1
v = Shell("C:\docPath.bat", vbHide)
'wait 5 seconds
DoCmd.TransferText acImportFixed, "Import Spec", "tempTable", fileListing,
False
This is fast, but it's just plain ugly. And Shell runs asynchronously so
that's an issue to deal with (this runs in a loop so if I don't put pauses
after the Shell statement it soon errors out).
I don't think I'm interested in ShellWait. I want a simpler solution if
possible.
================================================== =====
3rd effort: suggestions from cdma?
Thanks
If you wanted to look at the Wait for Shell option, check it out here:
http://www.mvps.org/access/api/api0004.htm
Or There are API's that can do the file finding very quickly. Here is a
sample code from the KPD-Team's API-Guide (
http://www.allapi.net/).
Watch for line wrap:
'Create a form with a command button (command1), a list box (list1)
'and four text boxes (text1, text2, text3 and text4).
'Type in the first textbox a startingpath like c:\
'and in the second textbox you put a pattern like *.* or *.txt
Private Declare Function FindFirstFile Lib "kernel32" Alias
"FindFirstFileA" (ByVal lpFileName As String, lpFindFileData As
WIN32_FIND_DATA) As Long
Private Declare Function FindNextFile Lib "kernel32" Alias
"FindNextFileA" (ByVal hFindFile As Long, lpFindFileData As
WIN32_FIND_DATA) As Long
Private Declare Function GetFileAttributes Lib "kernel32" Alias
"GetFileAttributesA" (ByVal lpFileName As String) As Long
Private Declare Function FindClose Lib "kernel32" (ByVal hFindFile As
Long) As Long
Const MAX_PATH = 260
Const MAXDWORD = &HFFFF
Const INVALID_HANDLE_VALUE = -1
Const FILE_ATTRIBUTE_ARCHIVE = &H20
Const FILE_ATTRIBUTE_DIRECTORY = &H10
Const FILE_ATTRIBUTE_HIDDEN = &H2
Const FILE_ATTRIBUTE_NORMAL = &H80
Const FILE_ATTRIBUTE_READONLY = &H1
Const FILE_ATTRIBUTE_SYSTEM = &H4
Const FILE_ATTRIBUTE_TEMPORARY = &H100
Private Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type
Private Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As FILETIME
ftLastAccessTime As FILETIME
ftLastWriteTime As FILETIME
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type
Function StripNulls(OriginalStr As String) As String
If (InStr(OriginalStr, Chr(0)) 0) Then
OriginalStr = Left(OriginalStr, InStr(OriginalStr, Chr(0)) - 1)
End If
StripNulls = OriginalStr
End Function
Function FindFilesAPI(path As String, SearchStr As String, FileCount As
Integer, DirCount As Integer)
'KPD-Team 1999
'E-Mail:
KP*****@Allapi.net
Dim FileName As String ' Walking filename variable...
Dim DirName As String ' SubDirectory Name
Dim dirNames() As String ' Buffer for directory name entries
Dim nDir As Integer ' Number of directories in this path
Dim i As Integer ' For-loop counter...
Dim hSearch As Long ' Search Handle
Dim WFD As WIN32_FIND_DATA
Dim Cont As Integer
If Right(path, 1) <"\" Then path = path & "\"
' Search for subdirectories.
nDir = 0
ReDim dirNames(nDir)
Cont = True
hSearch = FindFirstFile(path & "*", WFD)
If hSearch <INVALID_HANDLE_VALUE Then
Do While Cont
DirName = StripNulls(WFD.cFileName)
' Ignore the current and encompassing directories.
If (DirName <".") And (DirName <"..") Then
' Check for directory with bitwise comparison.
If GetFileAttributes(path & DirName) And
FILE_ATTRIBUTE_DIRECTORY Then
dirNames(nDir) = DirName
DirCount = DirCount + 1
nDir = nDir + 1
ReDim Preserve dirNames(nDir)
End If
End If
Cont = FindNextFile(hSearch, WFD) 'Get next subdirectory.
Loop
Cont = FindClose(hSearch)
End If
' Walk through this directory and sum file sizes.
hSearch = FindFirstFile(path & SearchStr, WFD)
Cont = True
If hSearch <INVALID_HANDLE_VALUE Then
While Cont
FileName = StripNulls(WFD.cFileName)
If (FileName <".") And (FileName <"..") Then
FindFilesAPI = FindFilesAPI + (WFD.nFileSizeHigh *
MAXDWORD) + WFD.nFileSizeLow
FileCount = FileCount + 1
List1.AddItem path & FileName
End If
Cont = FindNextFile(hSearch, WFD) ' Get next file
Wend
Cont = FindClose(hSearch)
End If
' If there are sub-directories...
If nDir 0 Then
' Recursively walk into them...
For i = 0 To nDir - 1
FindFilesAPI = FindFilesAPI + FindFilesAPI(path &
dirNames(i) & "\", SearchStr, FileCount, DirCount)
Next i
End If
End Function
Sub Command1_Click()
Dim SearchPath As String, FindStr As String
Dim FileSize As Long
Dim NumFiles As Integer, NumDirs As Integer
Screen.MousePointer = vbHourglass
List1.Clear
SearchPath = Text1.Text
FindStr = Text2.Text
FileSize = FindFilesAPI(SearchPath, FindStr, NumFiles, NumDirs)
Text3.Text = NumFiles & " Files found in " & NumDirs + 1 & "
Directories"
Text4.Text = "Size of files found under " & SearchPath & " = " &
Format(FileSize, "#,###,###,##0") & " Bytes"
Screen.MousePointer = vbDefault
End Sub
--
Bri
