Macro Issue Between Excel and Word
Solved
nicodu072
Posted messages
46
Status
Member
-
nicodu072 Posted messages 46 Status Member -
nicodu072 Posted messages 46 Status Member -
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
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
- 1
- 2
- 3
Next
Hello,
Could you please provide me with the exact error message...?
Thank you.
--
Best regards.
The Penguin
Could you please provide me with the exact error message...?
Thank you.
--
Best regards.
The Penguin
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
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
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.
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.
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
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
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.
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.
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
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
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:
--
Regards.
Le Pingou
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
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 ;-).
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 ;-).
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
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
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
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
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.
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.
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
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
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.
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.
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
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
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.
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.
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
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
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?
Attached are the error messages: https://www.cjoint.com/?BHpxIx8llcJ
Could it be related to a path issue?
- 1
- 2
- 3
Next
Please note that the files I sent you yesterday need to be different because I completed/created the program today.