Macro Issue Between Excel and Word Solved

Question

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

Le Pingou · Answer

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

Le Pingou · Answer

Hello, Could you please provide me with the exact error message...? Thank you.  -- Best regards. The Penguin

Le Pingou · Answer

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

nicodu072 · Answer

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.

Le Pingou · Answer

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

nicodu072 · Answer

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.

Le Pingou · Answer

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

nicodu072 · Answer

Good evening,  Alright, I thought we could only open files. Thank you.  Best regards.

Le Pingou · Answer

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

nicodu072 · Answer

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

Le Pingou · Answer

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

Le Pingou · Answer

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

nicodu072 · Answer

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.

Le Pingou · Answer

Hello, First point, have you tried my proposal directly?  -- Regards. The Penguin

nicodu072 · Answer

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.

Le Pingou · Answer

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

nicodu072 · Answer

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.

Le Pingou · Answer

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

nicodu072 · Answer

There is still a problem, however now the error message says "Project or library not found" instead of "Variable not defined."
Attached are the error messages: https://www.cjoint.com/?BHpxIx8llcJ

Could it be related to a path issue?

Le Pingou · Answer

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

Le Pingou · Answer

Hello, I'm only providing you with the instructions if everything works correctly. Copy the complete module [lepingou] and paste it into the module of the workbook. Save it, then assign each command button by right-clicking [Assign Macro...] and clicking on the appropriate button in the list, then OK, and repeat for the other two. Good work and good night. -- Regards. Le Pingou

nicodu072 · Answer

Wonderful, it works very well. Indeed, [Microsoft Word xx.0 Object Library] was unchecked. Having been checked before, doesn't this function remain by default?  Now, I suppose that if I create other buttons to publish from another database, I can follow your protocol by using your database to add the buttons and simply modifying the [docpub] and [nomfeuil] variables?  One last question, do you also know of a site or something that explains VBA in detail and thoroughly, because for me it seems complex to know what and how to declare the variables and especially the syntax to use.  In any case, I am very grateful for all the help and time you have given me to solve my various problems.  Once again, a big thank you.

Le Pingou · Answer

Hello, Thank you for the information. For a new order, you copy one of the macros (for example [Bouton_Aera_Clic()]), change its name to [Sub Bouton_nouveau_Clic()] and only modify [docpub] and [nomfeuil], the other two are filled in by the references from the base workbook. However, I have one point that is not clear: why do you have 2500 lines with formulas on each of the [Lie....] sheets and for the corresponding tables of the [Liste Mat ...] sheets, there are no more than 45 lines.....? What are the tables supposed to be for? Various links: https://heureuxoli.developpez.com/office/word/vba-all/ http://boisgontierjacques.free.fr/ https://www.developpez.net/forums/ https://www.lecompagnon.info//demos/index.html  -- Best regards. Le Pingou

nicodu072 · Answer

Hello,   I have 2500 lines because I don’t know how to automatically fill in the table [Link ...] when I enter information in the table [List ...], where I have extended the formulas over a certain number of lines in [Link ...] which is linked to [List].   I am currently optimizing my workbooks:   - With a button I added, I add a new line at the end of the table [Material List Aéra], however, I cannot drag the formula from cell Gx to cell Gx. Today, I can only paste exactly the same formula and not link it to its row. With this same button, I am forced to stretch it from my first line on the [Link Aéra] sheet to all my other lines on this sheet to ensure I have a coherent sequence of formulas in case of adding or deleting a line on the [Material List Aéra] sheet.   - I also perform an A to Z sort with another button on [Material List Aéra] which at the same time removes duplicates on the [Link Aéra] sheet to avoid repetitions during mail merges.   Do you have a solution for my first problem? And is there any way to optimize my different buttons?   For your information, my macros are located in Module 2 of the [Nomenclature] workbook.   File: https://www.cjoint.com/?BHqpB3elh7D   Best regards.

Le Pingou · Answer

Hello, Note that adding a line with a button is really not a good idea. Do I understand correctly that the tables [link..] are there only for mail merges ... Yes / No? Before undertaking anything, what is the exact purpose of your 3 lists?    Regards. The Penguin

nicodu072 · Answer

So the table [Material List ...] is a table that we need to provide to our clients and the table [Link ...] does indeed allow us to gather the different information for the mailing. This information therefore comes from the workbooks [Suppliers List] and [Nomenclature].  I wanted to add a row with a button because I have, as explained earlier, a formula problem that no longer follows on the sheet [Link ...] when I add or delete a row on the sheet [List...].  To remedy this, I have created (due to my lack of knowledge) this button where I recorded the macro that adds a line at the end of the table on the sheet [Material List ...] and extends my formula over the 2500 rows of the sheet [Link ...] so that they follow correctly.  I wouldn’t have created this button if this problem didn't exist. For your information, in the end the sheets [Link ...] will not be modifiable or visible to users of these workbooks. That is why I wanted to correct this error through a sequence of actions via the button.  If any points are still not clear to you, I am at your disposal.

