Rename and Copying folders with code 2

[gbscobel]

I have an application in Access 2003 that a client is now requesting do something I've never attempted before. From the application, they want to be able to navigate to a specific folder on their local drive, select it, copy it to a location on their network drive while potentially checking and/or creating a new folder based on record information and renaming the folder to correspond with a date field on a record. Here is an example:

The database has records keyed on a Job# field, for example, 1000 and each record has a unique entry date. Each user will have a folder in their local drive with image files in it. I'll call it c:\windows\images. On the network is a folder f:\job tracking\images. They press a button and they want the following to occur:

1. Allow the user to navigate to the c:\windows\images folder or whatever folder holds their image files and select it.
2. Check the f:\job tracking\images folder for a subfolder called 1000 (the job number).
3. If the folder does not exist, create it.
4. If it already exists or after the folder is created, move the entire c:\windows\images folder under the f:\job tracking\images\1000 folder and rename it to the entry date field stored in the table in the format 2007-02-26
5. Finally, they want that path stored in a hypertext field on the record for future access.

I know this is pretty complex but it's mostly moving and renaming folders. I assume there is code out there to do this but I'm having a really hard time finding it and would appreciate any help anybody could lend. Let me know if there's any more information I can provide to make this easier.

 

[Remou]

Have you considered the FileSystemObject, FileSearch, Browse for Folder, Dir, Name As and FollowHyperlink? What you use will also depend on your version of Access.

 

[gbscobel]

Thanks for the response. I am using Access 2003. Unfortunately, I have never done any programming using those commands, what I was hoping for was some code examples I could play with to figure out how to accomplish my task.

 

[MikeC14081972]

Use the code below to find the full path of the required file then the second code to copy it to the required destination.

Function FindFile(ByVal strFileName As String, _
            ByVal strSearchPath As String) As String
'Returns the full path for the first match found given the filename and drive
    Dim lpBuffer As String
    Dim lngResult As Long
    
    FindFile = ""
    lpBuffer = String$(1024, 0)
    lngResult = apiSearchTreeForFile(strSearchPath, strFileName, lpBuffer)
    If lngResult <> 0 Then
        If InStr(lpBuffer, vbNullChar) > 0 Then
            FindFile = Left$(lpBuffer, InStr(lpBuffer, vbNullChar) - 1)
            MsgBox ("" & FindFile & "")
        End If
    End If
End Function

Public Function CopyFile(strSource As String, strDest As String)
' Copies a given file to a given location
Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")

fso.CopyFile strSource, strDest

Set fso = Nothing

End Function

If you make something idiot proof - They'll Only make a better idiot!!!

 

[gbscobel]

Thanks very much, I will work with that and see where I get. It's very helpful to have an example to work off of.

 

[gbscobel]

Mike,

I had a question on this code, is this strictly for copying and moving files or will it work for entire directories/folders? The application needs to confirm the existence of a particalar folder name, create it if it doesn't exist, move a folder to that location and rename the folder once it's moved. Maybe I'm missing something, but I'm not understanding how your code gets me there unless the same syntax will work for an entire directory as well as a file.

 

[jadams0173]

The file system object also will copy folders, create folders and more....

I tried to have patience but it took to long! -DW

 

[MikeC14081972]

Another useful one for you.

Have a play with the 3 examples and you'll get an idea of what can be done.

Checking if file exists

Public Function FileExists(FilePath As String) As Boolean
' Checks to see if a given file exists
Dim fData As Object
Dim fso As Scripting.FileSystemObject

On Error GoTo ErrHandler

Set fso = New Scripting.FileSystemObject

    fso.GetFile (FilePath)
    
Set fso = Nothing

'Return Success
FileExists = True

Exit Function

ErrHandler:

'Returns Failure
FileExists = False

Exit Function

End Function

If you make something idiot proof - They'll Only make a better idiot!!!

 

[MikeC14081972]

By passing the full filepath required into this function Access will build the path for you including subfolders.

Public Function CreatePath(TargetPath As String) As Integer
' Creates a given file path
Dim FullPath As String
Dim SubPath As String
Dim Pos As Integer

On Error GoTo CreatePathErr

'Ensure the path has a trailing delimited
If Right(TargetPath, 1) = "\" Then
    FullPath = TargetPath
Else
    FullPath = TargetPath & "\"
End If

' Find the first path delimter
Pos = InStr(1, FullPath, "\")

'Process the whole target path
Do While (Pos > 0)
    
    ' Compose the sub path to be created
    SubPath = Left(FullPath, Pos)
    
    ' Check subpath exists and create if necessary
    If (Not FileExists(SubPath)) Then
        MkDir SubPath
    End If
    
    ' Find the next delimter
    Pos = InStr(Pos + 1, FullPath, "\")
    
Loop

'Return Success
CreatePath = True

Exit Function

CreatePathErr:
    
    ' Report Error
    Call ErrMsg("CreatePath", smodule, Error, Err)
    
    'Return Failure
    CreatePath = False
    
Exit Function

End Function

If you make something idiot proof - They'll Only make a better idiot!!!

 

[PHV]

I've already posted a simpler "CreatePath" routine:

Sub myMkDir(strFolderName As String)
On Error Resume Next
Dim a, t As String, i As Integer
a = Split(strFolderName, "\")
t = a(0)
For i = 1 To UBound(a)
  t = t & "\" & a(i)
  MkDir t
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886