use FileDialog and remember selection (type mismatch)

  • I have this code working in parts but not together. I can pen a word document using the top part and I can insert to a table using the bottom part (but not using file select I have to define the document path.)


    When I put these together I get type mismatch. Any ideas why?


    Type mismatch happens once i select the word document.


  • UPDATE:


    I've changed:


    Code
    objWord.Documents.Open Filename:=fileExplorer


    to:

    Code
    objWord.Documents.Open Filename:=strFolder


    This is correct I believe, However I now get the error: this command is not available because no document is open

  • You store the selected file name in strFolder but use FileExplorer as the file name when opening the Word doc. FileExplorer is the File Browse dialogue object not the (string) name of a file.


    You also append a trailing '\' to strFolder so your edit (which is otherwise correct) will also fail. Remove the '\'

  • Thank you for the reply,
    I have annotated the slashes out of the code which does now open the document. Thank you for that. I still get the error: this command is not available.

  • Please note it doesnt mention no document is open now.


    Also note the code worked before i implimented the file selection dialog. If i manually assign the path for the file there are no problems.


    Andy.

  • It helps if you mention where it errors... most probably the line

    Code
    With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable

    which probably means the document does not contain a bookmark called 'Risk'


    If would probably help if you programmed 'defensively' - check an object exists before trying to use it, especially if you have no control over opening the document. The user may not open the document you expect and then you know exactly what you are dealing with rather than trying to decipher a sometimes obscure error message.


    I would also assign the document object to a variable rather than relying on the ActiveDocument property. Like Excels 'Activesheet', it may not be the one you expect.


  • Set objWord = CreateObject("Word.Application")


    I thinkits on this line. The bookmark does exist ive checked. And the document does open now after your suggestion. Its a bit strange it's "working" to open it and then saying it can't

  • "I think its on this line...."


    I'm sorry, that is not very helpful.


    Use the debugger to find out. Edit the error handler

    Code
    Errorcatch:
         Debug.assert False
         MsgBox err.Description  
         ' This is temporary, if you leave it in it will go into an endless loop so do not forget to remove
         Resume

    When the code stops in the debugger use F8 to single step and see where it returns to

  • Sorry and thanks for the tip there.


    you were correct it is on this line:


    Code
    With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable


    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Screenshot_2.png","data-attachmentid":1207067}[/ATTACH]


    Maybe the document isnt "active" when im in excel? I dont know why it was working previously though.

  • Or maybe PasteAppendTable expects an existing table in Word to PasteAppend to? You haven't said what is in the document.


    This should work to simply paste the copied cells rather than inserting into a table. But without copies of your document/workbook all this is guesswork

    Code
    With oDoc
          If .BookMarks.Exists("RISK") Then
             .BookMarks("RISK").Range.Characters(1).Select
             ' Selection is a property of Word, not the document
              objWord.Selection.Paste
          Else
             MsgBox "The document " & UCase(.Name) & " does not contain a bookmark named 'Risk'", vbExclamation, "Error"
          End If
       End With
  • It has to be paste append, the information is from access but I have a lot of formatting errors when pasting from access. When using excel this DID work.


    Thanks for your suggestions,


    The data to be pasted in had one less column or a blank column. This was causing the error.


    I wouldnt have had a clue where to start without your input so again, Thanks a lot.

  • It is a requirement for this code to run to have the following option enabled in word:


    smart cut and paste enabled.
    Adjust table formatting and alignment on paste enabled.


    However, the table im pasting into then goes half way off the screen.


    Currently trouble shooting, Ill make a new post if i need help as its a different issue. But if anyone else has a similar problem it may help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!