Importing DOS directory data into MSA table

[roger]

I have an MDB that handles data about certain types of files and folders.

But my current method of getting file directories into MSA is cumbersome and
buggy.

I have a query that spits out a 3 line batch file.
Then I run TransferText to output it to a temporary txt file (named
TempGetDir.Bat)
Then I use a Shell command to run the .bat file
It goes:

c:\
cd\My Folder
dir > c:\TempDir.Txt

Then I use another TransferText command to import TempDir.Txt to a temporary
table, from there I run a couple of queries that extract the file and folder
data I want.

(I have to use the batch file and ChangeDir command because more often than
not the folder name involves a space, and the DOS command:

dir c:\My Folder\*.* > c:\TempDir.Txt

fails because of the space in the folder name)

So my Sub *works* but its cumbersome and buggy:

1: (cumbersome ) The time it takes the harddrive to write the TempDir.Txt
file varies. Sometimes it wasn’t finished writing the file and I’m already
trying to import it. I’ve had to insert an apiSleep command to pause the Sub
for 1 second in order to avoid this problem. But that makes the whole thing
slow to the point of being almost unusable.

2. (buggy) While the ChangeDir in the batch file solved the issue of spaces
in folder names, some folders have an ampersand (&) in their name, and the
ChangeDir command fails then. Which causes the dir command to return the
directory of the c:\ root and that screws EVERYTHING up.

I realize I could navigate the local HD using 8.3 filenames:
(as in c:\MyFold~1\TempDi~1.txt)
But the data I’m looking to import is IN the Long File Names. So if there is
a way to navigate on DOS using 8.3 but still get a directory in LFN, I’d love
to hear about it.

I also know that VB has a Dir function but it seems to return a single
filename, and not a directory at all. (with date size, subfolders, etc.) Is
there any VB command that does a real DOS dir?

I’d love to avoid the whole shelling out to DOS completely. Is there any way
to this just in VB?

 

[Albert D. Kallal]

I use:

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

The above is a recursive routine, so, it will it will walk the directory
structure....

To add to a table, you get:

Set rst = CurrentDb.OpenRecordset("tblFiles")

For i = 1 To cFiles.Count
rst.AddNew
rst!DiskName = Me.txtdisk
rst!FileName = cFiles(i)
rst.Update
Next i

rst.Close
Set rst = Nothing

 

[roger]

Thanks.

It will take me a day or two to digest that, but it looks like exactly what
I'm looking for.

I may still have a question or two

thanks again
roger