Looping through file names in a folder

[cruford]

Here is what I have so far:

Private Sub cmdOpenFile_Click()
On Error GoTo Err_cmdOpenFile_Click
Dim strLine As String
Dim intLength As Integer
Dim intPos As Integer
Dim strSpace As String
Dim strSerialNumber As String
Dim strSQL As String
Dim strHostName As String
Dim strTemp As String
Dim strFileName As String
dlgCommon.Filter = ("Text Files (*.txt) | *.txt&quot
strEquals = "="
dlgCommon.ShowOpen
Open dlgCommon.FileName For Input As #1
Do
Input #1, strLine
Loop Until Left(strLine, 13) = "Serial Number"
'sets the serial number from file
intLength = Len(strLine) 'checks length of string
intPos = InStr(strLine, strEquals) 'checks for "=" sign
strSerialNumber = Right(strLine, (intLength - (intPos + 1))) ' gets all characters after "=" sign
'sets the hostname from file
intLength = Len(dlgCommon.FileName) 'Gets length of file and path
strTemp = Right(dlgCommon.FileName, (intLength - 18)) 'truncates the path from file name
intLength = Len(strTemp) 'gets length of file name
strHostName = Left(strTemp, (intLength - 4)) 'truncates extension from file name
txtSerialNum = strSerialNumber
txtHostName = strHostName
strSQL = "INSERT INTO [tblserialnum] values ('" & strSerialNumber & "', '" & strHostName & "')"
DoCmd.RunSQL strSQL
Close #1
Exit Sub

Err_cmdOpenFile_Click:
Close #1
If Err.Number <> 32755 Then
MsgBox Err.Description, vbOKOnly, &quot;Error &quot; & Err.Number
End If
End Sub

What I would like to do is eliminate having to open the file manually. How can I loop through a directory (ex. m:\logs) and get each file name, stuff it in a variable the have the code open and use that. When it's through have it loop again and read the next file name in the directory.

 

[GHolden]

The easiest way to loop through file names in a folder is to use dir.

this example loops through c:\ and adds each file name to the mDirListing. There is more help on Dir in the Access help.


Function mDirListing(ByVal vstrPath As String) As String
Dim strCurrentFile As String

strCurrentFile = Dir(vstrPath & &quot;*.*&quot

If strCurrentFile <> vbNullString Then
mDirListing = strCurrentFile

Do While strCurrentFile <> vbNullString
strCurrentFile = Dir
If strCurrentFile <> vbNullString Then
mDirListing = mDirListing & vbCrLf & strCurrentFile
End If
Loop

End If

End Function

Sub test()
MsgBox mDirListing(&quot;C:\&quot
End Sub

There are two ways to write error-free programs; only the third one works.

 

[beetee]

The DIR function cannot be called recursively. Here is some code that will recursively print out file/directory names:



Option Explicit

Dim Recurse As Integer
Dim RowCount As Integer

Dim PathStack() As String
Dim PathStackEls As Integer
Dim PathStackSize As Integer

Sub PushDir(DirName As String)
If PathStackEls >= PathStackSize Then
PathStackSize = PathStackSize + 32
ReDim Preserve PathStack(PathStackSize)
End If
PathStack(PathStackEls) = DirName
PathStackEls = PathStackEls + 1
End Sub

Function PopDir() As String
PathStackEls = PathStackEls - 1
PopDir = PathStack(PathStackEls)
End Function

Sub Populate(ParentDir As String)
Recurse = Recurse + 1
Dim FileSpec As String
FileSpec = ParentDir & &quot;\*.*&quot;
Dim CurFileName As String
CurFileName = Dir(FileSpec, vbDirectory)
Do While CurFileName <> &quot;&quot;
If Not (CurFileName = &quot;.&quot; Or CurFileName = &quot;..&quot Then
DoEvents
Dim MyAttr As Integer
Dim FullFileName As String
FullFileName = ParentDir & &quot;\&quot; & CurFileName
On Error Resume Next
MyAttr = GetAttr(FullFileName)
If (MyAttr And vbDirectory) = vbDirectory Then
Dim PrevFileName As String
PrevFileName = CurFileName
RowCount = RowCount + 1
Debug.Print FullFileName
PushDir FileSpec
Populate FullFileName
FileSpec = PopDir
CurFileName = Dir(FileSpec, vbDirectory)
Do While CurFileName <> &quot;&quot; And CurFileName <> PrevFileName
CurFileName = Dir
Loop
End If
End If
CurFileName = Dir
Loop
Recurse = Recurse - 1
End Sub

Sub DirTree(FileName As String)
RowCount = 1
Recurse = 1
Populate FileName
End Sub