Finding, Comparing, Opening a folder with Explorer

  • Thread starter Thread starter DawnTreader
  • Start date Start date
D

DawnTreader

Hello

i have need of a little help. i have looked at a few resources on the
internet regarding making lists of files and folders and either returning
them to a table or listbox, as well as doing other things with the returned
data. i even had a coworker jog my brain as to how i would open explorer to
where i want it. thats all great.

my problem is that i am unsure where to beging or what functions to use to
do what i would like to create.

here is the scenario. i have a table with products, each product has a
serial number which correspond to part of the name a folder on a drive where
all sorts of information is stored. i was using hyperlinks to make the
folders open on a click. the problem is that people are constantly changing
the name or placement of these folders. i know they shouldnt be, but i dont
care about that anymore. i know there is a way to do what i want. i just dont
know how.

i want to use the serial number to find the corresponding folder and then
open that folder with windows explorer.

here is what i know has to happen. the command button that i create has to
call a routine to take the field where the serial number is stored, it will
have to be trimmed to 5 character from the left, i know how to do that and
then compare that to a search through all the folders starting at the
specified folder (C:\WO\) and look at the first 5 characters of the folder
name. then it should store the folder name and use that as an argument to
open an explorer window.

what function would best serve this purpose? can anyone suggest some code to
get this started? has someone already done this before?
 
Hi Dawn

A bit longwinded but here is the code taht finds all the files on your c drive

Once you have info in tables you can find the first 5 char using left(str,5)
then lookup
file and folder name then using application.followhyperlink open the file.



1. First set up a table called "tbl_FilesInFolders"

Filelds
FileInFolderID = AutoNumber
FolderName = Text 255
FileName = Text 255

Copy and Run the following code

Option Compare Database
Option Explicit

Public Function fFileinFolder() As Long
On Error Resume Next

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intStart As Integer
Dim lngFiles As Long
Dim lngFilesCount As Long
Dim strFile As String
Dim strDir As String
Dim strDirFile As String
Dim strSQL As String
Dim fs As Object
Dim rstDetail As Object
Dim lngTotalErrors As Long
Dim strFolder As String
Dim strFoundInFolder As String

On Error GoTo FileinFolder_Error

lngTotalErrors = 0
strFolder = "C:\"

'-- Delete Contents of Existing Table
DoCmd.RunSQL ("DELETE * FROM tbl_FilesInFolders;")




'-- Trap directory not found
If Dir(strFolder, vbDirectory) = "" Then
MsgBox "Directory Not Found:" & vbCrLf & strFolder, vbCritical,
"File List"
GoTo FileinFolder_Exit
End If


'-- Set up files used
Set dbs = CurrentDb
Set fs = Application.FileSearch

Set rstDetail = dbs.OpenRecordset("tbl_FilesInFolders", dbOpenDynaset)


'-- Look into Folders

With fs
.lookin = strFolder
.SearchSubFolders = True
.FileName = "*.*" 'search for all file
'if just say excel then .FileName =
"*.XLS"

If .Execute() > 0 Then



DoCmd.Hourglass True

lngFilesCount = .foundfiles.Count

'-- Get Data in a loop
For lngFiles = 1 To lngFilesCount
On Error Resume Next

'-- Full path & file name
strDirFile = .foundfiles(lngFiles)

'-- Get file & dir names
intStart = LastInStr(strDirFile, "\")
strFoundInFolder = Left(strDirFile, intStart)

strFile = Mid$(strDirFile, intStart + 1)
'-- Write data to file
rstDetail.AddNew

rstDetail!FolderName = strFoundInFolder
rstDetail!FileName = strFile

rstDetail.Update



Next lngFiles
Else
MsgBox "No files were found in the directory.", vbInformation,
"File List"
End If
End With



FileinFolder_Exit:
On Error Resume Next
rst.Close
rstDetail.colse
Set dbs = Nothing
Exit Function

FileinFolder_Error:
MsgBox Err.Description, vbCritical, "File List Error"
Resume FileinFolder_Exit

End Function
Function LastInStr(strSearched As String, strSought As String) As Integer
Dim intCurrVal As Integer, intLastPosition As Integer

intCurrVal = InStr(strSearched, strSought)

Do Until intCurrVal = 0
intLastPosition = intCurrVal
intCurrVal = InStr(intLastPosition + 1, strSearched, strSought)
Loop

LastInStr = intLastPosition

End Function

Hope this helps you.
 
Hello Trevor

thanks for the post. i appreciate the help.

i had found this code even before i posted. i have been trying to figure out
what i need from it, but have been unable to determine what parts of it and
other code i have found that i will need to acomplish what i want.

i dont want to store paths in a table. my db is bloated enough as it is.

all i need is to get the first 5 characters of a folder name into a variable
and compare that with the first 5 characters of a field on my form. if the 2
match then i want to grab the whole folder name and cause it to open an
explorer window. putting all the paths in a table is unnecessary. can we not
just loop until the variable with the first 5 folder name characters match
the 5 field characters?

another thing i see in this code is that it is written to find files. i dont
want a file. i want folders. is there a function that will call the name of
just the folder and allow me to store it in a string variable so i can
compare it to a field?
 
dans l'article (e-mail address removed),
DawnTreader à (e-mail address removed) a écrit le 23/01/08
1:11 :
 
Back
Top