Getting a list of filenames and path from a Directory with subdirs

[Taha60]

Hello....

Can anyone tell me how I can get a list of file names path and date in a directory with subdirectories...

I would like to store this info into a matrix that is nxn where n stands for any number then dump them into a table using sql insert statements... I am using access 97 and 2k

Thanks Taha
thamiral@uwo.ca

 

[nabiS2K]

Look up the Dir function in Access 97 help (2000 seems to have a joke of a help file). The Dir function is the tool you need.

Let me know if you still need help.
God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[Taha60]

Hello...

Thanks for the help... I would really appreciate a sample that would ...lets say .. enumerate all the files in c:\My Documents ... Additionally ... I need to make hyperlinks out of these file paths... therefore I will need the File name and then I can use the FILENAME#FILEPATH to make a good looking hyperlink

Thanks
Taha
thamiral@uwo.ca

 

[oharab]

Ok Taha!

This will only work if you have scripting installed.(most people will, unless it's been disabled by an administrator)

set a reference to Microsoft Scripting Runtime (in tool->References) and make sure you have a reference to DAO3.6 if you are using A2k or above.

Call it like: ImportFileNames("C:\Mydocuments&quot and it will go thru your folder & subfolders adding to a table called "tblFiles" with the fields "Pathtofile" and "FileName" in it.

Good luck.

B

Sub ImportFileNames(sDirectory As String)
Dim FSO As Scripting.FileSystemObject, fsoFolder As Scripting.Folder, fsoFile As Scripting.File
Dim fsoSubFolder As Scripting.Folder
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblFiles&quot

Set FSO = CreateObject("Scripting.FileSystemObject&quot

Set fsoFolder = FSO.GetFolder(sDirectory)
For Each fsoFile In fsoFolder.Files
rst.AddNew
rst!Pathtofile = fsoFile.ParentFolder
rst!FileName = fsoFile.name
rst.Update
Next fsoFile

For Each fsoSubFolder In fsoFolder.SubFolders
ImportFileNames (fsoSubFolder.Path)
Next fsoSubFolder
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub

----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[Taha60]

Thank BEN .. I already Emailed you what I did but for completeness I will post it here ... This code requires no scripting .. Tis Basically Ben's code but with SQL inserts instead of DAO ... (something I dont want to understand )

"WE ARE NOW FREE FROM THE "ROOT" "

here goes ...

'This is Ben's code with a few megre modifications

Option Compare Database
Option Explicit

Private Const BIF_RETURNONLYFSDIRS = 1
Private Const BIF_DONTGOBELOWDOMAIN = 2
Private Const MAX_PATH = 260

Private Declare Function SHBrowseForFolder Lib _
"shell32" (lpbi As BrowseInfo) As Long

Private Declare Function SHGetPathFromIDList Lib _
"shell32" (ByVal pidList As Long, ByVal lpBuffer _
As String) As Long

Private Declare Function lstrcat Lib "kernel32" _
Alias "lstrcatA" (ByVal lpString1 As String, ByVal _
lpString2 As String) As Long

Private Type BrowseInfo
hWndOwner As Long
pIDLRoot As Long
pszDisplayName As Long
lpszTitle As Long
ulFlags As Long
lpfnCallback As Long
lParam As Long
iImage As Long
End Type


Private Sub Command1_Click()
'Opens a Browse Folders Dialog Box that displays the
'directories in your computer
Dim lpIDList As Long 'Declare Varibles
Dim sBuffer As String
Dim szTitle As String
Dim tBrowseInfo As BrowseInfo
Dim sFolderPath As String

szTitle = "Pick A FOLDER"
'Text to appear in the the gray area under the title bar
'telling you what to do

With tBrowseInfo
.hWndOwner = Me.Hwnd 'Owner Form
.lpszTitle = lstrcat(szTitle, "&quot
.ulFlags = BIF_RETURNONLYFSDIRS + BIF_DONTGOBELOWDOMAIN
End With

lpIDList = SHBrowseForFolder(tBrowseInfo)

If (lpIDList) Then
sBuffer = Space(MAX_PATH)
SHGetPathFromIDList lpIDList, sBuffer
sBuffer = Left(sBuffer, InStr(sBuffer, vbNullChar) - 1)
sFolderPath = sBuffer
End If

ImportFileNames (sFolderPath)

End Sub

Sub ImportFileNames(sDirectory As String)
Dim fn As String
Dim dbs As DAO.Database

Set dbs = CurrentDb

fn = Dir(sDirectory & "\*.*&quot
Do Until fn = ""
If fn <> &quot;.&quot; And fn <> &quot;..&quot; Then

dbs.Execute (&quot;INSERT INTO tblFiles VALUES ('&quot; + fn + &quot;#&quot; + sDirectory + &quot;\&quot; + fn + &quot;')&quot, dbFailOnError

End If
fn = Dir
Loop

End Sub



Taha
thamiral@uwo.ca

 

[oharab]

Aw shucks! you're making me blush!


b ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[Taha60]

Hello again everyone

I was wondering if someone can tell me how I shall be able to monitor the progress of the above code that Ben put up...I would like to put some animation on the screen as it the function does its work...

Additionally I have about 10,000 Hyperlinks in an access table in the form of FILENAME#FILEPATH ...these are in a field of type hyperlink... I would like to get out the &quot;filename&quot; path and copy it to a new field for each record....

Any help would greatly be appreciated...

Thanks once again .... Taha
thamiral@uwo.ca

 

[nabiS2K]

You can have access place status or a meter in the status bar with the Syscmd.

Example:

Dim Mtr As Integer, MtrIntvl As Long, MtrLength As Long

MtrLength = 1000000
Mtr = SysCmd(acSysCmdInitMeter, "Updating", MtrLength)


For MtrIntvl = 1 to MtrLength
     Mtr = SysCmd(acSysCmdUpdateMeter, MtrIntvl)
Next MtrIntvl

Mtr = SysCmd(acSysCmdRemoveMeter)

God Bless,
Mike

Yes, I believe in Jesus. ;-)
&quot;Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved.&quot; (Acts 4:12, KJV)

 

[Taha60]

Thanks for the tip ... However I am curious can someone explain this ... It seems to take the same amount of time even if its doing a MUCH larger folder ...

Thanks ... How about the Hyperlink prob. guys...

Taha
thamiral@uwo.ca

 

[Taha60]

Me thinks me should rephrase the above msg...

I know that it is set to go only for a certain amount of time ... but I wanted something that is dependant on the number of hyperlinks that I wish to add... for instance if i need to add 1e100 hyperlinks the progress bar would take more time time to travel accross the screen than when I process 1e10 links...

However I really appreciate the status bar thingum ... It looks SUPER! and If I dont find a solution to the prob. above I will use it for sure ...

Thanks .... Taha
thamiral@uwo.ca

 

[nabiS2K]

Whoops... It seems I should have been a bit more clear.

My above code is just a example of how the SysCmd option works. I was just trying to demonstrate how to use the meter to show the progress.

If I were you, I would initialize the meter (Mtr = SysCmd(acSysCmdInitMeter, &quot;Updating&quot;, MtrLength)) with MtrLength being the number of hyperlinks you want written. After each hyperlink is added (it would have to be one at a time), you would use MtrIntvl = MtrIntvl + 1 followed by Mtr = SysCmd(acSysCmdUpdateMeter, MtrIntvl) to show the progress of each individual hyperlink.

Does this make more sense? God Bless,
Mike

Yes, I believe in Jesus. ;-)
&quot;Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved.&quot; (Acts 4:12, KJV)

 

[Taha60]

Hello...

With respect to the last message ... Does that mean I have to count the Hyperlinks before hand???!!!... As you can see in my code ... I am adding as I come across a hyperlink...

That is why i wanted to have a small window pop up to play animation while the func. does its work ...

This brings up another question ... How does access VBA work with drawing ... i.e Can I draw something in code while another process is working ??? In that case I can make the function return a certain value when its done while the animation function keeps checking a global variable for that value...

<<An easier way is just to make the animation spawn and continue in the Add hyperlink function...>> but it seems not to continue the animation while its processing hyperlinks...

Thanks .... Taha
thamiral@uwo.ca

 

[Taha60]

Hello...

With respect to the last message ... Does that mean I have to count the Hyperlinks before hand???!!!... As you can see in my code ... I am adding as I come across a hyperlink...

That is why i wanted to have a small window pop up to play animation while the func. does its work ...

This brings up another question ... How does access VBA work with drawing ... i.e Can I draw something in code while another process is working ??? In that case I can make the function return a certain value when its done while the animation function keeps checking a global variable for that value...

<<An easier way is just to make the animation spawn and continue in the Add hyperlink function...>> but it seems not to continue the animation while its processing hyperlinks...

Thanks .... Taha
thamiral@uwo.ca

 

[Taha60]

WHOOOPS >>>!!!! SORRY Taha
thamiral@uwo.ca