DOS....there must be a better way

[Digory]

Hi all - hoping for a bit of advice.

Working on tool to check population of server drive; all subfolders & files (multi format). Have been using macro button/VBA to run shell DOS command (dir + switches) to return alphabetical list inc sub-directories + files plus their extension ie. Everything c3000 lines). The DOS command outputs to file “data.xls”. Next button/VBA gets data from “data.xls” and pastes values into column A of tool worksheet (“data”) named range “listing”. I then use a countif wildcard function to search "listing" and populate a conditionally formatted matrix.

It works (albeit slowly) but you can see its not elegant and I get annoying messages about data on clipboard. I am hoping that there is a VBA method of populating my data worksheet ……any ideas.

D:-)

[Jim Cone]

Digory,

My Excel add-in "List Files" may be able to do what you want.
It finds folders/files on your computer meeting criteria you specify.
It generates a list on a new worksheet showing...
folder/file path, name, type ,size and last save date.
A hyperlink is created for each file.
(one click will remove all hyperlinks)

It comes with a one page Word.doc install/use file.
It is available - free - upon email request.
Remove "xxx" from my email address.

Regards,
Jim Cone
San Francisco, CA
[email protected]XX



"Digory" wrote in message
news:[email protected]
Hi all - hoping for a bit of advice.

Working on tool to check population of server drive; all subfolders &
files (multi format). Have been using macro button/VBA to run shell DOS
command (dir + switches) to return alphabetical list inc sub-directories
+ files plus their extension ie. Everything c3000 lines). The DOS
command outputs to file “data.xls”. Next button/VBA gets data from
“data.xls” and pastes values into column A of tool worksheet (“data”)
named range “listing”. I then use a countif wildcard function to search
"listing" and populate a conditionally formatted matrix.

It works (albeit slowly) but you can see its not elegant and I get
annoying messages about data on clipboard. I am hoping that there is a
VBA method of populating my data worksheet ……any ideas.

D:-)
Digory

[Digory]

Jim,

Thanks for the very kind offer but sounds a bit more of a hot-rod than I need + I suspect there maybe issues with add-ins ..........

Was kind of hoping someone out there may have a no bells & whistles VBA method

D:-)

[Jim Cone]

Digory,
Ok, maybe you can roll your own...

Microsoft Windows Script 5.6 Documentation
http://msdn.microsoft.com/library/de...ist/webdev.asp
'----------------------------------
Option Explicit
Option Compare Text

Sub ListFoldersAndSubFolderAndFiles()
'Jim Cone - San Francisco, USA
'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll)
'List all files and folders in the specified folder.
'Adds list to column B on active sheet.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strPath As String
Dim strName As String
Dim lngNum As Long

'Specify the folder...
strPath = "C:\Documents and Settings"
'Specify the file to look for...
strName = "*.xls"
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)
lngNum = 2

For Each objFile In objFolder.Files
If objFile.Name Like strName Then
Cells(lngNum, 2) = objFile.Path
lngNum = lngNum + 1
End If
Next 'objFile
Set objFile = Nothing

'Call recursive function
DoTheSubFolders objFolder.SubFolders, lngNum, strName

Set objFSO = Nothing
Set objFolder = Nothing
End Sub
'------------------------

Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef lngN As Long, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long

For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
Cells(lngN, 2).Value = scrFile.Path
lngN = lngN + 1
End If
Next 'scrFile

'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count > 0 Then
DoTheSubFolders scrFolder.SubFolders, lngN, strTitle
End If
Next 'scrFolder

Set scrFile = Nothing
Set scrFolder = Nothing
End Function
'---------------------------------


"Digory"
wrote in message
news:[email protected]
Jim,
Thanks for the very kind offer but sounds a bit more of a hot-rod than
I need + I suspect there maybe issues with add-ins ..........
Was kind of hoping someone out there may have a no bells & whistles VBA
method
D:-)--
Digory

[RB Smissaert]

This recursive dir function will do the job:

Function RecursiveFindFiles(strPath As String, _
strSearch As String, _
Optional bSubFolders As Boolean = True, _
Optional bSheet As Boolean = False, _
Optional lFileCount As Long = 0, _
Optional lDirCount As Long = 0) As Variant

