+ Reply to Thread
Results 1 to 7 of 7

vba Search and replace

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    new jersey,usa
    MS-Off Ver
    Office 365
    Posts
    9

    Smile vba Search and replace

    Hey guys, New guy on the block , getting into some excel , hope y'all good around here.

    I have a quick question, hopefully it has been resolved or someone has an idea of what it would be.

    I have a Master workbook, that connects to Other workbooks for information , the other workbook are in ADJACENT folders or can be in SUB folders

    example:

    FOLDER A: Contains MasterBOOK , FOLDER B , FOLDER C , FOLDER D are all adjacent within the same DIRECTORY , each of those folders contain a workbook which the MASTERBOOK pull data from. from time to time , I have to move WorkbookA from FOLDER B to FOLDER C... so when i open the MASTERBOOK, the PIVOT Data displays error that WOrkbookA is not found.... now picture this with multiple workbooks and multiple times a day, it can be quite a headache trying to update it every time.... SO , is there a VBA Code that i can run or can run when i open MASTERBOOK to search for MISSING files and FIND them... Please note that The paths are ALWAYS absolute..

    C:\ MAINFOLDER
    -FOLDER A
    MASTERBOOK
    -FOLDER B
    Workbook A
    -FOLDER C
    Workbook B

    I can also have:


    C:

    MASTERBOOK

    C:\MAINFOLDER
    -FOLDER A
    Workbook A
    -FOLDER B
    Workbook B

    Please let me know if anyone knows if its possible!! thanks a million .

  2. #2
    Registered User
    Join Date
    07-31-2012
    Location
    new jersey,usa
    MS-Off Ver
    Office 365
    Posts
    9

    Re: vba Search and replace

    Has this been achieved before ? let me know thanks

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    vba Search for data File Replace / Repair Links in Master File after Folder Change

    Hi xtrato, how’s it going...
    Welcome ( back ) to the ExcelForum
    You have been quite patient, so let me see if I can be of some help_...
    Quote Originally Posted by xtrato View Post
    Has this been achieved before ? let me know thanks
    _.. I expect it probably has. But I do not know where or when and as I am not totally clear as to what you want, I would not be able to search the web too easily. It would be helpful if your Thread title and description of the problem said a bit more clearly and precisely what it is you are trying to search and replace. Your list of File and folders was not too clear either
    _. Anyway , let me see if I can guess a bit what you want. Based on that I will have a go at an initial solution that may at least get you started. By the way, I have no idea about Pivot tables, but I hope that is not directly relevant to the problem

    _. This is how I read what you have and want.
    You have a Master Workbook. You seem to have XL2003, so let me call this File_....
    Masturtrato.xls.
    _.....I assume any macro will go in this File. It is convenient for me to put this for now in a Folder called EFldr1_1_1 which I have inconveniently stored on an external Hard Drive H: The Full path to it is
    H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\EFldr1_1\EFldr1_1_1

    I want to keep it simple to start with, so let me say I have just one_...
    WorkbookEh.xls
    _.....This workbook has in the first sheet ( In my German Excel called Tabelle1 ) some arbitrary value , WbEh_BeeToo, in cell B2
    Using Excel 2007 32 bit
    Row\Col
    A
    B
    1
    2
    WbEh_BeeToo
    Tabelle1
    I have put this file randomly in a Sub Folder, such that its Full path to it is
    H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\EFldr1_1\EFldr1_1_2\Fldr1_1_2_1\Fldr1_1_2_1_2

    This probably looks a bit clearer If I use a code (I intend stealing some bits out of anyway) to show up things a bit more clearly. The code takes in an initial Folder, in our example EFldr1_1, and then gives you a nice list of all your adjacent Folders, Sub Folders, and Files therein. Here you see the Masturtrato.xls at the “Top” in Folder EFldr1_1_1 and WorkbookEh.xls „down“ in Folder Fldr1_1_2_1_2 next to a similarly named WorkbookA.xls
    http://www.excelforum.com/showthread...=3#post4492342

    So now I guess a bit that in your master file, Masturtrato.xls, you would initially have a link, to pull information from the ( closed ) WorkbookEh.xls, for example the value in Cell B2 shown above is to come into cell C2 in Masturstrato.xls.
    You probably know then that in that cell , C2 in Masturstrato.xls , you will have the Formula ( Link )
    Row\Col
    B
    C
    1
    2
    ='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\EFldr1_1\EFldr1_1_2\Fldr1_1_2_1\Fldr1_1_2_1_2\[WorkbookEh.xls]Tabelle1'!$B$2
    Tabelle1

    _......
    If I now move that WorkbookEh.xls somewhere else, arbitrarily, let’s say to here_...
    H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\EFldr1_1\EFldr1_1_2
    _......as you see it here:
    http://www.excelforum.com/showthread...=3#post4492381

    So now I am thinking you want that when you move WorkbookEh.xls somewhere else, that you have a code that will run and For any cells with a “broken link”, the code will search through , find the missing File , and then repair the Link appropriately.

    Now I have a problem here... I do not get an error in the formula ( Link ) in Cell C2 in Masturstrato.xls , after moving the File WorkbookEh.xls , and I am surprised that you do. The reason for this is that ( as far as I know ) when a link is “broken”, Excel automatically goes to its XML “Cache” File where it holds the last value it had for that link... https://www.youtube.com/watch?v=-0cDSt0Io40
    http://www.excelforum.com/showthread...13#post4492413

    Possibly you have some internal settings that prevent that happening.??

    If I knew how to do that, the plan would now be to take each erroring cell and find the missing File and change the link

    For now I will go on to simply go through all links, check the where the file is, and if it is different change the link accordingly.

    So on with that in the next post_........
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    vba Search for data File Replace / Repair Links in Master File after Folder Change

    Quote Originally Posted by Doc.AElstein View Post
    .....
    So on with that in the next post_........
    _...... so here we go:_......

    I am still a bit of a VBA novice myself, but I thought the practice of a try would be worthwhile. ( This means this is probably not the best way to do this, ( assuming I have guessed anywhere close to what you want anyway !! ) )

    I am basically modifying the code used to produce the Tables of the Folders and Files referenced in the last Post, Post #3
    http://www.excelforum.com/showthread...=3#post4492381
    http://www.excelforum.com/showthread...=3#post4492342
    Here is the info on that code
    http://www.excelforum.com/excel-prog...ml#post4332852
    http://www.excelforum.com/showthread.php?t=1149000
    ( By the way, that code is useful when you are doing it manually to check where all your Files and Folders are )
    _....................

    So I did a code: Working on the simplified example showed and described in the last Post, Post #3, the start point is when you have the link_....
    'H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\EFldr1_1\EFldr1_1_2\Fldr1_1_2_1\Fldr1_1_2_1_2\[WorkbookEh.xls]Tabelle1'!$B$2
    _..... in Cell C2 of Masturtrato.xls, but have possibly changed where WorkbookEh.xls, let’s say as previously exampled to the Folder at
    H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\EFldr1_1\EFldr1_1_2

    Correspondingly we now need this Link
    'H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\EFldr1_1\EFldr1_1_2\[WorkbookEh.xls]Tabelle1'!$B$2
    _
    .....

    The code assumes your links are in the First Worksheet.

    Brief Code description of important parts, ( more details in ‘Comments in the Code )

    Rem 2) You are asked for the Folder containing, Masturtrato.xls, and this is assumed to be at the “furthest up” in the Folders and Sub Folders

    Rem 4) We go through all cells with Links in, checking for and amending if necessary the correct File path to the File referenced in the Link ( This can be reduced to just checking erroring cells, if we can clear up the discrepancy of you getting error and me not when the path has changed )

    ' 4a) Some variables are declared here used in keeping track of where we are when searching for the File. ( Not all variables are used in this code version )

    ' 4b) this is the main Loop looking For all Cells

    ' 4c) checks we have something in the cell looking like a Link, and If so

    '4 d) puts the File Name part of the link in a String variable, srchFilename , ( looks like this WorkbookEh.xls )
    and
    puts the bit after the File path in a String variable, strAftrPath, ( looks like this \[WorkbookEh.xls]Tabelle1'!$B$2 )

    ‘ 4 e ) takes all the info we now have and goes searching for that File name in another procedure. We say that at this point we “Call” the procedure. ( Not all the info it is taking is needed in this code )

    Rem 5) This is the function called to find the correct File path and correct the link if necessary. (Actually the code as written replaces then all : if the File path is correct then it gets replaced by what it already is! – For this first code attempt I am not too interested in the best efficient code, as that may be harder than to understand and modify, as I expect will be required. )

    This Function is a bit tricky to explain easily, So I will do that in the next post

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Recursion nightmare Migraines. Go for a jog and a mastur...tra tow instead. Bollox

    Here we go again_....


    Recursion procedure Sub SearchThroughEachFolderAndItsFiles

    This is an example of what is called a Recursion procedure. Often that is defined as a procedure that “Calls” itself. Think of that as you eating yourself. That is not possible. Nor is it possible for a Function to Call itself. So that term “Calling” itself is a load of bollox and incorrect IMO.
    What actually happens is at the point that the function is said to “Calls itself”, is that the current Code “freezes”. VBA then makes another carbon copy of the frozen procedure and starts running that. When that second copy finishes ( Ends ) the frozen initial procedure “unfreezes” and starts again.

    So, bearing that in mind. At the end of the last post we were at the start of our first copy of the procedure. ( The first “Call” )
    '5b) We consider every Sub Folder in the Current Folder Brought into the Function. The first time around this is the Folder you gave when the main calling code asked you.
    '5c) In this code we are only interested in looking at each File within the Sub Folder. ( It is set to only look at .xls or .xlsx Files, but that can easily be changed )

    '5d) checks for a match in the current File name and our search File name, and If so builds the link string, pastes the new String to the Cell and leaves both the looping for file at
    Line 900
    and leaves the going another “level down” Folders via
    lines 970 and 1000

    If we did not find the current Searched File name after looping all File in the current folder, we take the current folder into the next copy of the procedure at the “Call line”. So we “tunnel down” and keep going, as you see by that table _...
    http://www.excelforum.com/showthread...=3#post4492381
    _...as long as there are further Sub Folders

    As we finish a set of Sub folders, that copy of the Procedure stops ( “dies” ) and the last frozen one unfreezes. Effectively we “come back up” then at
    Line 990

    If you have not come across recursion before then if you follow that explanation then you are a lot smarter than me. I can’t follow it just now, and I wrote it. maybe later.. lol..

    If you are interested in understanding, you just need to go through an example, stepping through the code in the development Window using the Debug ( F8 ) mode. One final pint to bear in mind if you do that. Do not be put off by the code progression you “see”. Think of it like this. Every time you hit the start of_....
    Line 990
    _... you effectively put a transparent bit of paper on your screen and you write a new copy of the procedure on that and as you step further you are going through that code copy, and not the one on your screen which is frozen, until you finish going through that copy procedure ( That assumes you get that far before “Calling” the procedure again ) . When you do get to the end of the code on the current transparency, then you chuck that transparency away and go back to the last one at the point you left it ( which is the end of _..
    Line 990
    _....)

    _.....

    Well anyway, I tested the codes ( Honest ! ), and after working with the simple example you get this now in cell C2 in Masturtrato.xls:
    ( The codes are in my 'EP , Extended Player ( Explicit Pedantic ) Format for now a lot of unecerssary stuff and explanations.. see how we get on , then they can be simpplified, may be )

    Row\Col
    C
    2
    ='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\EFldr1_1\EFldr1_1_2\[WorkbookEh.xls]Tabelle1'!$B$2
    Tabelle1

    Here is the main “Calling” code: http://www.excelforum.com/showthread...90#post4492790



    and here is the “Called” procedure:
    http://www.excelforum.com/showthread...=1#post4492797



    -....................

    Good luck. Please let me know how you get on


    Alan
    Last edited by Doc.AElstein; 09-30-2016 at 06:38 PM.

  6. #6
    Registered User
    Join Date
    07-31-2012
    Location
    new jersey,usa
    MS-Off Ver
    Office 365
    Posts
    9

    Re: vba Search and replace

    Thanks a Bunch ALAN, let me digest all of this, and proceed , i will def let you know how it goes.

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: vba Search and replace

    Hi xtrato
    Quote Originally Posted by xtrato View Post
    Thanks a Bunch ALAN, let me digest all of this, and proceed , i will def let you know how it goes.

    OK _.... Good luck
    Thanks for the feedback
    Alan

    Edit: Thanks for the Rep thingy
    Last edited by Doc.AElstein; 10-05-2016 at 04:43 PM.

+ 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. Search and Replace - Need Help!
    By h00ps3rdy1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-01-2013, 11:48 AM
  2. Search and replace VB with VB
    By Mayweed in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-14-2011, 08:02 AM
  3. [SOLVED] Limit Search & Replace's Replace All?
    By [email protected] in forum Excel General
    Replies: 4
    Last Post: 08-03-2006, 02:00 PM
  4. [SOLVED] Search and Replace
    By Abdul in forum Excel General
    Replies: 2
    Last Post: 03-23-2006, 10:45 AM
  5. Search and replace
    By Subu in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  6. Search and replace
    By Subu in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  7. [SOLVED] SEARCH AND REPLACE
    By Michael in forum Excel General
    Replies: 3
    Last Post: 08-23-2005, 02:05 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