Import file names to a table

[Secret Squirrel]

I have a table where I want to store the file names of all the files in a
specific folder on my server. How can I create a function to create records
with the file names from this folder? There are both excel and word documents
in this folder and I want to be able to select one from a form and have it
open the file in either excel or word. But before I can do that I need to be
able to create the table automatically to store the path and the file names.
And also have this table updated every time I open the database in case a
file was deleted or if one was added.

 

[magmike]

I have a table where I want to store the file names of all the files in a
specific folder on my server. How can I create a function to create records
with the file names from this folder? There are both excel and word documents
in this folder and I want to be able to select one from a form and have it
open the file in either excel or word. But before I can do that I need tobe
able to create the table automatically to store the path and the file names.
And also have this table updated every time I open the database in case a
file was deleted or if one was added.

I can't help you with the automated part, but I have created my own
little document server that helps with most of your plan. If you
create a field in your table, lets say, called DocLink and for the
data type, select OLE Object. I use a form to add files, and using a
button on the form with the following code, I can easily enter the
path to the file by browsing to it. You can choose to either insert
the file into the table (not recommended) or just a link to the file
(this will keep your database from getting too big too quick). If you
use an OLE Object field on your form, you can actually see a snapshot
(unselect "Show Icon" if you want to see the snapshot) of the file and
double click to open it. It will open it in the correct program. My
add file button's code:

Private Sub InsertObject_Click()
On Error GoTo Err_InsertObject_Click

Dim stDocName As String

stDocName = "InsertObject"
DoCmd.RunMacro stDocName

Exit_InsertObject_Click:
Exit Sub

Err_InsertObject_Click:
MsgBox Err.Description
Resume Exit_InsertObject_Click

End Sub

As far as automating the adding of a new record when new records are
present - we'll both need help with that one, and I'd like to see the
answer to it. I hope this get's you a little closer to your goal.

magmike

 

[John W. Vinson]

I have a table where I want to store the file names of all the files in a
specific folder on my server. How can I create a function to create records
with the file names from this folder? There are both excel and word documents
in this folder and I want to be able to select one from a form and have it
open the file in either excel or word. But before I can do that I need to be
able to create the table automatically to store the path and the file names.
And also have this table updated every time I open the database in case a
file was deleted or if one was added.

See the VBA help for the Dir function. Calling it in a loop and putting its
result into a recordset would do what you ask. Untested air code, accepting a
pathname as an argument:

Public Sub GetFileNames(strPath As String)
Dim strName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset)
' find the first path
strName = Dir(strPath & "\*.xls") ' to get all files with .xls extension
Do Until strName = "" ' loop until no more files
rs.AddNew ' add a new record
rs!filename = strName
rs.Update
strName = Dir ' find the next file
Loop
rs.Close
Set rs = Nothing
End Sub

 

[magmike]

See the VBA help for the Dir function. Calling it in a loop and putting its
result into a recordset would do what you ask. Untested air code, accepting a
pathname as an argument:

Public Sub GetFileNames(strPath As String)
Dim strName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset)
' find the first path
strName = Dir(strPath & "\*.xls") ' to get all files with .xls extension
Do Until strName = "" ' loop until no more files
rs.AddNew ' add a new record
rs!filename = strName
rs.Update
strName = Dir  ' find the next file
Loop
rs.Close
Set rs = Nothing
End Sub

John: Could I use strPath & "\*.*" to add all file types? Also, would
this function also delete records when the file disappears?

Mr. Squirrel: How are you using this feature?

"What is a squirrel anyhow? Nothing but a rat with better PR." ;-)

 

[John W. Vinson]

John: Could I use strPath & "\*.*" to add all file types?
Sure.

Also, would
this function also delete records when the file disappears?

No; you'ld probably want to simply run a Delete query without criteria to
delete all records in the table prior to calling the subroutine, or you could
run such a query at the start of the routine.

 

[Secret Squirrel]

Thanks for your help John. One more question...

Say I want to just store the filepath and not the name of the file itself.
How would I change the code to open the browse window and allow me to select
a folder and then have it store that path in my table?

 

[Secret Squirrel]

I was able to create a function using VBA help. The only problem I have now
is since I've added a "DLookup" function to my string it only looks at the
first record in my dlookup with the specified criteria instead of looping
through all the records that meet that criteria. Do I need to put a loop on
the "with fs" statement?

 

[John W. Vinson]

I was able to create a function using VBA help. The only problem I have now
is since I've added a "DLookup" function to my string it only looks at the
first record in my dlookup with the specified criteria instead of looping
through all the records that meet that criteria. Do I need to put a loop on
the "with fs" statement?

