Macro Issue Between Excel and Word

Solved
Hello,
After several questions asked on the forum, which were very helpful, I turn once again to CCM for another point to clarify:

I have a work environment that links two workbooks; when I open it, I land on the workbook [Nomenclature], which is linked to the workbook [Supplier List].

On the Material Air Handling sheet of the [Nomenclature] workbook, there is a button that allows me to open a Word file to create an automatic mail merge using VBA.
My problem is as follows: when I test the VBA in Word, it works perfectly, but when I use it through the Excel button, an error appears, and therefore it does not work.
To visualize the functioning, I think we first need to look at the program of the Word file (for your information, to open the Word file, hold Shift because there is an auto-close), and then try the Excel button after.

Thank you for your help.

List of files: https://www.cjoint.com/?BHom5aYnbaD

Configuration: Windows Vista / Safari 537.1

51 answers

  • Votes
  • Date
  • Descending date
  1. Hello,
    I seem to have spotted a procedure in the file you sent me yesterday.
    I will check if it is feasible.

    --
    Regards.
    The Penguin
    1. Hello,
      Please note that the files I sent you yesterday need to be different because I completed/created the program today.
  2. Hello,
    Could you please provide me with the exact error message...?
    Thank you.

    --
    Best regards.
    The Penguin
    1. Hello,
      In your Word document, it's really not a good idea to have a procedure that closes the file as soon as you want to open it... what's the point of that... what imperative...! Disable this macro [Private Sub Document_Open()] and then try from the Excel application, it should work.

      --
      Best regards.
      The Penguin
      1. The error message is as follows: "Run-time error '5852'. The requested object is not available."
        Then when I start debugging I have:

        With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument (=> highlighted in yellow)
        .SuppressBlankLines = True

        I want to close the original Word file once the mail merge is complete; I don't need to have 2 Word files open, just the file resulting from the mail merge.

        I don't understand why you want to disable [Private Sub Document_Open()] as that is what triggers the automatic mail merge when Word is opened.

        I await your response.
        1. Hello,
          Thank you for the information.
          It would be better to have the mail merge commands directly in the macro on Excel [Sub Bouton1_Clic], and that’s where you close your base document after use.
          Additionally, in your code, the file extension for Word is [.docx] and the base document is in [.docm], which does not work.
          Regarding the error with: [wdSendToNewDocument], check in the Tools of the VBA editor under References that the [Microsoft Word xx.0 Object Library] is checked (if not, look for it in the list..).
          Try first with this last point to see... !

          --
          Regards.
          The Penguin
          1. Regarding [Microsoft Word xx.0 Object Library], it is checked, so my problem is still present.

            Also, in my files the extensions are indeed .doxm.

            Finally, regarding the beginning of your response, I am a novice in VBA (as you may have noticed, I think). I thought that Excel could not control Word and vice versa.
            1. Hello,
              From Excel, you can control Word without any problems. In your Excel code, you have correctly used this instruction (Set Wrd = CreateObject("word.Application") which allows access to the Word object.
              I'm looking to correct the procedure and I'll send it to you as soon as everything works.

              --
              Regards.
              The Penguin
              1. Good evening,

                Alright, I thought we could only open files.
                Thank you.

                Best regards.
                1. Hello,
                  You are using the document [Fiche Technique DOE AERAULIQUE.docx] without the macro [Private Sub Document_Open()] (please remove it)
                  In the module of the workbook [Nomenclature.xlsm], please paste the following procedure applicable to the sheet [Liste Matériel Aéraulique]:
                  Note: Check the path ("C:\Users\Nico\Des.......") and correct it if necessary!
                  The code:
                  Sub Bouton1_Clic() Dim Wrd As Object Dim DocWord As Object Set Wrd = CreateObject("word.Application") Wrd.Visible = True nombase = "C:\Users\Nico\Desktop\Nomenclature et Fiche tecnique\Nomenclature.xlsm" feuilbase = "'Lien Aéraulique$'" nompubli = "C:\Users\Nico\Desktop\Nomenclature et Fiche tecnique\Fiche Technique DOE AERAULIQUE.docx" Set DocWord = Wrd.Documents.Open(nompubli) With DocWord.MailMerge .OpenDataSource Name:=nombase, SQLStatement:="SELECT * FROM " & feuilbase With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Destination = wdSendToNewDocument .SuppressBlankLines = True .Execute Pause:=False ' nomfich = Wrd.ActiveDocument.Name End With DocWord.Close False End Sub

                  --
                  Regards.
                  Le Pingou
                  1. Hello,
                    your protocol works, but when I open the Word file [Technical Sheet DOE AERAULIQUE], it asks me to select my table, but my workbook is not available in the list of workbooks, and when I click cancel, the fields in my Word file are no longer linked to those in Excel.
                    When I go back to the protocol, I get "error 4198, the command failed" and the following line is highlighted:

                    .OpenDataSource Name:=nombase, SQLStatement:="SELECT * FROM " & feuilbase

                    We're getting closer to success ;-).
                    1. Hello,
                      Following your message, I noticed a small syntax error occurring in the response message where the grave accent ['] is transformed into an apostrophe ['], which causes the problem you are encountering.
                      I am currently preparing a comprehensive solution for the 3 lists that I will send via link and there will be no more issue.
                      If you wish to make the correction, the relevant code is:
                      feuilbase = "'Lien Aéraulique$'"
                      You need the grave accent instead of the apostrophe, before the [L] and after the [$]

                      Best regards.
                      Le Pingou
                      1. Hello,
                        My proposal is based on the principle that all files (xlsm, docx) are in the same folder of the directory. For testing, I created 2 fake documents.
                        All procedures are located in the module [lepingou], everything else has been deleted except for module1 (strange because there is no sheet [Fournisseur]...!)
                        You just need to save the proposal in the folder of your choice: https://www.cjoint.com/?3HppxyC4V77

                        --
                        Regards.
                        Le Pingou
                        1. Thank you for this work,
                          however I have a bit of trouble understanding the procedure to follow (sorry).

                          What should I do with [Sub MailMerge(nompubli, nombase, feuilbase, chemin)]?

                          For example, for button 1, should I write:

                          Option Explicit

                          Sub Button1_Click()
                          Dim docpub As Variant, nomcla As Variant, nomfeuil As Variant, chemin As Variant
                          docpub = "Fiche Technique DOE AERAULIQUE.docx"
                          nomcla = ActiveWorkbook.Name
                          nomfeuil = "Lien Aéraulique"
                          chemin = ActiveWorkbook.Path & "\"
                          MailMerge docpub, nomcla, nomfeuil, chemin
                          End Sub

                          Follow-up on:

                          Sub MailMerge(nompubli, nombase, feuilbase, chemin)
                          Dim DocWord As Object, Wrd As Object
                          Dim nomfich As String
                          Set Wrd = CreateObject("word.Application")
                          Set DocWord = Wrd.Documents.Open(chemin & nompubli)
                          With DocWord.MailMerge
                          .OpenDataSource Name:=(chemin & nombase), SQLStatement:="SELECT * FROM '" & feuilbase & "$'"
                          With .DataSource
                          .FirstRecord = wdDefaultFirstRecord
                          .LastRecord = wdDefaultLastRecord
                          End With
                          .Destination = wdSendToNewDocument
                          .SuppressBlankLines = True
                          .Execute Pause:=False
                          nomfich = Wrd.ActiveDocument.Name
                          End With
                          DocWord.Close False
                          Set DocWord = Nothing: Set Wrd = Nothing
                          MsgBox "The document " & nomfich & " is ready"
                          End Sub

                          Or should I compile everything? And the same for the other buttons?
                          If you could clarify this point for me.
                          Best regards.
                          1. Hello,
                            First point, have you tried my proposal directly?

                            --
                            Regards.
                            The Penguin
                            1. Hello,
                              To be clear, the first thing is to try the proposal: either open the workbook [Nomenclature] and for each sheet [List...] click on the button [Fiche TechniquePour] and check if the Word document is correct.
                              Save the proposal on the desktop for example....
                              I await your response.
                              Best regards.
                              The Penguin
                          2. Good evening, yes I used your proposal. I did a copy/paste into a module, but when I click on the buttons I get a "compilation error: undefined variable" which appears specifically at this point:

                            Sub MailMerge(nompubli, nombase, feuilbase, chemin) (=>Highlighted in yellow)
                            Dim DocWord As Object, Wrd As Object
                            Dim nomfich As String
                            Set Wrd = CreateObject("word.Application")
                            Set DocWord = Wrd.Documents.Open(chemin & nompubli)
                            With DocWord.MailMerge
                            .OpenDataSource Name:=(chemin & nombase), SQLStatement:="SELECT * FROM '" & feuilbase & "$'"
                            With .DataSource
                            .FirstRecord = wdDefaultFirstRecord (=>Highlighted)
                            .LastRecord = wdDefaultLastRecord
                            End With
                            .Destination = wdSendToNewDocument
                            .SuppressBlankLines = True
                            .Execute Pause:=False
                            nomfich = Wrd.ActiveDocument.Name
                            End With
                            DocWord.Close False
                            Set DocWord = Nothing: Set Wrd = Nothing
                            MsgBox "The document " & nomfich & " is ready"
                            End Sub

                            However, when I right-click on the button(s) and choose to edit the macro, it directly takes me to the corresponding part of the code for the button. So at this point, I think it should work if the previous error didn’t appear.

                            But did it work during your tests?
                            Best regards.
                            1. Hello,
                              I asked you if my proposal worked and got no response, I appreciate it.. !
                              As a rule, we try the proposal and then we will copy the code to the workbook.
                              Now please specify exactly what you copied and where you pasted it, I am not a mind reader.

                              --
                              Best regards.
                              The Penguin
                              1. Good evening again,
                                to be clear, I tried your proposal as is and the error appeared, then by replacing my files with yours, it was still the same.
                                Seeing this, I tried on my own by deleting my old modules and creating a new one on which I did the copy/paste.
                                Sorry if my response was not quick enough, I was away this evening.
                                1. Hello,
                                  I am attaching the file that I tested and that works for the 3 lists (Aera, Hydr, and Regu). https://www.cjoint.com/?3Hpw1pj9u4G
                                  Copy the ZIP file to your desktop, then extract all the files to a new folder on the desktop, open this new folder, then open the workbook [Nomenclature_jpp.xlw] and try the 3 lists.
                                  Note, no other Excel or Word applications should be running.
                                  I am waiting for your feedback (in case of an error, take a screenshot and paste it into a document that you will send me).

                                  --
                                  Regards.
                                  The Penguin
                                  1. Hello,
                                    Thank you.
                                    And this that I had already specified:
                                    Regarding the error with: [wdSendToNewDocument] check in the Tools of the VBA editor under References that the [Microsoft Word xx.0 Object Library] is checked (if not, search for it in the list..)

                                    --
                                    Best regards.
                                    The Penguin
                                    • 1
                                    • 2
                                    • 3