+ Reply to Thread
Results 1 to 46 of 46

Search all workbooks in a specified folder and return the desired cell value and file name

  1. #1
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Search all workbooks in a specified folder and return the desired cell value and file name

    Hi everyone!!

    I have 100+ excel workbooks in a folder with multiple sheets in each workbook. I am in need for a vb macro code that would search/loop through all the workbooks in a specified sheet and return a specified cell value.

    Ex:
    I have workbooks like apple, banana, cocoa, mango, orange. Each of these work book have a sheet named sales and in that sheet [sales] there is a cell "m6" and i need that value in a separate workbook where i want to run this code. Along with value from "m6" i need the name of the workbook from where that value is extracted.

    Data should look like
    s no sheet name value

    additionally:
    If i can add hyperlink to the value, it would be easier and wiser to open if i needed to make any correction.

    Honestly, i tried google but to no avail. I couldn't find any code tailored to my needs. Hence, i turned towards this.

    Thanks in advance.

  2. #2
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Are all the workbooks named similarly e.g. sales1 , sales2 etc or do you already know all the workbook names? If so you don't need VBA for this. You can construct a summary sheet using standard excel functions utilizing the indirect() function to build the path to each workbook.

    If you don't know the file names then you can run some code to get them and then build the summary sheet as per above.

    The basic code for looping through a directory is something like this:

    Please Login or Register  to view this content.
    Although in your case you don't need to open the files just return the full paths to the summary sheet.

    You probably want to mock up some dummy sheets and post them to get a better response.
    Please consider adding a * if I helped.

  3. #3
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    No all workbooks are named differently. So a code is needed that can loop through each file (without opening) return the desired cell value from the worksheet.

    Sorry to bother but, I am novice to programming so I don't know how to write a code. All I do is copy it and run it.

  4. #4
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    This just lists the files in a list from the active cell. Obviously replace the ????? with the directory - path must end with a \

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Thinking about it if you run the macro you might as well build link as well - try this:

    From memory I think when you use Indirect you have to open all the workbooks so not the greatest suggestion!

    Please Login or Register  to view this content.

  6. #6
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Okay, Here is the code.. ( Untested Though )

    Okay Here is the code.
    I have tested it and its working as well.
    Note:- Change the Folder Path in the code before running the code.

    Please Login or Register  to view this content.
    Last edited by Vikas_Gautam; 01-25-2015 at 09:37 AM.
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  7. #7
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Added in the hyperlink bit ....thanks Vikas!

    Note no need to open base files , therefore quicker. You can add in

    Please Login or Register  to view this content.

  8. #8
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Yes, Bendon.. Nice coding..
    But workbook has to update links that may take a some time..
    rest all looks fine...

  9. #9
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    HI VIKAS

    I copied your code into a new Workbook while modifying the directory path. After running the code, there isn't any data fetched from the workbooks is being displayed but only showing the number of files done in the status bar.

    Am i doing something wrong. I copied the code into 'thisWorkbook' page in vba.

    Guide me.

    Thanks in anticipation,

  10. #10
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    HI VIKAS

    I copied your code into a new Workbook while modifying the directory path. After running the code, there isn't any data fetched from the workbooks is being displayed but only showing the number of files done in the status bar.

    Am i doing something wrong. I copied the code into 'thisWorkbook' page in vba.

    Guide me.

    Thanks in anticipation,

  11. #11
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Well, That was working for me..
    Don't place the Code file in the Same folder where Source files are kept..

    Suggestions:-
    Try inserting a new Module in Vba..
    What about Hyper links..? Aren't they working...?

    Or Attach a zip file having your Code file and some individual source files..
    Last edited by Vikas_Gautam; 01-26-2015 at 03:38 AM.

  12. #12
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Hi Vikas !!

    By inserting in module and placing it outside the source directory it worked..Thanks a ton...!!

    May I ask, if I had to add title to each column, how should i tweak the code. Like first colum displays the 'name of the file' second column 'sales values'.

    Thanks once again..!!

  13. #13
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Add this lines to the code after r = 2.

    Please Login or Register  to view this content.
    Change the column head accordingly..

  14. #14
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Heah Vikas

    I used your code but it is taking heavy toll on the cpu performance for it is opening each file and then extracting the values. However, the code provided by Brendan worked like swift, for it doesn't open the work books and it is much swifter.

    However, the issues with Brendan code is that I cannot add headings as done with your code. I added the below code after loop in Brendan code.

    Please Login or Register  to view this content.
    The issue is the when I run Brendan code, it starts from A1 and I am unable to adjust the offset values. Can you help me here.

  15. #15
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Not the most eloquent code in the world but should get the job done.................

    Please Login or Register  to view this content.

  16. #16
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Here is amended code..

    Please Login or Register  to view this content.
    Last edited by Vikas_Gautam; 01-26-2015 at 01:40 PM.

  17. #17
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    After running the first code...
    Copy the following code and paste it in the ThisWorkbook Module:-
    Please Login or Register  to view this content.
    Or
    You can CHECK the Enable Automatic Updating of External Links in the Trust Settings----->External Content Section
    This will be doing the same job of updating links without asking.

  18. #18
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Heah Brendan...!!!

    Thanks for the help, it worked majestically. Really thanks a lot..!!

    However, File Name is returning with extension and other parameters that are associated with it. I just want the file name..is there any way for that??

    ex: Mango_April_2014_Final Figure is my file name in the system and what the code is returning is Mango_April_2014_Final Figure.xlsm.

    What I intent is only "Mango", not all the trailing part. Is there any way..??

    Thanks in anticipation..!!

  19. #19
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    is the delimiter always "_" i.e. you just want whatever is before the first "_" returned?

  20. #20
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    I just want what ever is before first "_". However, sometimes delimiter is "(".

    So consider both the cases.

  21. #21
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    That was wonderful Brendan...Thanks a lot for taking your time and helping this novice.

    If there are two or more directories should I keep changing path everytime?

    I just have a weird thought, if path can be made dynamic, like when I run code, if system asks me for the directory then I can browse and select it. By which way, it will be helpful for everyone who doesn't know vb.

    Moreover, we are only looking for .xlsx files in the code, if there are other type of excel files, wouldn't be useful to include all such??

  23. #23
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Try this.....

    Please Login or Register  to view this content.

  24. #24
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Done !! Perfect !! Wonderful !!

    I love this forum and its members who are so generous to take their time out and help people. Long live Free World. Long Live such people.

    Thanks a lot Brendan and Vikas.

    Nothing more I required except this last tip- Is there any way to bold the headings.??

  25. #25
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    use the following line:-
    Please Login or Register  to view this content.
    After
    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Heah Friends, your codes did wonder. I came back again only to request you, if there is any possibility to add borders to entire table?

    Plus, since I have many rows, can I add dynamic serial number at the left most column i.e., first column as S.No.

  28. #28
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Please Login or Register  to view this content.

  29. #29
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Heah Brendan, code only gave a overall outer border to the table but not a table like look format with each cell bordered.

    And serial number is not coming from first row (excluding headers).

    Another thing I noticed that, since Sales is number I want it in the format of #,##,###.

    Do help.
    Last edited by Ravana; 02-02-2015 at 06:08 AM.

  30. #30
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Try this ....

    Please Login or Register  to view this content.

    not sure what "And serial number is not coming from first row (excluding headers)." this means? What is the problem?

  31. #31
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    The problem of table solved.

    However, the other problems is:

    Excluding header, there are 10 rows of data and I want serial no for those 10 rows. However, serial no starts from 3rd row i.e., excluding header row and first data row.

    Moreover, the cells.style =" comma" changes the header row too. I want change only in data rows but not in header row. The format of "Comma" is giving me 2 trailing zeroes after decimal which looks annoying for a huge data sheet.

  32. #32
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Changed number format - not getting the scenario issue - perhaps post the workbook.

    Please Login or Register  to view this content.

  33. #33
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Yep. Number format, no more issue. thats' taken care by your code.

    However, the serial number issue still persists. Out of 10 lines only 9 lines are being numbered, the first line is not being numbered.

  34. #34
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    I've never got that error and I can't think what in the code would do it. so I'm lost. I think in one of your earlier posts you said it was doing 7 out of 10 and now it's 9 out of 10. So i suspect something outside the code. You haven't got the cell format to set white font have you so the text appears hidden or something similar? Could you post the resulting summary table so i can try to replicate?

  35. #35
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    My error. Sorry.

    I overlooked the declarations at the start, hence I wasn't getting the desired. Problem solved.

    Thanks Brendan, for taking all your time. Really appreciate your generous help. Thanks a lot!!

  36. #36
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    No worries - can you mark the thread solved?
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    Consider some * rep if i helped.

  37. #37
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Heab Brendan,

    Greetings of the day..!!

    Please Login or Register  to view this content.
    This code is allowing me to select which ever folder needed. however, if I cancel without selecting, still it is running the macro.

    I do not want macro to run, if I cancel after opening the folder.

  38. #38
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Try this line in the code after Path = Application......
    Please Login or Register  to view this content.

  39. #39
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Please Login or Register  to view this content.
    This didn't work.

    Giving me error: Run Type error "13", Type mismatch.

  40. #40
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    try this then

    Please Login or Register  to view this content.

  41. #41
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Still, macro is running.

  42. #42
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Post your workbook..!

  43. #43
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Please Login or Register  to view this content.

  44. #44
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    317

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Luckily this code worked.

    Please Login or Register  to view this content.

  45. #45
    Registered User
    Join Date
    09-27-2010
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    [QUOTE=Vikas_Gautam;3968477]Here is amended code..

    Please Login or Register  to view this content.
    [/QUOTE

    Vikas_Gautam,

    This code comes closest to doing a job that I have been struggling with. I have a folder named "05-MAY 2015" and I have a different folder for each month that contains all shipping tickets for the month. I need to extract 3 cells from each of the workbooks in the folder to summarize all that information at month-end. I would like to have a cell on the resulting workbook that updates the file path with the folder name. At present using your code, I have altered the path to be: "N:\BESI EQUIPMENT DELIVERY TICKETS\05-MAY 2015" & "\". I would like the file name (05-MAY 2015) to be input into a cell on the answer sheet where the selected data is to be accumulated so it can be updated each month. Next, I don't get any data returned, even though I changed your cell no. from M6 to my cell no. J9 (I need 3 cell values returned (J9, D11 and J11) from each Sheet1 in each workbook file, but was trying just to get one (like your code has) to work). I get the file name to print and the hyperlink to print, but the contents of cell J9 just comes back as 0 even though there are numerical and sometimes alpha values in J9. The hyperlink doesn't open the file; although it tries -- it just hangs up; but I don't really need the hyperlink. I am using windows 8.1 and Excel 2013 (64 bit) if that makes a difference. I need, beside the file name, 3 columns of data: 1 is headed "Ship Via", the second is headed "Job No." and the third is headed "Location"; all coming from the 3 cells noted. Any assistance you can give will be greatly appreciated.
    Last edited by caroleo; 05-07-2015 at 07:51 AM.

  46. #46
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Search all workbooks in a specified folder and return the desired cell value and file

    Let First row be heading row having Ship Via in A1, Job No. in B1 and Location in C1.
    and E1 for Path(N:\BESI EQUIPMENT DELIVERY TICKETS\) and F1 for Folder Name(05-MAY 2015)

    So here is the amended code:-

    HTML Code: 
    Sub Fetching_Hyperlinking()
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
    
        Dim wbThat As Worksheet    
        Dim wbThis As Worksheet
    
        Set wbThis = ActiveSheet
        FPath = wbThis.Range("E1") & wbThis.Range("F1") & "\" 'Change the Folder Path before running the code
        FName = Dir(FPath & "*.xls*")
        r = 2
        
       'Putting column Heads...
       With wbThis
        .Cells(1, 1) = "Ship Via" 
        .Cells(1, 2) = "Job No."
        .Cells(1, 3) = "Location"
       End with
    
        Do While FName <> ""
            Set wbThat = Workbooks.Open(fPath & fName).Sheets(1)
            shName = wbThat.Name
            wbThis.Cells(r, 1).Formula = "='" & FPath & "[" & FName & "]" & shName &"'!$J$9"    'Ship Via
            wbThis.Cells(r, 2).Formula = "='" & FPath & "[" & FName & "]" & shName &"'!$D$11"  'Job No.
            wbThis.Cells(r, 1).Formula = "='" & FPath & "[" & FName & "]" & shName &"'!$J$11"   'Location
    
            wbThat.Parent.close False
            r = r + 1
            FName = Dir
        Loop
    
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        
        MsgBox "Finished", vbInformation
    End Sub
    Last edited by Vikas_Gautam; 05-08-2015 at 05:33 AM.

+ 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. VBA to search files within a folder for a specific string and return file name
    By tjej6987 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-03-2014, 12:41 PM
  2. Search for a cell value in multiple workbooks and return a range
    By dompainter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2013, 09:27 AM
  3. Search a column across multiple workbooks and return the value of a different cell.
    By MrSpiffdifilous in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-11-2013, 10:42 AM
  4. Help to Search for and in a Text file for a desired string
    By Taylorez in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-14-2010, 06:53 AM
  5. search a folder and return the most recent file's name
    By Pink_Mtl in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-07-2010, 04:13 PM

Tags for this Thread

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