'adapted from the MS example:
'http://support.microsoft.com/default.aspx?scid=kb;en-us;185476
'---------------------------------------------------------------
'will list all the files in the supplied folder and it's
'subfolders that fit the strSearch criteria
'lFileCount and lDirCount will always have to start as 0
'---------------------------------------------------------------

Dim strFileName As String 'Walking strFileName variable.
Dim strDirName As String 'SubDirectory Name.
Dim arrDirNames() As String 'Buffer for directory name entries.
Dim nDir As Long 'Number of directories in this strPath.
Dim i As Long 'For-loop counter.
Dim n As Long
Dim arrFiles
Static strStartDirName As String
Static strpathOld As String

On Error GoTo sysFileERR

If lFileCount = 0 Then
Static collFiles As Collection
Set collFiles = New Collection
Application.Cursor = xlWait
End If

If Right$(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If

If lFileCount = 0 And lDirCount = 0 Then
strStartDirName = strPath
End If

'search for subdirectories
'-------------------------
nDir = 0

ReDim arrDirNames(nDir)

strDirName = Dir(strPath, _
vbDirectory Or _
vbHidden Or _
vbArchive Or _
vbReadOnly Or _
vbSystem) 'Even if hidden, and so on.


Do While Len(strDirName) > 0
'ignore the current and encompassing directories
'-----------------------------------------------
If (strDirName <> ".") And (strDirName <> "..") Then
'check for directory with bitwise comparison
'-------------------------------------------
If GetAttr(strPath & strDirName) And vbDirectory Then
arrDirNames(nDir) = strDirName
lDirCount = lDirCount + 1
nDir = nDir + 1
DoEvents
ReDim Preserve arrDirNames(nDir)
End If 'directories.
sysFileERRCont1:
End If
strDirName = Dir() 'Get next subdirectory

DoEvents
Loop

'Search through this directory
'-----------------------------
strFileName = Dir(strPath & strSearch, _
vbNormal Or _
vbHidden Or _
vbSystem Or _
vbReadOnly Or _
vbArchive)

While Len(strFileName) <> 0

'dump file in sheet
'------------------
If bSheet Then
If lFileCount < 65536 Then
Cells(lFileCount + 1, 1) = strPath & strFileName
End If
End If

lFileCount = lFileCount + 1

collFiles.Add strPath & strFileName

If strPath <> strpathOld Then
Application.StatusBar = " " & lFileCount & _
" " & strSearch & " files found. " & _
"Now searching " & strPath
End If

strpathOld = strPath

strFileName = Dir() 'Get next file

DoEvents
Wend

If bSubFolders Then
'If there are sub-directories..
'------------------------------
If nDir > 0 Then
'Recursively walk into them
'--------------------------
For i = 0 To nDir - 1
RecursiveFindFiles strPath & arrDirNames(i) & "\", _
strSearch, _
bSubFolders, _
bSheet, _
lFileCount, _
lDirCount

DoEvents
Next
End If 'If nDir > 0

If strPath & arrDirNames(i) = strStartDirName Then
ReDim arrFiles(1 To lFileCount) As String
For n = 1 To lFileCount
arrFiles(n) = collFiles(n)
Next
RecursiveFindFiles = arrFiles
Application.Cursor = xlDefault
Application.StatusBar = False
End If

Else 'If bSubFolders
ReDim arrFiles(1 To lFileCount) As String
For n = 1 To lFileCount
arrFiles(n) = collFiles(n)
Next
RecursiveFindFiles = arrFiles
Application.Cursor = xlDefault
Application.StatusBar = False
End If 'If bSubFolders

Exit Function
sysFileERR:

Resume sysFileERRCont1

End Function


Use it for example like this:

Sub test()

Dim i As Long
Dim arr

arr = RecursiveFindFiles("C:\", "*.*")

For i = 1 To UBound(arr)
Cells(i, 1) = arr(i)
Next

End Sub


RBS


"Digory" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all - hoping for a bit of advice.
>
> Working on tool to check population of server drive; all subfolders &
> files (multi format). Have been using macro button/VBA to run shell DOS
> command (dir + switches) to return alphabetical list inc sub-directories
> + files plus their extension ie. Everything c3000 lines). The DOS
> command outputs to file "data.xls". Next button/VBA gets data from
> "data.xls" and pastes values into column A of tool worksheet ("data")
> named range "listing". I then use a countif wildcard function to search
> "listing" and populate a conditionally formatted matrix.
>
> It works (albeit slowly) but you can see its not elegant and I get
> annoying messages about data on clipboard. I am hoping that there is a
> VBA method of populating my data worksheet ..any ideas.
>
> D:-)
>
>
> --
> Digory
> ------------------------------------------------------------------------
> Digory's Profile:
> http://www.excelforum.com/member.php...o&userid=16439
> View this thread: http://www.excelforum.com/showthread...hreadid=474447
>

