Hello everyone, I'm writing some code for a senior seminar as a business major, however I am not a strong programmer. The project involves me writing code in VBA that will go through a file directory, and produce line by line in the cells in excel the file paths for each file in the prescribed listing.
This is what I have so far, it creates a new page, however, so far I am not getting any output, but it throws no errors. It is currently enabled to only pull other .xls files for testing's sake.
If it helps at all, the file structure in question looks something like the following
'Documents' --> 'Ian's Stuff' --> 'Ian's Stuff' --> 'Business Seminar in IT' --> 'Business Seminar in IT Notes'
Option Explicit
Sub FullDir()
ActiveWorkbook.Sheets.Add
GetFiles "c:\documents\", ".xls"
End Sub
Sub GetFiles(strRootDir As String, Optional strType As String)
Dim strDirName As String
Dim bTypeMatch As Boolean
Dim colDirs As Collection
Dim lDirCounter As Long
Dim lIndex As Long
Set colDirs = New Collection
colDirs.Add strRootDir
lDirCounter = 1
lIndex = 2
'check for sub directories and make a recursive call to the lowest level dirs first
Do While lDirCounter <= colDirs.Count
strRootDir = colDirs(lDirCounter)
strDirName = Dir(strRootDir, vbDirectory + vbNormal)
Do While strDirName <> ""
If strDirName <> "." And strDirName <> ".." Then
If (GetAttr(strRootDir & strDirName) And vbDirectory) = vbDirectory Then
'add to the directories collection so that this will be done later
colDirs.Add strRootDir & strDirName & "\"
Else
'we found a normal file
bTypeMatch = False
If strType = "*.*" Then
bTypeMatch = True
ElseIf UCase(Right(strDirName, Len(strType))) = UCase(strType) Then
bTypeMatch = True
End If
If bTypeMatch = True Then
'we found a valid file
Cells(lIndex, 1) = strRootDir & strDirName
lIndex = lIndex + 1
End If
End If
End If
strDirName = Dir
Loop
lDirCounter = lDirCounter + 1
Loop
End Sub