UtterAccess Discussion Forums > Copying Selected Files In Folder To New Folder

Full Version: Copying Selected Files In Folder To New Folder

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

Midnight

Apr 21 2012, 01:53 AM

Hi
I am wanting to be able to execute some code (using an mdb) that looks at a table - holding the id numbers of selected students - then goes to the folder holding Photos of ALL students, selects the photos which match ( as the photos are named by the students unique 10 letter code), and transfers them to another folder.
My vb is not as good as i'd like - especially when dealing with file manipulation.
any help would be much appreciated
cheers
Paul

dipetete

Apr 21 2012, 06:52 AM

Wrote on the fly, so you have to try

CODE

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fso As Object
Dim strPath As String
Dim strFile As String
Dim strPF As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset ("YourTable", dbOpenTable)
Set fso = CreateObject("Scripting.FileSystemObject")
strPath = "C:\YourPath" & "\"

If (Not rst.EOF) And (Not rst.BOF) Then
    Do While Not rst.EOF
        strFile = rst!StudentID & ".extension"
        strPF = strPath & strFile
        If fso.FileExists(strPF) Then
           fso.CopyFile strPF, "C:\destination_folder\"
        End If
    rst.MoveNext
    Loop
End If

rst.Close

strFile = ""
strPath = ""
strPF = ""
Set fso = Nothing
Set rst = Nothing
Set dbs = Nothing

PS: I forgot to mention. You have to add the Microsoft Scripting Runtime to your references...

Midnight

Apr 21 2012, 08:02 PM

dipetete
Ta worked a treat - once I had managed to get the path name correct.
HOWEVER, it just occurred to me that I would also need to CREATE the destination folder - and would want to use the classgroup to which the selected files belong. E.g. "MAA121B" (this is stored in the temp file i created and it also shows in the CBO box which the users use to select their group)

What would i need to add to get this to happen?

many thanks
Paul

arnelgp

Apr 22 2012, 02:40 AM

for your modification:

CODE

Sub sTransferPics(ByVal varID As Variant)
    ' varID is the ID of your student
    Dim strSourcePath As String
    Dim strTargetPath As String
    Dim bolOKToCopy As Boolean
    Dim strPictExtension As String * 4


    varID = Left(varID, 10)

    ' TODO: put your source picture folder here
    strSourcePath = "C:\TEMP\"
    'remove extra "\" if already supplied
    strSourcePath = Replace(strSourcePath, "\\", "\")

    ' TODO: replaced with correct extension
    strPictExtension = ".jpg"

    ' TODO: replace yourComboBox with your combobox name
    strTargetPath = "C:\" & yourComboBox.Value & "\"
    'remove extra "\" if already supplied
    strTargetPath = Replace(strTargetPath, "\\", "\")

    ' check if the target folder exists
    If Dir(strTargetPath, vbDirectory) = "" Then
        ' does not exists, then create it
        MkDir strTargetPath
    End If

    ' will continue copying picture
    bolOKToCopy = True

    ' check if the picture already exists in target folder
    If Dir(strTargetPath & varID & strPictExtension) <> "" Then
        ' already exists, what shall we do
        If MsgBox("Picture " & varID & strPictExtension & _
            " already exists in " & strTargetPath & "." & vbCrLf & vbCrLf & _
            "Do you want to overwrite it?", vbQuestion + vbYesNo) = vbNo Then

            ' doesn't want to overwrite, so we will not continue
            bolOKToCopy = False
        Else
            ' ok to overwrite, well delete it first
            Kill strTargetPath & varID & strPictExtension
        End If
    End If

    If bolOKToCopy Then
        FileCopy strSourcePath & varID & strPictExtension, strtargetpth & varID & strPictExtension
    End If

End Sub

Midnight

Apr 22 2012, 03:21 AM

Arne
ta, i'll give it a whirl
many thanks
Paul

dipetete

Apr 22 2012, 04:25 AM

I would use another recordset first, using a SELECT DISTINCT query both to create the folder and to sort the students.
Alt+F11, Alt+F1 write recordset. Find recordset.object and look at the examples for creating a recordset based on a query
Good luck

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.