+ Reply to Thread
Results 1 to 33 of 33

Search for pdf files by date or title

  1. #1
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Search for pdf files by date or title

    Hi,

    I use a folder called Account scans to hold all my scanned accounts and Invoices.
    I save each in the folder under the following criteria - name - inv No. - date

    Now what I would like to be able to do is to have a spread sheet with search as per the attached example which would search the Account scans folder for any PDF which matched the search criteria.

    Now - this additional thing might no be possible but having found the searched file is possible to apply an auto hyperlink to the file in the folder - or is that not possible

    Assistance appreciated
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    i think your best off tackling in 3 steps

    list everything in folder which is pdf
    from list find stuff and delete ones that dont match criteria

    hyperlink part is actually probably the easiest bit

    Please Login or Register  to view this content.
    this is the code for listing all pdf in folder
    note
    if for some reason pdf doesnt have "-" in it...it wont list
    if it has too many "-" in there it wont work
    Last edited by humdingaling; 05-20-2015 at 02:08 AM. Reason: slight syntax error fixed
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    I understand but I was thinking of a search situation which would enable me to say search all PDF files by date or by Invoice Number or name

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    hmm i may have over thought...though i am unsure of duplication would be an issue but
    the search string can override the need to write in files

    Please Login or Register  to view this content.
    instr is case sensitive if that is an issue need to UPPER or LOWER everything first
    Last edited by humdingaling; 05-20-2015 at 02:08 AM.

  5. #5
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    Could I ask your assistance to apply the code to the sample please
    I not sure what to do

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    See attached spreadsheet
    the only thing you need to update is

    Please Login or Register  to view this content.
    change this to your directory

    press the grey button and it should work

    i added the hyperlink feature into this one as well
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    an alternative if you having with re-writing code
    i can re-write it for you that it searches the folder in which the file is placed

    would that be better?

  8. #8
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    Yes thanks I will work out the path 10 sec pls

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    Here you go
    i replaced
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    now all you have to do is place this file in the folder where all the pdf are stored
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    That's great but I am getting an error message - this row is highlighted
    HTML Code: 
    and the sort is showing up complete in col e
    I not sure how to send a screen print which might assist

  11. #11
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    Thanks very much what I am getting in Col e is the Hyperlink
    This is fine

    Thanks much appreciated

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    confirm your PDF file structure
    is it NAME - INV - Date

    also Does your date have "-" as seperator? or is it something like 200515?
    if it is like 20-05-2015 then i will need to re-write code

  13. #13
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    Name - Invoice number - date [12-jul-15]
    That is the general format
    Sometimes rarely only name and date
    appreciate you attention

  14. #14
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    I am happy to return to a date format of 12-5-15

  15. #15
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    no the issue is how i constructed the break down
    i only thought there was 3 "-" in the file name but based on what you have told me there is actually 5 "-"
    also is [] around the date for all files as well?

    having it Jul or as a plain number doesnt make a difference
    thats ok ill have a look at it tomorrow

    just block that section out (by putting a ' infront of it) for the time being and if everything else works fine then there is only one line to fix tomorrow

    since im checking to make sure im not making another assumption
    is there any chance that there will be "-" in the name or invoice field?

    ie Mary-Jane brown or INV-09328 or something like that
    i would need to find some other way of splitting the Name-Inv-Date if there is
    Last edited by humdingaling; 05-20-2015 at 03:21 AM.

  16. #16
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    Sorry there are no brackets in the date I just put them there as an example
    The basic format is as follows

    Brittian - 12312312 - 12-5-15
    Bill - 212131 - 13-5-15

    on rare occasions

    Jones - 12-8-15

    sorry if any confusion

  17. #17
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    Sorry there are no brackets in the date I just put them there as an example
    The basic format is as follows

    Brittian - 12312312 - 12-5-15
    Bill - 212131 - 13-5-15

    on rare occasions

    Jones - 12-8-15

    sorry if any confusion

  18. #18
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    Humdingaling

    My format is

    Billing - 123123 - 12-5-14
    Tom - 258582 - 13-8-14
    and
    occasionally

    Jones - 13-2-14

    The format is as shown above

  19. #19
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    I use the following layout

    Norman - 123123 - 10-5-14
    Smith - 3215122 - 22-8-14
    and
    sometimes

    Graph - 23-8-14
    Jones - 4-8-15

    I rarely vary from this

  20. #20
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    Try this version

    ive put a check in to determine whether INV is in the pdf filename

    by counting the number of "-" in the file name
    assuming without invoice there is only 3 with...there is 4
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    Thanks for the work you have undertaken here
    I getting a run time error 13 with the following highlighted as the problem
    HTML Code: 
    It might not be possible but I notice that it will only find exact work matches
    e.g. with DHL it will only show exact matches
    If I was to add DH is it possible to get all the names with DH and others or is this asking too much

  22. #22
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    Do you mind blank that section out again and pasting all the hyperlinks in a seperate sheet so i can see what the file names are looking like
    i'm only using mock data to do it ...i may be missing something in my assumption

    with the search
    it isn't doing exact match
    it is using Instr function which does partial matches because it only search to see if the string exists in any part of the file name

    so if you DH Lroberts or RobertsL DH or RoDHbLerts it would return all 3

  23. #23
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    attached please find example files PDF which will give your an idea of the format in the folder

    Again assistance appreciated
    Attached Files Attached Files

  24. #24
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    ok i see the problem....
    you have pdf's which dont have date as well as invoices that dont have invoice number

    back to drawing board

    and one that seems to be missing a "-"

    API - 33435215529-9-14.pdf
    Last edited by humdingaling; 05-21-2015 at 02:25 AM.

  25. #25
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    I always like to make things easy

  26. #26
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    the more i look at this the more i can see issues moving forward
    its always going to hiccup when some sort of naming issues pops up

    if you accidentally put a pdf in that folder that shouldnt be there

    so
    Please Login or Register  to view this content.
    i think your best option is to put an on error resume code in there so it just skips the issue file
    it wont fix it but you if you cant control the file names of the pdf this is the next best thing

    also it seems case is a problem for you
    so amending the search term
    Please Login or Register  to view this content.
    should help that
    mind you if you just search for p d o r F
    it will return everything because...well .pdf
    Attached Files Attached Files

  27. #27
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    Thanks you and I really appreciate your work
    Thanks

  28. #28
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    This is working well for me except I cannot search using a date
    the date format in the PDF files is 12-5-15.
    Can I seek assistance to modify the code to allow for the search by date as well as what already exists

  29. #29
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    how do you search for dates though?
    think of how you would practically use it and i will see if i can build around it

  30. #30
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    You will note the files generally have - name, number and date
    The search using the date would allow for all the files on a specific date to be displayed
    what we have currently allows for name search and an invoice search but I cannot search by date
    the date format on the pdf is 12-5-15

  31. #31
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    i was referring to the syntax of how you search
    you need to be specific
    in the same data if you search "12-5-15" it does come up?
    ps make sure it is text not date format

  32. #32
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: Search for pdf files by date or title

    Sorry for the late reply
    I have not been able to log on to the web site since 6pm last night for some unexplained reason.
    I have altered the cell to text and it does pull dates
    So again my thanks
    Much appreciated

  33. #33
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Search for pdf files by date or title

    not a problem
    the site is being unstable again

    yeah just remember the code a basic text search
    if you want something more fancy it needs to match how you plan to use it
    just remember the more fancy you get the more likely it will break

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. many workbooks title into cell after search
    By mrjackt in forum Excel General
    Replies: 0
    Last Post: 03-05-2015, 08:58 AM
  2. Need to search array and return value along with title and row values
    By wilburr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-28-2013, 06:01 AM
  3. [SOLVED] Search files until date is found
    By twckfa16 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-09-2013, 02:57 PM
  4. Using cell contents to search for corresponding sheet title
    By teblowtime in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-30-2013, 03:31 PM
  5. Replies: 3
    Last Post: 04-08-2012, 06:52 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1