Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    After Hans usccessfully helping me to move files, based on fields within a record, I have a further question.

    There are files relating to each record on the database I have that need to be moved, but their file path or name is not stored in the record. However the first so many characters of that file name could be known. Usually it is the first line of the property address.

    The files are stored in certain folders; F:\Word Pro, F:\Window Cards, F:\Photos

    Sometimes there are more than one file in the folder for each corresponding record based upon it's first line of address.

    Is it possible to generate vba to look at the first so many character of a file name, based upon the property number and streetname stored in the record to then move those files across to a pre-define common folder?
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't understand what exactly you want. Could you give a step-by-step description with specific information?

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay, sorry that I was not clear before.

    WE are in the process of re-organising the computer files for each property we have. Some of the file paths were stored in the database fields for each property record. This you helped on Friday with. So I can accomplish moving the files where the record knows where each file is.

    However we have other computer files (word documents, publisher files, image files) where there location / file path is not stored within the record of each property.

    The common begining characteristic of each file name for the above types of files will usually be the house number, followed by a space and then the streetname of the property. However there will usually be other text that follows this, such as '123 Street name - garden.jpg'

    We stored the files in the following folders;

    f:\Photos - There maybe more than one file here for a particular property (multiple internal photos of the house)
    f:\Window Card
    f:\Word Pro - There maybe more than one file here for a particular property (multiple documents for example, contracts, etc)

    I would like to be able to run vba that looks in the above folders for files that match the first part of a file name to equal the proprty number and streetname and then move these across to a different folder (as achieved on on Friday). The vba needs to handle dealing with multiple files in a particlar folder that match the criteria.

    I hope this helps?
    Best Regards,

    Luke

  4. #4
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay,

    I have the following code from the internet, which apparently should do what I want:

    Dim fs As FileSearch
    Set fs = Application.FileSearch
    fs.NewSearch
    With fs
    With .PropertyTests
    .Add _
    Name:="Text or Property", _
    Condition:=msoConditionIncludesPhrase, _
    Value:="horse"
    End With
    .LookIn = "E:\Data"
    .SearchSubFolders = False
    .Filename = "*"
    .MatchTextExactly = False
    .FileType = msoFileTypeAllFiles
    End With
    If fs.Execute() > 0 Then
    MsgBox "There were " & _
    fs.FoundFiles.Count & _
    " file(s) found."
    For i = 1 To fs.FoundFiles.Count
    msgbox fs.FoundFiles(i)
    Next i
    Else
    MsgBox "There were no files found."
    End If

    However when run, it states 'User define type, not defined'
    Best Regards,

    Luke

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Solved myself, add Office Object Library to references.
    Best Regards,

    Luke

  6. #6
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Access just locks up when I try this. hmmmm
    Best Regards,

    Luke

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That code could be useful, but keep in mind

    a. As is, it will look for text in a file's contents, not in the file's name.
    b. If you plan to upgrade to Office 2007 at some time, FileSearch isn't supported there anymore.

  8. #8
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay got it working with:

    Dim fs As FileSearch
    Set fs = Application.FileSearch
    fs.NewSearch
    With fs
    .LookIn = "f:\Photos"
    .SearchSubFolders = False
    .FileName = PROPNUMR & " " & STREETNAME & "*"
    .MatchTextExactly = False
    .FileType = msoFileTypeAllFiles
    End With
    If fs.Execute() > 0 Then
    msgbox "There were " & _
    fs.FoundFiles.Count & _
    " file(s) found."
    For i = 1 To fs.FoundFiles.Count
    msgbox fs.FoundFiles(i)
    Next i
    Else
    msgbox "There were no files found."
    End If

    I now need to work out how to store the file name returned in the code to then loop through to move the files across.
    Best Regards,

    Luke

  9. #9
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='784196' date='12-Jul-2009 13:14']That code could be useful, but keep in mind

    a. As is, it will look for text in a file's contents, not in the file's name.
    b. If you plan to upgrade to Office 2007 at some time, FileSearch isn't supported there anymore.[/quote]

    It is only a one off to move 300+ records.
    Best Regards,

    Luke

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In the previous topic, you included the AREA field in the folder name.
    Apparently, the file names you're working with in this topic don't include an area indication.

    a. How will you determine the target folder for the files if you don't know the area?
    b. What if there are two properties with the same PROPNR and STREETNAME in different areas?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •