Search Folder for a file

[carl6885]

Hi

Little query for anyone that can help.

I have a table that has some details in. one field has an ID in it.

In one field I have to add a http link to a file that will be uploaded to a file server.

The http field will include: http://www.example.com/filename.pdf.

filename.pdf will consist of the ID and the place name so 101NewInn.pdf.

The file is located in a folder on a local drive at the minute and I would like a way, if possible to automate it as there is hundreds to do....

So something like.

Take ID from field.
Search the folder for a file that has the ID in the name.
If found append the filename including extension to http://........
save to the field

then move onto the next one until all done.

Any ideas????

Thanks

Carl

 

[joeKra]

Try this, make sure not to copy it to the same folder, it should not result in a endless loop,

Private Sub test2()
Dim strPath
Dim fso As FileSystemObject
Dim FLd
Dim FIL
Dim strOldName
Dim strNewName
Dim strFileParts
On Error Resume Next


 'Define the path to the file
    strPath = "C:\Users\Owner\Documents\Business Docs\"
    strNewPath = "C:\Users\Owner\Documents\Business Docs Renamed\"
    
    'Create the instance of the FSO
    Set fso = CreateObject("Scripting.FileSystemObject")
    'Set the folder you want to search. NOTE - some antivirus may not like this
    Set FLd = fso.GetFolder(strPath)
    
    'Loop through each file in the folder
    
    
    For Each FIL In FLd.Files
    If Dir(strPath & IDField & ".pdf") = True Then
        'Get complete file name with path
        strOldName = FIL.Path
        strFileParts = "http\\" & FIL.Name
        strNewName = strNewPath & strFileParts
            'Use the MoveFile method to rename the file
            fso.MoveFile strOldName, strNewName
            End If
         Next
         Else

   

    'Cleanup the objects
    Set FLd = Nothing
    Set fso = Nothing
    MsgBox "Done"
    
    
    
End Sub

 

[carl6885]

Hi

Thanks for this - I can see what it is doing in the code so it should work.

Only thing that concerns me is that I dont think it will find the files. The file im looking for would be ID(something else).pdf.

Is there a way to find it if there is extra text in filename?

Thanks

 

[carl6885]

Also getting error on:

Dim fso As FileSystemObject

User defined type not defined... this a referencing issue?

Thanks

 

[carl6885]

Sorted referencing issue but I can seem to get it to work. Getting the done message but nothing seems to be happening other than that

 

[carl6885]

I have stepped it through and it seems to iterate through every file in the folder no matter what is in the file....

 

[joeKra]

yes you have to reference first microsoft scripting Runtime

and here is an updates script for will find the file ONLY if it starts with the id

Private Sub test2()
Dim Rs As DAO.Recordset, IDField As String
Set Rs = CurrentDb.OpenRecordset("TableName")

On Error Resume Next

While Not Rs.EOF
IDField = Rs![ID]

Dim strPath
Dim fso As FileSystemObject
Dim FLd
Dim FIL
Dim strOldName, strNewPath
Dim strNewName
Dim strFileParts


 'Define the path to the file
    strPath = "C:\Users\Owner\Documents\Business Docs\& IDField &" * ""
    strNewPath = "C:\Users\Owner\Documents\Business Docs Renamed\"
    
    'Create the instance of the FSO
    Set fso = CreateObject("Scripting.FileSystemObject")
    'Set the folder you want to search. NOTE - some antivirus may not like this
    Set FLd = fso.GetFile(strPath)
    
    'Loop through each file in the folder
    
    fso.GetFile (strPath)
        'Get complete file name with path
        strOldName = strPath
        strFileParts = "http\\" & IDField & ".pdf"
        strNewName = strNewPath & strFileParts
            'Use the MoveFile method to rename the file
            fso.MoveFile strOldName, strNewName
            
            Wend
         
         
    'Cleanup the objects
    Set FLd = Nothing
    Set fso = Nothing
    MsgBox "Done"
    
    
    
End Sub

 

[carl6885]

thanks for this however it seems to just hang whenever run it forcing access to close.

 

[vbaInet]

The http field will include: http://www.example.com/filename.pdf.

If found append the filename including extension to http://........
save to the field

What is the point in saving this part to the field repeated times? Concatenate the http part to the ID field whenever you want to use it.

If you want an indicator that the file exists there's no point in this either because you cannot guarantee that two minutes after you set this indicator the file will still be there.

Here are some links that should give you some pointers with regards finding if a file exists in a folder:
http://allenbrowne.com/func-11.html
http://allenbrowne.com/ser-59.html

If you want to continue with your original idea, then have a look here:
http://allenbrowne.com/ser-59alt.html