Le Pingou · Answer

Hello, Thank you, but I'm not asking you how you do it, but rather what result you need to achieve. The table is therefore established for a client, and you save it in an archive, while at the same time it is used to create the Technical Sheet for the DOE. Then we start again with a new client...! If that's correct, then how many lines do you need per client... That's the question... The solution depends on all of this. By the way, the 3 tables [Link..] are only for mail merge, so we can simply rely on a single table that will be created only when ordering the Technical Sheet for the DOE..... Yes / No?   -- Best regards. The Penguin

nicodu072 · Answer

So, to answer your questions,  The table is therefore established for a client and you save it in an archive, and at the same time it is used to create the Technical Sheet for DOE. Then we start over with a new client....!  YES  If that's the case, then how many lines do you need per client.... That's the question..  Each document is variable. In fact, there is no fixed number of lines, it varies depending on the equipment we install for our clients. There can be 5 lines or 200 lines (hence my 2500 lines to ensure I have enough cells with formulas).  By the way, the 3 tables [Link..] are only there for the mail merge, so we can simply settle for a single table that will be established only during the order of the Technical Sheet for DOE..... Yes / No?  Yes, the 3 tables [Link...] are indeed only there for the mail merge. And it's true that it is possible to have a single table that combines the 3 tables [Equipment List...]. However, in this table, the air handling, hydraulics, and control should not be mixed, and in the case where I add equipment to the [Air Handling Equipment List] table, it must be positioned before the first Hydraulic equipment in the [Link] table.   So to summarize, my result is, as you said, to have 3 lists of equipment (Air Handling, Hydraulics, and Control) taking into account the line repetitions. The lists must be sorted from A to Z and then incremented using the formula you sent me in one of the previous resolved posts.  Then we create a mail merge file that includes these 3 lists without considering repetitions (duplicates).  Is this clear enough?  Best regards.

Le Pingou · Answer

Hello, I leave you to discover: https://www.cjoint.com/?3HrxnkXiTSF  -- Best regards. The Penguin

nicodu072 · Answer

Hello,  I don't know what to say other than BRAVO and a BIG THANK YOU for everything you have done for me. It works very well and meets exactly what I wanted.  Thank you again

Le Pingou · Answer

Thank you, you're welcome.  -- Regards. The Penguin

nicodu072 · Answer

Small issue, when I tested earlier, I didn't open the Word mail merge files, I just checked that they opened fine. Upon opening them, I noticed that when I do the mail merge on [Equipment List Aérau], it works, but then when I launch from [Equipment List Hydrau], the mail merge opens but the Word file corresponds to [Equipment List Aérau]. To check, I went to the [Mail Merge Link] sheet and the table updates as expected.  Is this a bug on my end? Because for the command "Sub MailMerge(nompubli, nombase, feuilbase, chemin)" you didn't change anything except the msgBox and it was working very well before.  Best regards.

Le Pingou · Answer

Hello, Having tested the application before sending it to you, I assure you that the 3 Word documents match. Can you explain to me what you have undertaken....¨ !  -- Best regards. The Penguin

Le Pingou · Answer

Hello, Here is a copy of the result of the application I sent: https://www.cjoint.com/?3Hsp5UORAqB  -- Best regards. The Penguin

Le Pingou · Answer

Hello, Just a little information regarding the sort A-Z and insert-line procedures, I have left your procedures in the [Module2] so that you can compare them with mine [TrierB_AZ] and [ajoutligformu]   -- Best regards. The Penguin

nicodu072 · Answer

Hello, so I extracted the zip into a new file on my desktop, I enabled Microsoft Word xx.0 Object Library, and now, when I click on the buttons for Mail Merge, it always stays on the first mail merge that comes from Regulation even though I click on the Aéraulique button. But just to remind you, the Lien_Publipostage table is indeed updated.  I don't understand because after trying and trying again, I delete the entire file and go back to your link to restart the procedure, and it's always the same.  Maybe something is still not activated in my Excel.

Le Pingou · Answer

Hello, Thank you for the video, it’s really well done (something I can’t do). Try again but without deleting the Word document...! -- Best regards. The Penguin

nicodu072 · Answer

Good evening, it doesn't change anything, please send me your files, there may have been a bug during compression or sending (hopefully).  For the video, it's here: https://www.commentcamarche.net/telecharger/photo-graphisme/5423-camstudio/

