Execute DIR via shell and get results back into a table ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to execute DIR type search commands and get the results
into an Access table.
I downloaded some code from the web that does this via API calls but after
working on it for 2 days can't get it to work in all cases. It works if I
enter drive letters but if I put in any folder it refuses to run. The code is
way too voluminous to post.
So I was wondering if there is any way from VBA to run the DIR command,
redirect the output to a file, then import the file to a table. Is this a
feasible alternative?
 
Feasible, yes. You could certainly run a dos command along the lines of dir
*.jpg > filelist.txt and then import the resultant text file back in via
TransferText (or various other methods), but it's going to be far more
efficient just to use the Dir function within Access. There's no reason at
all why the code associated would need to be voluminous unless you're doing
something very strange.

I'd just take a look at that first.
 
Here is some code I use:

A few things:

This code also walks through all of the sub-dirs in a directory. So, if
there is 5 levels of dirs..this routine returns all of the entries back. It
does this by using a programming concpet called recursion.

Here is the sample code. The first sub shows you how to use the routine, and
then 2nd sub is the code:

Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer

startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

' lets printout the stuff into debug window for a test

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i

End Sub


Sub FillDir(startDir As String, dlist As Collection)

' build up a list of files, and then
' add add to this list, any additinal
' folders

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

' now build a list of additional folders
strTemp = Dir(startDir & "*.", vbDirectory)

Do While strTemp <> ""
If (strTemp <> ".") And (strTemp <> "..") Then
colFolders.Add strTemp
End If
strTemp = Dir
Loop

' now process each folder (recursion)
For Each vFolderName In colFolders
Call FillDir(startDir & vFolderName & "\", dlist)
Next vFolderName

End Sub

So, given that above "test" routine of:

' lets printout the stuff into debug window for a test

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i

You simly change the above code to write out the data to a table, you could
use:

dim rstDir as dao.RecordSet

set rstDir = currentdb.openRecordSet("tblDirData")
For i = 1 To dlist.Count
rstDir.AddNew
rstDir!DFileName = dlist(i)
rstDir.Update
next i

rstDir.Close
set rstDir = nothing

So, the above code would write the data to a table for you. So, from the
above should should be able to cobbile togther what you need.
 
Back
Top