Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Aug 2001
    Location
    Perth,(aka middle of nowhere), Western Australia, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pastespecial problem in VBA with Word - Error 5342 (VBA/Word97/SR2)

    <P ID="edit"><FONT SIZE=-1>Edited by dansy on 16-Aug-01 17:04.</FONT></P>Hi,
    hope someone can help with this as its driving me nuts.
    I have a set of vba subs (see extract below) that open a directory and scans each file (opened read only) of a given mask for text of a given mask. Results are put into a new document for each source file with the .text element of the target find being pastespecial'd as a hyperlink in column 1 of the target document table row.

    This works fine sometimes on the 3 test source files, sometimes processes 2 then bombs (error 5342) and sometimes only does the first file. The first file is always processed successfully and the target file it bombs on is successfully opened and the text found, the newdoc created and the table inserted, but fails on the first pastespecial with error 5342. This is because the ability to paste as a link is not available (in a newly created document!!) but I have no idea why. I have searched and searched for hits on 5342 to no avail.
    Note that even when I stop the macro and go into the newdoc the Paste as Link option is not available. The target document is not protected.
    Any help on this would be appreciated as I am tearing my hair out - and there aint much left to tear out! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>


    ----CODE EXTRACT ---
    Sub req_extract_main
    Set fs = Application.FileSearch
    With fs
    .LookIn = "D:vba code tester"
    .FileName = "abcd-*.doc"
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then
    total_file_counter = .FoundFiles.Count
    MsgBox "There were " & total_file_counter & _
    " REK*.DOC file(s) found."
    For i = 1 To .FoundFiles.Count
    Documents.Open FileName:=.FoundFiles(i), ReadOnly:=True
    Selection.HomeKey Unit:=wdStory ' Set pointer to start of doc
    search_source = .FoundFiles(i) ' Set the source document to the first window
    Set sourceDoc = Documents(.FoundFiles(i))
    OpenNewDoc ' Create the target doc
    ScanReq ' Scan the target and populate the New doc
    Documents(.FoundFiles(i)).Close SaveChanges:=wdDoNotSaveChanges

    Next i
    Else
    MsgBox "There were no suitable files found."
    End If
    MsgBox "A total of " & total_file_counter & " files were searched of which, " & yes_file_counter & " had matching content."
    End With
    End Sub

    ...

    Sub ScanReq()
    With sourceDoc.ActiveWindow.Selection.Find
    .ClearFormatting
    .Text = "[^#^#^#^#:^$^?:^#^#^#]" ' Set the search mask and find criteria
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Do
    sourceDoc.ActiveWindow.Selection.Find.Execute ' Perform the find then assign vars to the
    ' results
    If sourceDoc.ActiveWindow.Selection.Find.Found Then
    With sourceDoc.ActiveWindow.Selection
    found_text = .Text
    found_sentence = .Sentences(1)
    found_page = .Information(wdActiveEndPageNumber)
    End With

    With newDoc.ActiveWindow.Selection ' Check insertion point is in the table then
    If .Information(wdWithInTable) = True Then ' add a row and paste the results
    .Rows.Add
    End If
    found_counter = found_counter + 1
    sourceDoc.ActiveWindow.Selection.Copy ' copy the .text portion so we can paste the link
    Documents(newDoc).ActiveWindow.Selection.PasteSpec ial Link:=True, DataType:=wdPasteHyperlink
    .MoveRight Unit:=wdCell, Count:=1
    .Text = found_sentence
    .MoveRight Unit:=wdCell, Count:=1
    .Text = found_page
    .MoveLeft Unit:=wdCell, Count:=2
    .MoveDown
    End With
    Else
    End If
    Loop Until Selection.Find.Found = False
    If found_counter > 0 Then ' We had at least one match so sort them
    sort_results ' Sort the results
    yes_file_counter = file_counter + 1
    With newDoc
    .SaveAs FileName:="Results of scan of " & Left(sourceDoc.Name, 9)
    End With
    Else
    Documents(newDoc).Close SaveChanges:=wdDoNotSaveChanges
    End If
    End Sub
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>



    UPDATE -- I NOW HAVE CONSISTENCY !
    It bombs on the first paste on the second file EVERY time now.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Pastespecial problem in VBA with Word - Error 5342 (VBA/Word97/SR2)

    I don't use this feature, but it seems to require that Word "know" the filename of the source file through some hidden mechanism in the way it manages the clipboard. Maybe there is a way to interrogate this information at debug time, but I don't know what it would be.

    Two random thoughts. Maybe it would work better if you replaced:

    Set sourceDoc = Documents(.FoundFiles(i))

    with

    Set sourceDoc = ActiveDocument

    if in fact the sourceDoc is the currently active document at that moment? Also, I don't think you need the ActiveWindow property in your sourceDoc and newDoc references, and in fact it seems possible that that might throw it off.

  3. #3
    Lounger
    Join Date
    Aug 2001
    Location
    Perth,(aka middle of nowhere), Western Australia, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pastespecial problem in VBA with Word - Error 5342 (VBA/Word97/SR2)

    <P ID="edit"><FONT SIZE=-1>Edited by dansy on 20-Aug-01 10:47.</FONT></P>First - thanks for responding.
    Debug shows the correct values in sourcedoc and newdoc for the file names.

    re the change in set sourceDoc, I tried that but it didn't help

    With regard to the inclusion of ActiveWindow - if I don't use that then the "Selection" property is not available.

    Another thing I should have noted is that I have eliminated the potential for something weird in the second test file causing the problem by replicating the first test file (which ALWAYS) works, as a new name so it gets used as the second test file - i.e. input data is identical.

    The copy from the active window extracts the right text (tested with sourcedoc.activewindow.selection.text).

    I am running Word on Nt4.0 SP6 if that makes any difference.

    --
    Should also note that changing it from pastespecial to paste makes it work but obviously the links are not there.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Pastespecial problem in VBA with Word - Error 5342 (VBA/Word97/SR2)

    <hr>Another thing I should have noted is that I have eliminated the potential for something weird in the second test file causing the problem by replicating the first test file (which ALWAYS) works, as a new name so it gets used as the second test file - i.e. input data is identical.

    The copy from the active window extracts the right text (tested with sourcedoc.activewindow.selection.text).<hr>
    This sounds crazy, but the only way I can get the "Paste Link" option grayed out is to close the source file. When your routine bombs, is there any possibility that the clipboard still holds a copy of the data from the earlier document? You probably need to use different source data to determine this.

  5. #5
    Lounger
    Join Date
    Aug 2001
    Location
    Perth,(aka middle of nowhere), Western Australia, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pastespecial problem in VBA with Word - Error 5342 (VBA/Word97/SR2)

    <P ID="edit"><FONT SIZE=-1>Edited by dansy on 21-Aug-01 13:53.</FONT></P>The .text value of SourceDoc.ActiveWindow.Selection is set correctly (verified using debug) and the source document is still open (read-only). The previous source document has been closed.
    I have changed the first find target so that I can verify the .text value is correct.

    Apart from the SourceDoc.ActiveWindow.Selection.text value which suggests the .Copy is all ok is there any way to verify the .Copy worked ?

    From the absolute lack of hits on the net (apart from an Excel PasteSpecial bug) it looks I am charting new territory here with pastespecial - I certainly hope thats not the case!

    I am going to try and replicate the problem outside VBA using the exact same sequence and files manually.

    UPDATE
    <font face="BrushScript BT">MANUAL RECONSTRUCTION</font face=brush>
    I have managed to repeat it once manually but not since - not quite sure what I did right the first time(or should that be what I did wrong).

    <font face="BrushScript BT">CLIPBOARD CONTENT VERIFICATION</font face=brush>
    If the clipboard content was not correct then the .paste approach should also fail but it does not - it always works for all files. It is only when I use .pastespecial that the problem occurs after the first file.
    Using the .paste only method I have repeatedly and successfully processed 34 files consecutively 10 times without incident.

  6. #6
    Lounger
    Join Date
    Aug 2001
    Location
    Perth,(aka middle of nowhere), Western Australia, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pastespecial problem in VBA with Word - Error 5342 (VBA/Word97/SR2)

    <P ID="edit"><FONT SIZE=-1>Edited by dansy on 21-Aug-01 16:23.</FONT></P>FURTHER UPDATE
    I have just tried this on Word 2000 on a very old laptop its lack of speed negates the need for step mode in debug!.
    Got a completely different error number but along the clipboard track.

    Run-time error '4605'
    This method or property is not available because the Clipboard is empty or not valid.

    So the pointer to the clipboard problem looks like it is on the right track - back to my question about how I validate the .Copy actually worked?

    Given the .Paste approach works then perhaps the "not valid" part of the error is where to look. Any ideas?

    LATEST
    OK.
    Perhaps I was a little hasty saying it was now consistent. Just tried again after adding a clear clipboard (thanks to another thread) command immediately prior to the .Copy to see if that would help.

    Ran it in Debug and it processed 2 files and then failed on the 3rd. Ran it again and it died on the second file !! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Pastespecial problem in VBA with Word - Error 5342 (VBA/Word97/SR2)

    Since the wall against which we are beating our heads is not getting any softer, let's try Plan B.

    Change this line:

    Documents(newDoc).ActiveWindow.Selection.PasteSpec ial Link:=True, DataType:=wdPasteHyperlink

    To something like this:

    With newDoc
    With .ActiveWindow.Selection
    .TypeText found_text 'as though pasted
    .HomeKey wdLine, wdExtend 'select that
    End With
    .Hyperlinks.Add Selection.Range, sourceDoc.FullName 'assign link path
    End With

    This does not link you to the specific line from which you copied the material but since you are opening sourceDoc Read Only, those hidden bookmarks aren't getting saved anyway, so the functionality is the same, no?

  8. #8
    Lounger
    Join Date
    Aug 2001
    Location
    Perth,(aka middle of nowhere), Western Australia, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pastespecial problem in VBA with Word - Error 5342 (VBA/Word97/SR2)

    <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    The wall wasn't getting any softer but my head was!

    .Hyperlinks.Add Selection.Range, Address:=SourceDoc.FullName, SubAddress:=SourceDoc.ActiveWindow.Selection.Sente nces(1)

    This works and the subaddress option of the hyperlink <font color=blue>does</font color=blue> allow you to link to the specific line/text from which you copied the material regardless of the lack of bookmarks.
    However, if there is more than 1 instance of the text you copied in the document and you use .text as the subaddress then it always links to the first occurrence. Using the .Sentences(1) as the subaddress rather than .Text reduces the likelihood of this.

    My problem is solved however I would love to unearth the problem with pastespecial at some point in the future.

    Thanks a lot for your help. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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