Recursive search for word doc. Open if found Else open different word doc.

**Travis M.** · 06-08-2016, 08:31 PM

Hello,

I need a way to search for the existence of a word.doc (the name of which is located in a particular cell). If the file exists, I want to open the file and bring it to the front. If it does not existI want to open a different word.doc and bring to the front and complete some code that is already working.

Tricky part 1: if the file already exists, it may be located in multiple subdirectories from a single parent directory.

I have seen a number of posts of recursive functions, but they all seem too complex from my needs and my VBA skills are not quite sharp enough to simplify them.

Tricky part 2: I need something that works without shell scripts or calling on the cmd terminal. I need this code to run while being remotely logged in to a citrix server that does not give me access to cmd terminal (at least, that is my understanding).

Side note:I would like to execute this code from a button click that also copies a designated range of cells to the clip board.

I was able to get everything working before when I just needed to copy info and opening a specific file. I was even able to print info from the workbook to the header of the newly opened word.doc. After getting this to work, however, I realized that there may be some instances when the file is already created. To address this I am hoping to add that "recursive search and open else ..." function mentioned above.

Here is what I have so far, I just don't know how to go about the recursive search:

Please Login or Register  to view this content.

Please let me know if something doesn't make sense.

Thanks in advance for help!

**AlphaFrog** · 06-09-2016, 02:47 AM

Try something like this...

Please Login or Register  to view this content.

**Travis M.** · 06-09-2016, 11:46 AM

Thanks for your help alphafrog! This seems to make sense but it was not able to find a file if it already exists. I made a file and name it the same as what strFile is equal to, and the macro simply opened a new default file (located after the else statement).

I have not worked with functions. Do I need to be doing something special to allow the sub to read the function?

Below is the the same code with a few notes (i changed some of the file paths as I wanted to do a test run. In this test run, I created 3 subfolders in testparent and place a file named the same as strFile in one of those subfolders. When I ran the macro, it opened: I:\Dir1\Dir2\Dir3\Dir4\DefaultFileName" instead of the file that already existed whose name was = strFile.

Thanks again for your help.

Travis

Please Login or Register  to view this content.

**AlphaFrog** · 06-09-2016, 11:58 AM

You don't need to do anything "special" to run the Function. Your code looks correct. I tested the code and it worked for me (using a different root folder of course).

Does the strFile include the extension e.g.; MyFile.doc ? If not, try this...
strFile = Worksheets("Controls").Range("B23").Value & ".doc"

**Travis M.** · 06-09-2016, 12:23 PM

You hit the nail on the head. I added & ".docm" to the end of strFile and it found the correct file and opened it! Thank you so much for your help!

I have just one more question. The way the "if...else" is written can I add "Then Exit Sub" after the "if" to avoid re-printing info to header of a file that already exists?

Please Login or Register  to view this content.

**AlphaFrog** · 06-09-2016, 12:37 PM

Originally Posted by Travis M.

You hit the nail on the head. I added & ".docm" to the end of strFile and it found the correct file and opened it! Thank you so much for your help!

I have just one more question. The way the "if...else" is written can I add "Then Exit Sub" after the "if" to avoid re-printing info to header of a file that already exists?

You're welcome.

You could use Exit Sub or just move the code that is after the End If to before the End If so it only runs within the Else code block.

Please Login or Register  to view this content.

Recursive search for word doc. Open if found Else open different word doc.

LinkBack

Thread Tools

Rate This Thread

Display

Recursive search for word doc. Open if found Else open different word doc.

Re: Recursive search for word doc. Open if found Else open different word doc.

Re: Recursive search for word doc. Open if found Else open different word doc.

Re: Recursive search for word doc. Open if found Else open different word doc.

Re: Recursive search for word doc. Open if found Else open different word doc.

Re: Recursive search for word doc. Open if found Else open different word doc.

Thread Information

Users Browsing this Thread

Similar Threads

Marco to Open all contents (.xlsm) in a folder and search each file for a word

Open Word docm from Excel: Test if already open? Word errors?

Word browsing folder and open txt files in word document.

Search and open a file that can be pdf,word,jpeg etc

Macro to open word doc, then open another file in word and then excute a find replace

How to search for a word in a column and have entire row opaque once word is found

SECOND REQUEST...HELP!Excel Hyperlink doesn't open Word doc the same way as Word does

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions