Coding help needed for a specific project

[hargy]

Hi programmers

I am a project manager who has to deliver an important project for my client here in UK. The project is to do the following:

- Business areas are to write documents to be supplied electronicaly for an external agency to inspect.
- The documents will contain a large number of hyperlinks to supporting evidence.
- The hyperlinks are to be inserted once the content has been approved by senior management
- the info will be burned to CD so relative rather than absolute pathnames will be used
- the document writers are to ensure the proposed hyperlinks are formatted differently to the other text in the document
- looking at the material there are 50 documents each containing 50 - 100 hyperlinks

Easily this project lends itself for vba. The formatting of the proposed hyperlinks (format is hyperlink word in a colour <document.doc> in red) allows vba to find and insert hyperlinks allowing my support team to check the accuruacy of the CD. The structure of the folders in which the reports and supporting materials are placed is simple and self contained - report1 should have all the supporting materials in the same folder. I need help in identifying a method for these elements:

1. A script that will read a report and copy the proposed hyperlink documents into a text file. It will then look through the reports folder (and subfolders) and produce a list of what is there also. Comparison between the two will identify missing documents and is referred back to the author

2. Once all is available - the report is scanned and the hyperlinks inserted automatically into the report. Any unresolvables are saved to a text file for when the reports are checked for hyperlink accuracy

Is there anybody that can supply assistance in the form of code or ideas as to how these two elements are achieved. I have short timescales to deliver the CD to the inspectors so all help really appreciated

Hargy

 

[xlbo]

Are you intending on writing this code from scratch yourself or getting someone else to do it ??

What is your experience level with VBA if you are going to do it yourself ??

I only ask as you have stated that you are a project mgr rather than a programmer and the code to do this is likely to be long and complex.

Because of the nature of this site, it is not expected that people will respond with full answers - you are more likely to get snippets of code or suggestions of areas to look into - therefore, if you are doing the coding yourself, I would suggest that you need quite a strong background in VBA for this....

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[hargy]

Geoff

I am good with vba in access, excel, less so in word. As a PM I am multi skilled technically in networking, pc and server architectures, coding (in Fortran 77 at uni) and vba amongst others - I am a jack of all trades and as the saying goes master of none (typical PM!!).

I was looking for pointers from vba experts and small help really - noone can complete this successfully than me, but ideas are really useful and code help eliviates tiresome reaserch time which I dont have.


Hargy

 

[xlbo]

no probs - just checking that you had some VBA knowledge

I have to say though, that after all that, I don't know that much about Word myself - I just wanted to make sure that whoever posted got the right idea about what you needed

There's some good Word experts here but you may want to also try:

http://www.theofficeexperts.com/forum/

as that is Dreamboat's own set of forums so you may get some help from her there...

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[hargy]

Geoff

Thanks for the help - I am not scared of the work just the pressure to get it done in time

Hargy

 

[fumei]

I do know Word well and some things jump out to me immediately.

1.

A script that will read a report and copy the proposed hyperlink documents into a text file.

Proposed hyperlinks. Create a unique style for the proposed hyperlinks. This will be critical. When the document is being approved this will make it easier to get the information out to a routine, as in:

Search for text with ProposedHyper style attached.
Select and copy that text.
Paste into the text file.
QUESTION: not sure UI am following the process here. Why are they copied to a separate text file??

If a "proposed" hyperlink is approved, having a specific style makes it a snap to go back, find that style, and if approved, reapply a different style (one to make it a real hyperlink, the other to make it something else. What? regular text??)

2. "Proposed hyperlink documents" - I am assuming the word document is not literal. What is happening is what i wrote above, the TEXT of the name of the proposed hyperlink is copied to a text file. You are not talking about actually getting the contents of the documents themselves. Correct?

3.

the report is scanned and the hyperlinks inserted automatically into the report. Any unresolvables are saved to a text file for when the reports are checked for hyperlink accuracy

Scanned? By that do you mean someone who look at it? There is a inconsistency here. On one hand you seem to have proposed hyperlinks that wait for approval - on the other hand, you have automatically inserted hyperlinks with unresolvables (I am guessing that means the proposed hyperlinks do NOT match to the report folder files) checked for accuracy. If they are unresolvables, then it seems to me they are, by definition, inaccurate.

Geoff is bang on. This is quite do-able, but it will be long and complex.


There is a further apparent inconsistency. It sounds like this is a verification process of a CD. Yet is also sounds like it is a verification process to make sure things are good FOR the creation of a CD.

- The documents will contain a large number of hyperlinks to supporting evidence.
- The hyperlinks are to be inserted once the content has been approved by senior management

How can the document CONTAIN the hyperlinks that will be inserted after approval? It can not. If they are proposed, then how are you going to store the proposed URL?

Again, while the specific design steps need to be clarified (at least to my mind), conceptually this is quite possible, but not trivial. So, in reply to your #1 and #2.

#1.

Questions:
Clarify "copy proposed hyperlink documents to a text file."

Partial solution:
If the proposed hyperlink document is just a filename in the initial report, grab each one by style name, use a FileSystemObject and FileExist to confirm it does exist.

Write the path for each to a file.

Problem: this initial step is (correct me if I am wrong) NOT on the CD, but on a local/network machine. If it IS on a CD, the confirmation file has to be written somewhere. Are you dealing with an unclosed CD?

Question:

Is the approval process on quality control - does the hyperlink follow correctly, or is the approval process also on content? If a proposed hyperlink works correctly, can the approval process demand the removal of the propsed link? If so,

Possible solution:

A UserForm that pulls all proposed hyperlinks (as document name) into a combobox. Has three command buttons - Check, Approved, and Not Approved. Check button does a file system
verification, looking for FileExist, and if found displays the path in a label. Approved does the style swap from proposed to a real hyperlink, inserting the path from the label. Not Approved converts the proposed to regular text (or removes it entirely...again, this is a serious design stage determination).

As stated, there are some process flows that seem not quite right to me, but it sounds like a fun project. I hope your time constraints are not too too constraining, because...well, this one (if you truly want to automate it as much as possible) will take some major work. Word is certainly up to the task though.

Gerry

 

[hargy]

Sorry my use of language should be better and less colourful. The text is as

blah blah blah blah blah blah blah blah blah blah blah blahblah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah hyperlink <document.doc> blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah

the document.doc is the file to be inserted into the hyperlink propoerties and the word hyperlink is where the hyperlink will go. So we have 50 reports containing this format and between 50 and 100 per report. The purpose of this exercise is to take the legwork out of the cd creation process which will be resource intensive without computer assistance

First thing is to write code that moves through the document and copies the hyperlink information (as shown above) as data to a spreadsheet or access table. Because users have entered this information (and make errors) thislist per doc needs to be cross referenced with whats in the report folder. The users will compile the documents to be hyperlinked in the same folder as the report. The files in the reports folder need to be captured as data and compared with the data obtained from the report. This will ensure first level accuracy of the hyperlinks

Secondly, vba code needs to be written that then inserts the hyperlinks as a macro process into the reports

 

[fumei]

First thing is to write code that moves through the document and copies the hyperlink information (as shown above) as data to a spreadsheet or access table. Because users have entered this information (and make errors) thislist per doc needs to be cross referenced with whats in the report folder. The users will compile the documents to be hyperlinked in the same folder as the report. The files in the reports folder need to be captured as data and compared with the data obtained from the report. This will ensure first level accuracy of the hyperlinks

Secondly, vba code needs to be written that then inserts the hyperlinks as a macro process into the reports

1. The normal insertion of a hyperlink in Word will not work if the link is not there. In other words, you can not (normally) put a hyperlink into a document without an actually link. The hyperlinking process links to four possible places, with some sub places.

a. Existing file/web page

b. Local places with document (ie. current doc)

c. creating a new document

d. an email address.

In other words, you can NOT make a broken link. It can of course be broken later, but not when you insert a hyperlink. Hyperlinks ARE real links. So blah hyperlink <document.doc> IS a functioning hyperlink.

SO "document.doc is the file to be inserted into the hyperlink propoerties and the word hyperlink is where the hyperlink will go."

Could you give a specific example of this text. You can change the actually doc name and the hyperlink. All I can think of is something like:

blah blah blah folder1/whatever thisdoc.doc blah blah

Is this correct? It seems a rough process. The originator should insert a hyperlink (which if they can not work, will not show as a hyperlink). If they can not find it, and therefore caan NOT make the hyperlink they write:

blah blah blah unknown linkfilename

Because users have entered this information (and make errors)

. Perhaps in earlier version it is possible to make errors, but from XP (Office 2002) you can NOT make initial errors - either the hyperlink DOES link, or there is NO hyperlink. Hpwever, it IS possible to make an error in linkingto the wrong file.

Are you saying the cheker is going to follow every link to see if it is correct?

OK. Build a UserForm that loads three comboboxes.

Combobox1 (CB1) loads (by Style) every instance of the hyperlink style - NOTE you can have text that does NOT actually jump, but still has the style. So the "Unknown link would still be picked up.

CB2 has conditional logic that matched CB1. If item 23 is displayed imn CB1, then item 23 will display in CB2. Contents will be the document name associated with the hyperlink (known or otherwise) in CB1.

CB3 is a validation Yes/No. Could be a yes and No Option button in a frame, as you can onyl choose one.

I gotta go right now, but reply further shortly.

Gerry

 

[hargy]

Thanks for your help with this one - we are reaching a solution and I shall be publishing the code on here soon for others to use as necessary

Hargy

 

[fumei]

That would be good. It is a complex problem, and sharing a solution would be appreciated by many, I am sure.

Gerry

 

[hargy]

We broken the problem down into smaller parts and now we are joining these up to form a neat solution - I have to say I was loaned a developer who took the arduos process of code writing away from me - allowing me to be systems analyst / ideas - my dad would be proud having been a programmer and anaylist (now retired) for 40 years

 

[fumei]

broken the problem down into smaller parts"

WHAT A CONCEPT!



Gerry

 

[comaboy]

Hargy,

This may be of use to you:

If the hyperlinks in the document are "real" hyperlinks (ie, they wotk when you click them), then they will be members of the Hyperlinks Collection. So the following code would be *really* annoying for 50-100 links, but should get you going in the right direction.

Sub GrabHyperlinks()
    Dim hypLink As Hyperlink
    For Each hypLink In Hyperlinks
        MsgBox hypLink.Address
    Next
End Sub

All it really does is iterate through the collection - you can then use this to write the results to a text file or whatever.

If you google for "recursive files and folders" (no quotes) you'll soon find the code to grab the documents list.

With this you should be able to stitch a solution together fairly simply.

Hope this helps!