Running macros ons linked documents

[1with49]

Hi...

Basically I have built a long document that uses lots of links like the following:

{LINK Word.Document.8 C:\\Inetpub\\wwwroot\\Alcatel\\output\\temp_issues12345.doc \f 0 \r \* MERGEFORMAT}

But all of these documents contain macros that need to be run on open, and currently when using link this doesnt happen. How do I run a macro on a linked document in VBA?

Thanks Again Michael

[fumei]

That is because the linked documents are not, in fact, opened. That is the purpose of OLE (linking documents) - the source document is NOT required to be "opened". So of course the Document_Open event does not fire....the linked document is not opened.

Please give more details.

1. is each document open code different?
2. why is each document open code required?
3. what does the document open code do?

There are a couple of ways to fire code in another document. It would be helpful to get a good description of the purpose, and logic behind this.

[1with49]

Basically all the docs contain the same macro, the reason ive have done this is because if I just attached the macro to the complete merged document and ran it, then it crashed, but I think it would work if I fired it accross each document in turn then linked them. If I can I would really only want to fire the macro on the final document once instead of doing it several times.

Any ideass, I can post the macro code if it helps

[fumei]

Here is my reply on the other thread. We will move further posts on this subject to THIS thread.

* * * * * * * * * * * *

Ah, I believe this is being covered by another thread. Linked documents are not opened in the normal way. Therefore your code does not operate on them.

You could build an array of the linked documents name and path, and then run through that array opening the files.
Dim oField As Word.Field
Dim SourceFiles() As String
Dim i As Integer
For Each oField In ActiveDocument.Fields()
ReDim Preserve SourceFiles(i)
SourceFiles(i) = oField.LinkFormat.SourceFullName
i = i + 1
Next

This builds the array. Now you have a string array, a list, of all the document names in the LINK fields. Using SourceFullName gets the full path. if you want you could use SourceName to just get the document name, without the path. This would only be useful if the linked documents are all in the same folder.

In any case, with the list, you can run through it and open the files.

Alternatively...depending on where and your code is doing, you could use the linked documents name to fire code in them.

[1with49]

Thanx for that, ive got it working correctly now, altho my tagging macro <bold> and stuff could use some optimisation as it takes 2 mins+ to run at the moment but here is the code I used in the end....

Dim wrdApp As Word.Application
'Set wrdApp = CreateObject("Word.Application")
Set wrdApp = New Application
Dim wrdDoc As Object

Dim oField As Word.Field
Dim SourceFiles() As String
Dim i As Integer
For Each oField In ActiveDocument.Fields()
ReDim Preserve SourceFiles(i)
SourceFiles(i) = oField.LinkFormat.SourceFullName
Set wrdDoc = wrdApp.Documents.Open(SourceFiles(i))
wrdDoc.AutoOpen
wrdDoc.Save
wrdDoc.Close
i = i + 1

Next
' Quit this instance of Word.
wrdApp.Quit
' Clear variable memory.
Set wrdApp = Nothing
Set wrdDoc = Nothing



It worked a treat which is nice, not often I get things working this quickly!!!
Do you have any tips for optimising VBA code in general? Thanx again Mike

[fumei]

First off, use the VBA window on the site for code. That is what it is there for. Here is your code, in a code window.
Dim wrdApp As Word.Application
'Set wrdApp = CreateObject("Word.Application")
Set wrdApp = New Application
Dim wrdDoc As Object

Dim oField As Word.Field
Dim SourceFiles() As String
Dim i As Integer
For Each oField In ActiveDocument.Fields()
ReDim Preserve SourceFiles(i)
SourceFiles(i) = oField.LinkFormat.SourceFullName
Set wrdDoc = wrdApp.Documents.Open(SourceFiles(i))
wrdDoc.AutoOpen
wrdDoc.Save
wrdDoc.Close
i = i + 1

Next
' Quit this instance of Word.
wrdApp.Quit
' Clear variable memory.
Set wrdApp = Nothing
Set wrdDoc = Nothing

