Consulting

Results 1 to 9 of 9

Thread: Running macros ons linked documents

  1. #1
    VBAX Regular
    Joined
    Oct 2005
    Posts
    15
    Location

    Running macros ons linked documents

    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

  2. #2
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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.

  3. #3
    VBAX Regular
    Joined
    Oct 2005
    Posts
    15
    Location
    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

  4. #4
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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.
    [vba]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[/vba]

    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.

  5. #5
    VBAX Regular
    Joined
    Oct 2005
    Posts
    15
    Location

    it worked!

    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....

    [VBA] 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
    [/VBA]


    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
    Last edited by mdmackillop; 10-17-2005 at 05:35 AM. Reason: VBA wrap added.

  6. #6
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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.
    [vba]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[/vba]

    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:
    [vba]With wrdDoc
    .AutoOpen
    .Save
    .Close
    End With
    Set wrdDoc = Nothing[/vba]

    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.

    [vba]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[/vba]

    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.
    [vba]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[/vba]

    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.

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by fumei
    Do I have tips for optimising code? Hey everybody...I was asked..OK?

    Sure.
    LOL!

    Excellent reading material buddy!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  8. #8
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    bit of a rant.....

  9. #9
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by fumei
    bit of a rant.....
    Depends on the reader...Like always I love your rants!!!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •