+ Reply to Thread
Results 1 to 15 of 15

search files across multiple sheets based on cell value and highlighted

  1. #1
    Forum Contributor
    Join Date
    01-08-2021
    Location
    Asia
    MS-Off Ver
    2016
    Posts
    292

    search files across multiple sheets based on cell value and highlighted

    hello

    I have some files names in multiple sheets and in multiple columns
    begins from COL A but the end the columns should be dynamic
    not end to specific columns the files are existed in this directory "C:\Users\ORLAD\Desktop\ff" and the files names are existed in
    all the sheets brings by another macro and this directory has many subfolders and different files in extensions so what I want when put the file name in SHEET SEARCH in cells c3 should move and active this cells in specific sheet and highlight by blue and show message to open the file if press ok then open the file and delete color cell which highlighted and if press no then just stay highlight the cells and if I move to sheet SEARCH also delete the color

    keep in your mind every time I create a new sheets and a new subfolders and files and increase the columns when add files names then should the code are dynamic
    Attached Files Attached Files
    Last edited by leap out; 06-07-2021 at 04:50 AM.

  2. #2
    Forum Contributor
    Join Date
    01-08-2021
    Location
    Asia
    MS-Off Ver
    2016
    Posts
    292

    Re: search files across multiple sheets based on cell value and highlighted

    nobody can help?

  3. #3
    Forum Contributor
    Join Date
    01-08-2021
    Location
    Asia
    MS-Off Ver
    2016
    Posts
    292

    Re: search files across multiple sheets based on cell value and highlighted

    issue in another forum
    https://www.mrexcel.com/board/thread...ghted.1173073/

  4. #4
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: search files across multiple sheets based on cell value and highlighted

    Here is an option for you. It currently only operates at one folder level. Place this in a code module

    Please Login or Register  to view this content.
    Place this code in the Thisworkbook code module

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    01-08-2021
    Location
    Asia
    MS-Off Ver
    2016
    Posts
    292

    Re: search files across multiple sheets based on cell value and highlighted

    thanks for your interesting but it gives error in this line
    HTML Code: 
    object variable or with block variable not set

  6. #6
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: search files across multiple sheets based on cell value and highlighted

    try changing vbBlue to 16711680 and vbWhite to 16777215. This updated code has the color updates and now searches subfolders. Use this code in the code module. The thisworkbook module code stays the same as above.

    Please Login or Register  to view this content.
    Last edited by maniacb; 06-07-2021 at 02:32 PM. Reason: Update code to check for file in sheets. Code cleanup. Update for corner case

  7. #7
    Forum Contributor
    Join Date
    01-08-2021
    Location
    Asia
    MS-Off Ver
    2016
    Posts
    292

    Re: search files across multiple sheets based on cell value and highlighted

    it gives the same error in the same line

  8. #8
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: search files across multiple sheets based on cell value and highlighted

    give it another go. I updated the code in post #6.

  9. #9
    Forum Contributor
    Join Date
    01-08-2021
    Location
    Asia
    MS-Off Ver
    2016
    Posts
    292

    Re: search files across multiple sheets based on cell value and highlighted

    awesome ! you achieved about 98% from what I want . when I don't open the file then stay the color that what I want but when I move to sheet search should delete the color as I said and last request can I do that without button just write the file and when press enter then run the macro

  10. #10
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: search files across multiple sheets based on cell value and highlighted

    update the thisworkbook module with:

    Please Login or Register  to view this content.
    and in the "search" sheet module enter this code:

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    01-08-2021
    Location
    Asia
    MS-Off Ver
    2016
    Posts
    292

    Re: search files across multiple sheets based on cell value and highlighted

    thanks but I note two things first it just open files from type just excel but I have png,pdf, jpg,docx ... etc. it should open all of files extensions
    the second when I add a new files names then the cells becomes white color and when search in sheet search it gives me the files are not existed because the white color cells

  12. #12
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: search files across multiple sheets based on cell value and highlighted

    This won't open all file types, but it will open some. See the bottom of the code to see what extensions are included.

    Please Login or Register  to view this content.
    The msg of 'the file does not exist' only appears if the actual file is not in the directory structure. The color of the cell doesn't play a part in that part of the code.

  13. #13
    Forum Contributor
    Join Date
    01-08-2021
    Location
    Asia
    MS-Off Ver
    2016
    Posts
    292

    Re: search files across multiple sheets based on cell value and highlighted

    truly that's excellent ! , I would thank you for your time and your patience , just I want fixing this problem when I add new files names in specific columns it highlights by white color I want no fill color when add a new file name in any column for any sheet

  14. #14
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: search files across multiple sheets based on cell value and highlighted

    In the Thisworkbook module, update the indented line to:

    Please Login or Register  to view this content.
    In the main code before the select case line, update line to:

    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    01-08-2021
    Location
    Asia
    MS-Off Ver
    2016
    Posts
    292

    Re: search files across multiple sheets based on cell value and highlighted

    perfect ! thanks so much for your solution and everything

+ 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. [SOLVED] search files across multiple sheets and open & highlight by inputbox
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 04-16-2021, 08:55 AM
  2. [SOLVED] Search specific text from multiple sheets based on condition
    By sreeks in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-23-2017, 08:37 PM
  3. Replies: 0
    Last Post: 08-21-2017, 12:26 AM
  4. Search multiple files on a row or cell basis
    By Buzzed Aldrin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2014, 04:32 PM
  5. Search for a string in multiple excel files with multiple sheets
    By ghermanu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2012, 05:46 PM
  6. Replies: 3
    Last Post: 02-22-2012, 11:40 AM
  7. Replies: 6
    Last Post: 03-08-2011, 04:08 AM

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