Le Pingou · Answer

Hello, You piqued my curiosity and I tried to make a video. Here is the first result http://dl.free.fr/getfile.pl?file=/FNBIEoKV   -- Regards. The Penguin

nicodu072 · Answer

Indeed, it works very well for you. I will have to ask you to come and work with us ;-). I see that you are working with Office 2010; I am working with 2007. Could this have an impact? Please remember to send me your file to review, thank you.  Best regards.

Le Pingou · Answer

Hello, In this case, a new provision of the file will not change anything. A breakpoint needs to be set in the procedure [MailMerge(nompubli, nombase, feuilbase, chemin)] at [DocWord.Close False] to see what is happening in the Word document.   -- Regards. The Penguin

nicodu072 · Answer

Hello, I have set the breakpoint as you asked me to. Everything goes well until the recipient selection in the mail merge, which remains on the RAx references (see video): https://www.cjoint.com/?BHtnAc7DtW7  I then created a breakpoint on the old file (from the end of the week without the mailmergewithoutduplicates) to see what was happening, and the recipient update is working correctly: https://www.cjoint.com/?BHtnEqWahX7  To test, I took the old Word document, where the procedure worked, and replaced it with the new one. Nothing changes, the update doesn’t happen either. So, we already know that it doesn’t come from the Word file. Now, why does it work on your end and not on mine! I tried on a computer running SEVEN (as it seems to me) and it works perfectly. I saved it and sent it back to my PC (Vista) and the same issue occurred. But this time the reference remains on Hx, which is the last mail merge launched before saving on the other PC.  So the problem might be related to the operating system and perhaps to the mailmergewithoutduplicates part? Strange!

Le Pingou · Answer

Hello, Thank you for the information. In any case, no part [mailingwithoutduplicates], I’m thinking rather of a missing piece of information in the code...! Is the version of MSO under SEVEN 2007 or 2010? For now, I haven't found anything. Patience because tomorrow I will test on another PC with MSO 2007 and maybe I will find the key to the problem.    Best regards. The Penguin

nicodu072 · Answer

Hello, So under SEVEN, the version used is MSO 2007. In this case, does that mean that there is a missing piece of information in the code for it to work under Vista? How is that possible? Because nowhere do you refer to an operating system. Best regards.

Le Pingou · Answer

Hello, Very interesting information. Please wait until tomorrow.  -- Best regards. The Penguin

Le Pingou · Answer

Hello, Well, my test with a PC running Windows Vista and MSO2007 revealed the same problems that you noticed. It seems that under Vista the UAC gets tangled? For now, I haven't found out why and therefore the fix. I am not losing hope of finding....!   -- Best regards. The Penguin

nicodu072 · Answer

Good evening, That reassures me. Don’t worry about it, at least don’t spend too much time on it for me, because I tested it at the office and it works on my PC running Windows Seven. I simply won’t be able to use it on my personal PC. However, thank you for your help. I am still improving the file. If you’re interested, I’ll attach it to you when I’m finished to get the expert’s opinion ;-).. Have a good evening. Best regards

Le Pingou · Answer

Hello, Thank you for conducting a test with this version: https://www.cjoint.com/?3Hvtly5XMhj   -- Best regards. The Penguin

nicodu072 · Answer

Good evening,  Congratulations!  The file works perfectly. I didn't take the time to understand all the code, but I noticed that you changed the variable type, specified the database opening in more detail, and created the automatic saving of files in the directory based on a predefined name.  Thank you again for your help.  Best regards.

Le Pingou · Answer

Hello, Glad to hear everything is good on your end. Actually, as always, when things are not working anymore, I rewrite the code again without considering the existing one, which is why you see a different way of writing. But the problem with Windows Vista is that it does not understand changes in the source file (Excel) unless they have been saved (unlike Windows 7). By the way, if you try the version with the 3 link sheets, you will notice that if you modify the data in one of the tables [Lists...], the Word documents will be incorrect. Regarding: the automatic saving of files in the directory based on a predefined name, this is to be able to control the result obtained in relation to the initial request (who goes with whom).   -- Best regards. The Penguin

nicodu072 · Answer

Good evening, Very well, thank you for this additional explanation. And thank you again for your work. Best regards.

Macro Issue Between Excel and Word

51 answers

No access to my ubisoft account

Thunderbird password connection not possible

Antivirus problem

Unable to scan

Wrong keystrokes

I wanted to turn on my windows pc but i lost my product key

Caution: redirects at spartoo (300 eur)

Latine reddere titulum.

How to change the email on my account how does it work

How to back out with cdiscount