Search for a docx file and extract info from it's name

[Zapdor]

Hi everyone!
I'm kinda new to vba, and trying to automate some tablets at work.
Now here is my problem:
I have a folder that contains 12 sub-folders, one for each month.
my co-worker adds docx files every once in a while.
* he names them this way: "File No. - Date - Explanation" (for example: "152 - 12012012 - My friend is a VBA freak")
* I have an excel table, with the headers: "File No.", "Date", "Explanation"
my goal is to write down a code that when i fill the "file No." section, the rest of the table will automatically be filled.
- unfortunately, i cant figure out how to search for a file with the number as part of the name and save it's adress.
- furthermore, how to extract the rest of the info from the file's name.
once i have the adress of the file, i know how to add hyperlink.

THX A LOT!!!

[JBeaucaire]

This macro will watch column A for a value you type in. It will search the fPATH folder and go into each of the month folders listed in MyFolders one at a time until it finds a file starting with that filename. Found, it will put the datestring in column B and the description in column C.

Please Login or Register  to view this content.

This macro goes into the Sheet module.

[JBeaucaire]

Here's an adaption that will add the HYPERLINK for you in column D, too.

Please Login or Register  to view this content.

[Zapdor]

Thx a lot!!!

I'll let you know if it's working as soon as i try it

[snb]

@JB

What aubout ?

Please Login or Register  to view this content.

[Zapdor]

Haven't tried it yet, but I tried understanding the code, and I have 2 little questions:
1. the line
Target.Offset(, 2) = Left(MyArr(2), InStr(MyArr(2), ".") - 1)
could you please explain to me what the InStr function is supposed to return?

2. why disable and enable events?

Thx!

[Zapdor]

@JB

What aubout ?

Please Login or Register  to view this content.

I assume mn is month,
which list is 4? (sorry for the ignorance, i'm really new to this)

[snb]

Use the help - F1 - in the vbeditor - alt-F11 - to get to know more

Just try this

Please Login or Register  to view this content.

[Zapdor]

Use the help - F1 - in the vbeditor - alt-F11 - to get to know more

Just try this

Please Login or Register  to view this content.

K so:
a. idk why, but i find this one cool thx
b. i think that the line
Target.Offset(, 2) = Left(MyArr(2), InStr(MyArr(2), ".") - 1)
should be just
Target.Offset(, 2) = MyArr(2)

no?

Also,
if i only want this code to activate when i enter a number on the FIRST column,
i can add "If Target.Cells.Column <> 1 then exit sub"
right?

and i'm still not sure why to disable and re-enable events.

Thx again!

[JBeaucaire]

Haven't tried it yet, but I tried understanding the code, and I have 2 little questions:
1. the line
Target.Offset(, 2) = Left(MyArr(2), InStr(MyArr(2), ".") - 1)
could you please explain to me what the InStr function is supposed to return?

The filename retrieved will be something have something "This is my description.docx", that line of code will attempt to strip off the extension and leave just the description This is my description.

2. why disable and enable events?

Since this is a "event" macro that triggers itself anytime a change is made on the sheet, we want to suppress triggering it again while we write to the adjacent cells.

[Zapdor]

AWSOME! thx a lot!!!

only 1 question left unanswered:

if i only want this code to activate when i enter a number on the FIRST column,
i can add "If Target.Cells.Column <> 1 then exit sub"
right?

[JBeaucaire]

Probably, yes. Easy to test, right?

[JBeaucaire]

@SNB Doesn't that presume no one has made any changes to the builtin custom lists?

[snb]

@JB

As far as I know it doesn't. You can't change the first 4 customlists, because they are builtin. You can only add 5,6,7,8,9, etc.

I have an item on customlists in my website; I still have to translate it to english.

[Zapdor]

OK.
So I tried it today, made some adjustments for my Workbook.
Except for 1 small mistake (the hyperlink adress starts with fPATH and not fNAME), it ran FLAWLESSLY! thank you so much!
(and it's also so cool!)

In the next couple of days i'm going to take it to the next level (trying to extract info from inside the word document for the rest of the columns in my table).
If I have any trouble I'll let you know

THX AGAIN!

[Zapdor]

Couldn't wait...

So i haven't tried it yet on my table, but I think I know how to write a code for my issue.

I only have one problem now:
The documents I'm refering to are form-filling type.
Since it's MS 2010, the form-fields are content controls.
I know I can give each a bookmark and use "Word.Application.Bookmark(myBookmark).Range.Text"

But is there a way to tell excel to directly look at the right content control and copy it's text, without using bookmarks?

Once again, Thanks

[snb]

Please Login or Register  to view this content.

[Zapdor]

~sorry for the delay~

I've used bookmarks and everything works fine. Still, I'd like to understand the ContentControl thing for future use.

I don't want want to loop through all content controls and check their text.
What I want to do, is something like this:

'In ActiveDocument.ContentControls
MyContentControl = Find(MyText).Range
MyContentControl.Range.Text.Copy
ActiveSheet.Range(MyRange).Paste

Is the line
MyContentControl = Find(MyText).Range
legitimit? I want to put text like the Writer's_Name, and it will find the content control with that name(the content control's title)


Edit:
OK, I just figured myself what I exactly I need.
My ActiveDocument is WDoc.
I tried writing this line:
WDoc.ContentControls.Title.Find(what:=myText).Range.Text.Copy

Obviously it doesn't work... and ideas?

BTW snb:
Using your sub DOES work, if I type WDoc.ContentControls, and add "If (ct.Title = MyText) Then...MyRange = ct.range.text".
Still, looping again and again through all content controls is slow, and I'm sure there's some way to directly adress MyContentControl.

[Zapdor]

Hi again!

Sorry for the double-post... but I had to tell you I found a solution!!!
From your posts I learnt how to use objects (told you I'm new at vba..). After googling a bit, I found this command: SelectContentControlsByTag(MyTag).
I want to look for the content control based on it's title, so I just tried: SelectContentControlsByTitle(MyTitle)

And here is my solution:
Dim WContentControl As Object
Set WContentControl = WDoc.ContentControls
Set WContentControl = WDoc.SelectContentControlsByTitle(MyTitle)
Range(MyRange) = WordContentControl(1).Range.Text 'since my content controls are actually quick-parts, all content controls have the same content. so addressing
WordContentControl(1) is equal to addressing any other location of the array.

Tomorrow I'll change my bookamarks-code to titles-code.
If you have any comments I'd love to know.

Thanks again SO MUCH for all your help!!!
Next time you'll hear from me I'll probably have troubles going the other direction: putting the data in the excel sheet and let it automatically open the right word template, changing the data and saving the file with the appropriate name. Good luck me!