Not sure I understand. Yes, DLookUp just retrieves one (arbitrary, basically)
record. You'ld certainly need a loop through the recordset using the MoveNext
method to step through the records. Perhaps you could post your code and
indicate just what you want to do with it.

 

[Secret Squirrel]

Sorry about that. I thought I copied the code onto here. I want it to loop
through all my records in the DLookup and create records for each file it
finds. The reason I wanted to use the DLookup was so that it will look in
different folders based on what's in my tblFolders and add the file names for
each folder it looks at.

Function AppendFileName()
Dim fs As Object, FileName As String, Path As String, MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Dim myLookIn As String
myLookIn = DLookup("[FolderName]", "tblFolders", "[FolderCheck] = True")

Set MyDB = CurrentDb()
Set MyRec = MyDB.OpenRecordset("Select * From tblFileNames")
Set fs = Application.FileSearch
With fs
.LookIn = myLookIn
.FileName = "*.*"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Path = .FoundFiles(i)
FileName = Mid(.FoundFiles(i), Len(myLookIn) + 1, 250)
MyRec.AddNew
MyRec!FileName = FileName
MyRec!LookIn = myLookIn
MyRec!Path = myLookIn & FileName
MyRec.Update
Next i
End If

End With

End Function

 

[John W. Vinson]

Sorry about that. I thought I copied the code onto here. I want it to loop
through all my records in the DLookup and create records for each file it
finds. The reason I wanted to use the DLookup was so that it will look in
different folders based on what's in my tblFolders and add the file names for
each folder it looks at.

But... but... DLookUp does not return records or a recordset or files. It
looks up *one* value from one field of one table or query. Do you mean the
FileSearch method instead???

Function AppendFileName()
Dim fs As Object, FileName As String, Path As String, MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Dim myLookIn As String
myLookIn = DLookup("[FolderName]", "tblFolders", "[FolderCheck] = True")

What is in tblFolders at this point? What's the structure of the table?

Try this instead... I'm totally guessing at the actual structure here but if
my guess is close you should be able to adapt:

Function AppendFileName()
Dim fs As Object, FileName As String, Path As String, MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Dim MyFolders As DAO.Recordset

Set MyDB = CurrentDb()
Set MyFolders = MyDB.OpenRecordset("SELECT FolderName FROM tblFolders" _
& " WHERE [FolderCheck]", dbOpenDynaset)
Set MyRec = MyDB.OpenRecordset("Select * From tblFileNames")
Do Until MyFolders.EOF
Set fs = Application.FileSearch
With fs
.LookIn = MyFolders!FolderName
.FileName = "*.*"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Path = .FoundFiles(i)
' Leave off the third argument to Mid to return the rest of the string
FileName = Mid(.FoundFiles(i), Len(MyFolders!FolderName) + 1)
MyRec.AddNew
MyRec!FileName = FileName
MyRec!LookIn = myLookIn
MyRec!Path = myLookIn & FileName
MyRec.Update
Next i
End If
End With
MyFolders.MoveNext
Loop

End Function

 

[Secret Squirrel]

The table structure of tblFolders is:

FolderID - pk
FolderName - this is the folder path
FolderCheck - this is used to tell the code to import the file names or not.
If this is set to true then it should import the file names for that
directory. Otherwise just skip that record

Sorry about that. I thought I copied the code onto here. I want it to loop
through all my records in the DLookup and create records for each file it
finds. The reason I wanted to use the DLookup was so that it will look in
different folders based on what's in my tblFolders and add the file names for
each folder it looks at.

But... but... DLookUp does not return records or a recordset or files. It
looks up *one* value from one field of one table or query. Do you mean the
FileSearch method instead???

Function AppendFileName()
Dim fs As Object, FileName As String, Path As String, MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Dim myLookIn As String
myLookIn = DLookup("[FolderName]", "tblFolders", "[FolderCheck] = True")

What is in tblFolders at this point? What's the structure of the table?

Try this instead... I'm totally guessing at the actual structure here but if
my guess is close you should be able to adapt:

Function AppendFileName()
Dim fs As Object, FileName As String, Path As String, MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Dim MyFolders As DAO.Recordset

Set MyDB = CurrentDb()
Set MyFolders = MyDB.OpenRecordset("SELECT FolderName FROM tblFolders" _
& " WHERE [FolderCheck]", dbOpenDynaset)
Set MyRec = MyDB.OpenRecordset("Select * From tblFileNames")
Do Until MyFolders.EOF
Set fs = Application.FileSearch
With fs
.LookIn = MyFolders!FolderName
.FileName = "*.*"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Path = .FoundFiles(i)
' Leave off the third argument to Mid to return the rest of the string
FileName = Mid(.FoundFiles(i), Len(MyFolders!FolderName) + 1)
MyRec.AddNew
MyRec!FileName = FileName
MyRec!LookIn = myLookIn
MyRec!Path = myLookIn & FileName
MyRec.Update
Next i
End If
End With
MyFolders.MoveNext
Loop

End Function

 

[Secret Squirrel]

The tblFolders is where I want to store all my folder paths and then give the
users the ability to to import the file names based on whether the
FolderCheck is true. What I was hoping to also do was import all the paths
for all the folders within a certain directory. For example if I had 3
folders and 4 subfolders all under a folder called "HR" then I would want to
import all the paths for those folders into tblFolders and then check off
which ones I wanted to import the file names for. Is there a simple way to
write some code to do this?

Sorry about that. I thought I copied the code onto here. I want it to loop
through all my records in the DLookup and create records for each file it
finds. The reason I wanted to use the DLookup was so that it will look in
different folders based on what's in my tblFolders and add the file names for
each folder it looks at.

But... but... DLookUp does not return records or a recordset or files. It
looks up *one* value from one field of one table or query. Do you mean the
FileSearch method instead???

Function AppendFileName()
Dim fs As Object, FileName As String, Path As String, MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Dim myLookIn As String
myLookIn = DLookup("[FolderName]", "tblFolders", "[FolderCheck] = True")

What is in tblFolders at this point? What's the structure of the table?

Try this instead... I'm totally guessing at the actual structure here but if
my guess is close you should be able to adapt:

Function AppendFileName()
Dim fs As Object, FileName As String, Path As String, MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Dim MyFolders As DAO.Recordset

Set MyDB = CurrentDb()
Set MyFolders = MyDB.OpenRecordset("SELECT FolderName FROM tblFolders" _
& " WHERE [FolderCheck]", dbOpenDynaset)
Set MyRec = MyDB.OpenRecordset("Select * From tblFileNames")
Do Until MyFolders.EOF
Set fs = Application.FileSearch
With fs
.LookIn = MyFolders!FolderName
.FileName = "*.*"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Path = .FoundFiles(i)
' Leave off the third argument to Mid to return the rest of the string
FileName = Mid(.FoundFiles(i), Len(MyFolders!FolderName) + 1)
MyRec.AddNew
MyRec!FileName = FileName
MyRec!LookIn = myLookIn
MyRec!Path = myLookIn & FileName
MyRec.Update
Next i
End If
End With
MyFolders.MoveNext
Loop

End Function

 

[John W. Vinson]

The tblFolders is where I want to store all my folder paths and then give the
users the ability to to import the file names based on whether the
FolderCheck is true. What I was hoping to also do was import all the paths
for all the folders within a certain directory. For example if I had 3
folders and 4 subfolders all under a folder called "HR" then I would want to
import all the paths for those folders into tblFolders and then check off
which ones I wanted to import the file names for. Is there a simple way to
write some code to do this?

Did you try the code I posted?

 

[Secret Squirrel]

Yes it's exactly what I needed. It goes through all the records in my
tblFolders and creates new records for all the files within those folders. I
didn't realize the DLookup only selects one record. Now I know!

Is there an easy way to modify that code to just select the path chosen
through a browse window and have that path written to a record also? I would
assume it would be similar to what you gave me. Correct?

 

[John W. Vinson]

Is there an easy way to modify that code to just select the path chosen
through a browse window and have that path written to a record also? I would
assume it would be similar to what you gave me. Correct?

Well, as written, the code assumes that you're passing the path as an
argument. You could copy and paste the path from an Explorer window, or use
the FileSearch program to search for folders - I'm not sure just what kind of
interaction you want!

 

[Secret Squirrel]

I was looking to create a similar code to the code in this thread but have it
store the folder paths to all folders within a specified directory. I want to
load my tblFolders with all the folders and subfolders within a specific
directory and then I can toggle these on or off to load the file names using
the code we've been working on.

 

[John W. Vinson]

I was looking to create a similar code to the code in this thread but have it
store the folder paths to all folders within a specified directory. I want to
load my tblFolders with all the folders and subfolders within a specific
directory and then I can toggle these on or off to load the file names using
the code we've been working on.

Well... a folder is just a special kind of file. See the VBA help for Dir or
the help for FileSearch. You can use the same logic. I think there's enough
between my example and the help files to get you going; if you'ld like to hire
someone to write the code for you feel free.