[Digory]

Guys - thanks; will have a play & report back.

D:-)

[RB Smissaert]

The Sub test should actually be like this as the loop to dump the files is
not needed as it can be done already in
the funtion:

Sub test()

Dim i As Long
Dim arr

arr = RecursiveFindFiles("C:\", "*.*", True, True)

End Sub


RBS

"Digory" <[email protected]> wrote in
message news:[email protected]...
>
> Guys - thanks; will have a play & report back.
>
> D:-)
>
>
> --
> Digory
> ------------------------------------------------------------------------
> Digory's Profile:
> http://www.excelforum.com/member.php...o&userid=16439
> View this thread: http://www.excelforum.com/showthread...hreadid=474447
>

[Digory]

Guys,

Have had bit of a play / explore help utility with keeping it simple in mind. I think this has potential to do what I need:

Sub test()
With Application.FileSearch
.LookIn = "c:\my documents"
.FileType = msoFileTypeAllFiles
.Execute
For I = 1 To .FoundFiles.Count
MsgBox .FoundFiles(I)
Next I
End With
End Sub

Only problem is that it outputs to a MsgBox file by file
How can I get it to output to worksheet ?

Cheers

D:-)

[RB Smissaert]

Very simple.
Replace this:
MsgBox .FoundFiles(I)
with this:
Cells(I, 1) = .FoundFiles(I)

The recursive dir function will be faster though than this.

RBS


"Digory" <[email protected]> wrote in
message news:[email protected]...
>
> Guys,
>
> Have had bit of a play / explore help utility with keeping it simple in
> mind. I think this has potential to do what I need:
>
> Sub test()
> With Application.FileSearch
> LookIn = "c:\my documents"
> FileType = msoFileTypeAllFiles
> Execute
> For I = 1 To .FoundFiles.Count
> MsgBox .FoundFiles(I)
> Next I
> End With
> End Sub
>
> Only problem is that it outputs to a MsgBox file by file
> How can I get it to output to worksheet ?
>
> Cheers
>
> D:-)
>
>
> --
> Digory
> ------------------------------------------------------------------------
> Digory's Profile:
> http://www.excelforum.com/member.php...o&userid=16439
> View this thread: http://www.excelforum.com/showthread...hreadid=474447
>

[Digory]

Thats excellent - I like simple
Q: Can I get it to include subfolders ?

D:-)

[RB Smissaert]

It does already as it is (that is why it is recursive), look
at the arguments of the function:

Function RecursiveFindFiles(strPath As String, _
strSearch As String, _
Optional bSubFolders As Boolean = True, _
Optional bSheet As Boolean = False, _
Optional lFileCount As Long = 0, _
Optional lDirCount As Long = 0) As Variant

RBS


"Digory" <[email protected]> wrote in
message news:[email protected]...
>
> Thats excellent - I like simple
> Q: Can I get it to include subfolders ?
>
> D:-)
>
>
> --
> Digory
> ------------------------------------------------------------------------
> Digory's Profile:
> http://www.excelforum.com/member.php...o&userid=16439
> View this thread: http://www.excelforum.com/showthread...hreadid=474447
>

[Digory]

RBS,

We may have got our threads tangled as I was meaning the filesearch below rather than your recursive dir function.

Sub test()
With Application.FileSearch
.LookIn = "c:\my documents"
.FileType = msoFileTypeAllFiles
.Execute
For I = 1 To .FoundFiles.Count
Cells(I, 1) = .FoundFiles(I)
Next I
End With
End Sub

D:-)