Do I have tips for optimising code? Hey everybody...I was asked..OK?

Sure.

This specific code is pretty simple actually, and can not be optimised all that much. However, it can be tidied better.

You should look up Scope in Help. Properly speaking, you should destroy the document object wrdDoc with each iteration of the For..Next. As in:
With wrdDoc
.AutoOpen
.Save
.Close
End With
Set wrdDoc = Nothing

Oh, and while not truly noticeable on current fast computers, it is a good habit to use With statements in code. The code is parsed faster....and it is less typing, PLUS makes code (generally) easier to read.

Anyway, the object wrdDoc should properly be destroyed in scope, in each iteration. Yes the code will run, and the object is set to the next document. However, it better to release it ( = Nothing) completely before setting it again.

Why? Because of what it is actually doing. Creating an object (declaring it, and then setting it) allocates memory addresses. But if you think about it, Document1 is really a different object than Document2. What if Document2 requires more memory addresses than Document1? True, even Windows (more robust that it used to be...but....) can handle the task. But what actually happens is that the additional memory addresses have to be looked for, PLUS those new memory addresses will not be contiguous with the previously allocated block.

So destroy an object when it is no longer needed. In your code this is when you close the document. Hey it is CLOSED, right? Therefore the object that corresponds to it is no longer needed. Release it. The Set wrdDoc instruction for the next document object is already in your code.

I have a question, from a design perspective. You have all these documents, and you run code on them as they are opened. The code is, in fact, in the Document_Open event of those document...oh, no it is in AutoOpen.

Whatever, the point is...if the AutoOpen code is the same for each document, why not put it in the main document, and run it there in a loop? Why does it have to be in the AutoOpen routine in each document?

From a code execution perspective it is better to have it run ONCE, but with multiple iterations (each document).

Here is what your code does, and what is going on. I will skip for a moment the creation of the instance of Word.

Loop through the current document Field collection building an array of the SourceName of the LINK fields.

Comment: in this instance, the array is NOT needed. It was there to get a list of the SourceFullName strings. However, as your code stands it is not needed. Why? Because you HAVE the string of the document name within the loop. You are ALWAYS using i as the index of SourceFiles()...so why bother? Here is your code "optimised" for this.

Dim oField As Word.Field
For Each oField In ActiveDocument.Fields()
Set wrdDoc = _
wrdApp.Documents.Open _
Filename:=oField.LinkFormat.SourceFullName
With wrdDoc
.AutoOpen
.Save
.Close
End With
Set wrdDoc = Nothing
Next

And it works. The reason that I built the array was to isolate instructions. Getting the string names is a different instruction from actioning the results. However, VBA is quite flexible, and in fact your way could be better...I have to think about it.

Here is what your code could look like if you removed the AutoOpen in each document and ran instructions from the main document.
Sub ActionOnEachDoc()
' whatever it is you are doing
' whatever it is you are doing
' whatever it is you are doing
' whatever it is you are doing
' whatever it is you are doing
With ActiveDocument
.Save
.Close
End With
End Sub

Sub DoIt()
' blah blah make the Word instance
For Each oField In ActiveDocument.Fields()
Set wrdDoc = _
wrdApp.Documents.Open _
Filename:=oField.LinkFormat.SourceFullName
Call ActionOnEachDoc
Set wrdDoc = Nothing
Next
' blah blah ...destroy the worlds
' blah blah
End Sub

You see, in your code you are opening each document, then explicitly running AutoOpen. Each and every AutoOpen has to be read, parsed, then executed. The above opens each document and runs the Sub ActionOnEachDoc..which has already been read and parsed.

Blah blah blah...this has gone on long enough.

[MOS MASTER]

Do I have tips for optimising code? Hey everybody...I was asked..OK?

Sure.

LOL! :rotlaugh:

Excellent reading material buddy! :)

[fumei]

bit of a rant.....

[MOS MASTER]

bit of a rant.....

Depends on the reader...Like always I love your rants!!! :)