save word document as name in excel distribution list

[Madelien]

Hi All,

I'm a bit embarrassed to say that I am a newbie to VBA.

I did some experimenting to adjust a macro I found on the internet to work for me, but so far I get some errors I don't know how to fix.

So, What do I want the macro to do for me? I have an excelfile that serves as a distribution list for a word document (both are attached).
Thinks like customer name, date serious occupational accident (SOA), victim name, (sorry the file itself is in dutch) are copied into the word document. The footer of the word document is compiled of all these things and some text: EAO «datum_EAO»«slachtoffer» «Klant_» «dossiernr» - Luik A or in English: SOA «date_SOA»«victim» «Customer_» «dossier nr» - Part A
Now I would like to save my word document as a pdf with that footer as the document name.
I have already inserted the macro in the word document, but I get an error that is related to .datasource.

Bezoeken 2017 EAO MadelienMues.xlsxEAO datum bedrijf slachtoffer Luik A.docm

Who can please help me? Thank you very much in advance

[gmayor]

There is nothing to be embarrassed about being new to VBA. However, the Excel file is broken, and although the document can be opened, there are no macros in it so it is difficult to see what you are trying to do.

The macro is almost certainly in your normal template. However reading between the lines and with minimal understanding of the Dutch language, this appears to be a mail merge and you want to split it into separate files by records and name each file from the record itself, saving as PDF. You would need a macro for that, but to avoid re-inventing the wheel, save your document as a non-macro enabled DOCX format document and then use either of the following links to split the document to PDF files. The first link includes some code should you want to roll your own. The second may be necessary with the particular document. http://www.gmayor.com/individual_merge_letters.htm or http://www.gmayor.com/MergeAndSplit.htm

[Madelien]

Hello Gmayor,

thanks for your reply. I'm adding the excel again, something got broken indeed, I hope it works now.

The macro should be in the word document. At least that is what I tried to do.

In fact, I want to use input data from the excel and use as the word document's name. So for expamle, I'm working on one case of occupational accident, Mr X got hurt on date ABC, he is an employee of Client Y with 'client number 123. This info comes from the excel file and is copied into the word doc. I want to save the word document as 'SOA date ABC Mr X Client Y 123' without the need to type it manually and make mistakes (SOA stands for Serious Occupation Accident). If it's not possible to get the macro to find the info from the xls file itself, it can be get from the word doc (it is in the footer). The Client Y get's my report as a pdf. So I don't need to split the word doc.

Bezoeken 2017 EAO MadelienMues.xlsx

Thanks for your patience!

[macropod]

What you're describing is a mailmerge, where you want each record output to a new file. For the mailmerge basics, see:
https://support.office.com/en-us/art...f-932c49474705
Once you've done the basic setup, you can use the code in one of the links Graham provided. Alternatively, see Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks thread at:
http://www.msofficeforums.com/mail-m...ps-tricks.html
or:
http://windowssecrets.com/forums/sho...ips-amp-Tricks

[Madelien]

Damn, I feel stupid... maybe I'm missing some fundamentals to understand.
But let's not loose hope
The connection with excel is working well, necessary data is copied into word doc. So now I need to figure out how I can save the word doc as text that is already in the word doc.
When looking at the code to send mailmerge output to individual files, it seems like a copy of the word doc is made and then saved? I don't need to first make a copy, or is that a silly remark? I directly want to do a saveas, and have the text in the footer of the document to be used as the name. I don't see in the code where I can adapt the name used to save the document...

please have a little more patience with me

[Madelien]

In the word doc there is a macro that looks like this (I found it on the internet)... However, I can't get it to work, the error is on the .datasours (see bold below). I adjusted the things in red, to fit for my purpose

Sub SaveEachDocument()
Dim x As String
With Application
.DisplayAlerts = False
.ScreenUpdating = False
x = "G:\Risk\Veiligheid\Niveau_1's\Madelien Mues\admin\EAO datum bedrijf slachtoffer Luik A.doc"
With ActiveDocument
.MailMerge.DataSource
.Saveas x & "EAO " & Format(.DataFields("datum_EAO"), "dd-MM-yyyy") & " " & .DataFields("slachtoffer") & " " & .DataFields("Klant_") & " " & .DataFields("dossiernr") & "Luik B "
.ActiveRecord = 1
End With
.DisplayAlerts = True
End With
End Sub

[macropod]

Might I suggest you limit your creativity to the edits indicated in the link itself?...

[Madelien]

---Quote (Originally by macropod)---
Might I suggest you limit your creativity to the editsindicated in the link itself?...
---End Quote---

hmm, it seems your I think your answer could use a bitmore creativity... Sometimes there are more solutions to one problem. And infact, I tried the macro you suggested, but it doesn't do what I want. Did youeven look at the macro?And are you sureyou understand what I want it to do? It's not always easy to explain
***************

Graham,

I have downloaded the Add-ins you suggested, thanks. However when running them I get an error message saying the current document is not the correct type? I had saved it as a non-macro enabled docx, like you said.

[gmayor]

This is mail merge. You need to attach the data source to the document from the mailings tab of the ribbon in order to use the add-in. The document needs to be a 'letters' merge document.

[Madelien]

Thanks Graham, it works. This addin has a lot of flexibility and is great for large numbers of files.
In my case, the name should always consist of the same input from excel, and I always process 1 file at a time.

Do you mind taking a look at the macro from a bit higher, I tried to adjust it to work for me too?
This was the original, it's validated by someone else:

 
Sub SaveEachDocument()
Dim x As String
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        x = "C:\Users\mijnnaam"
        With ActiveDocument
            .MailMerge.DataSource
            .SaveAs x & "Factuur " & .DataFields("NAAM") & " " & .DataFields("FACTUURNR") & Format(.DataFields("BEHANDELD"), "dd-MM-yyyy")
            .ActiveRecord = 1
        End With
        .DisplayAlerts = True
    End With
End Sub

[macropod]

You seem determined to ignore the advice you've been given and prefer to foist your own incompetent code on the forum for us to fix. Your supposedly 'validated' code is garbage; it cannot do the job and would require a complete re-write along the lines of what you've been provided before it would have a hope of doing so.

[Madelien]

Such a sharp tongue, there is no need to be rude, Paul. But I understand that that is an easy path, although I expected more from a Guru
I'm not ignoring advice, I'm trying to take these pieces out of all the information where I, yes with my limited knowledge of VBA, can work with. So don't be too quick to take offence and judge.

Anyway, I had tried the Add-ins of Graham but it since I don't process large amounts of files and since I always need the same data from the excel file, it is a bit overkill for me and it is faster to type it manually.
I'm trying to adjust the code you sent in one of the links 'Send Mailmerge Output to Individual Files', hopefully it will give an output closest to what I prefer.
And although not very elegantly put, of course it is waste of time to try to make something good out of a bad code, so thanks for that input. And I confess it is appealing to use this macro, because, (and that was before I knew it was rubbish) I seems straightforward and I can understand what it is supposed to do.

[macropod]

Such a sharp tongue, there is no need to be rude, Paul.

After this little gem:

Did youeven look at the macro?And are you sureyou understand what I want it to do? It's not always easy to explain

who are you to accuse others of rudeness???

Be very careful about how you respond.

[Madelien]

when I said I understand it is an easy path, I was also talking about myself. I realised that comment was an impulsive reaction to what I perceived as a blunt comment. That why I changed it again.

I think that is the best lesson, we should all be careful on how to respond.

Could we please end this discussion, we are both waisting time we can use for something else. I'm sorry if I offended you.