Excel/PDF offline automatisering tool

[barry86]

Beste mede tweakers,

Als disclaimer wil ik eerst aangeven dat ik geen programmeur ben, dus ik ben al vrij snel “overvraagd” door onwetendheid. Ik sta open voor commentaar en/of ik hier wel in de juiste sub-fora ben! 😊

Ik ben op zoek naar een offline automatisering tool van het volgende:

Ik heb een excel bestand met 2 tabladen.
Tablad 1 bevat 22 kolommen en (tien)duizenden rijen aan data en die de data is uniek.
Tablad 2 bevat een A4 layout met info en 22 velden die worden ingevuld.
2 velden daarvan worden handmatig ingevuld en via verticaal zoeken worden de overige 20 velden uit tabblad 1 opgehaald.
Vervolgens wordt dit resultaat als PDF bestand opgeslagen met als bestandsnaam de 2 handmatig ingevulde velden.

Dit process gebeurt 100tal keren per week en neemt toe.
De vraag is, of er een soort van klant en klare tool bestaat die dit proces kan automatiseren?

De bedoeling is dat elke rij van tablad 1 moet worden opgeslagen in een PDF bestand middels de layout van tabblad 2.
Ik ben druk aan het googlen geweest maar kom uit op de standaard "excel naar PDF generator" en diverse zijtak aanbieders. Ik vermoed dat ik niet de juiste terminologieren gebruik oid.

Ik hoop dat ik zo informatief mogelijk ben geweest!? Ik ben benieuwd!

[g0tanks]

Dit is een redelijk specifieke handeling, dus ik verwacht niet dat er een kant-en-klare tool voor bestaat. Daarentegen is het niet bijzonder complex, dus zou je zelf een macro kunnen schrijven in Excel (met VBA). Dat laatste vereist helaas wel enige programmeerkennis.

[Lustucru]

De bedoeling is dat elke rij van tablad 1 moet worden opgeslagen in een PDF bestand middels de layout van tabblad 2.

Zoals TS het beschrijft is het een simpel gevalletje mailmerge. Tabblad 2 vervangen door een word-document en klaar.

[NESFreak]

Het A4tje omzetten naar word, en met een mailmerge invullen?

[barry86]

Perfect! ik kende mail merge niet. Zie je maar weer dat je gewoon even op het goede spoor gezet moet worden

Ik heb tabblad 2 naar een word document omgezet en de excel tabblad 1 gekoppeld.
Dit werkt mooi!

Alleen bij het opslaan worden alle regels samengevoegd tot 1 pdf document.
Ik moet van elke regel een afzonderlijk PDF document hebben en met de tekst uit 1 van de regels.
Gaat die via macro schrijven? Of kan dit ook via andere wegen?

[m-vw]

Heb even word niet bij de hand, maar kan je onder pdf opties niet kiezen voor 1 pagina per pdf?

[NESFreak]

barry86 schreef op dinsdag 14 mei 2024 @ 14:32:
Perfect! ik kende mail merge niet. Zie je maar weer dat je gewoon even op het goede spoor gezet moet worden

Ik heb tabblad 2 naar een word document omgezet en de excel tabblad 1 gekoppeld.
Dit werkt mooi!

Alleen bij het opslaan worden alle regels samengevoegd tot 1 pdf document.
Ik moet van elke regel een afzonderlijk PDF document hebben en met de tekst uit 1 van de regels.
Gaat die via macro schrijven? Of kan dit ook via andere wegen?

De eerste bruikbare link in google geeft je een macro wat dit zou kunnen doen. Ik heb het zelf nog niet getest, maar misschien heb je er wat aan. Er lijkt op het eerste gezicht geen dubieuze commandos in te zitten (maar vertrouw nooit blind macros die je van een random gast op het internet krijgt )

https://imnoss.com/word-mail-merge-to-separate-pdfs/

The Super Quick Version

• Copy the text of the Macro from below and add to Word.
• Add the following fields to your Mail Merge data: DocFolderPath, DocFileName, PdfFolderPath, PdfFileName.
• In the FileName fields, put the file names you want for the separated Word Docs and converted PDFs. No need to include the extensions.
• In the FolderPath fields put valid folder paths (e.g. C:\Users\me\Documents). These can be the same for all rows, or different – the choice is yours.
• Create your Mail Merge.
• Run the Macro
• Sit back and let the computer work.

code:

Sub MailMergeToPdfBasic()                                                        ' Mark the start of the Subroutine (i.e. Macro) and name it "MailMergeToPdf"
' Macro created by Imnoss Ltd
' Please share freely while retaining attribution
' Last Updated 2021-05-03
    Dim masterDoc As Document, singleDoc As Document, lastRecordNum As Long   ' Create variables ("Post-it Notes") for later use
    Set masterDoc = ActiveDocument                                               ' Identify the ActiveDocument (foremost doc when Macro run) as "masterDoc"

    masterDoc.MailMerge.DataSource.ActiveRecord = wdLastRecord                   ' jump to the last active record (active = ticked in edit recipients)
    lastRecordNum = masterDoc.MailMerge.DataSource.ActiveRecord                  ' retrieve the record number of the last active record so we know when to stop

    masterDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord                  ' jump to the first active record (active = ticked in edit recipients)
    Do While lastRecordNum > 0                                                   ' create a loop, lastRecordNum is used to end the loop by setting to zero (see below)
        masterDoc.MailMerge.Destination = wdSendToNewDocument                    ' Identify that we are creating a word docx (and no e.g. an email)
        masterDoc.MailMerge.DataSource.FirstRecord = masterDoc.MailMerge.DataSource.ActiveRecord              ' Limit the selection to just one document by setting the start ...
        masterDoc.MailMerge.DataSource.LastRecord = masterDoc.MailMerge.DataSource.ActiveRecord               ' ... and end points to the active record
        masterDoc.MailMerge.Execute False                                        ' run the MailMerge based on the above settings (i.e. for one record)
        Set singleDoc = ActiveDocument                                           ' Identify the ActiveDocument (foremost doc after running the MailMerge) as "singleDoc"
        singleDoc.SaveAs2 _
            FileName:=masterDoc.MailMerge.DataSource.DataFields("DocFolderPath").Value & Application.PathSeparator & _
                masterDoc.MailMerge.DataSource.DataFields("DocFileName").Value & ".docx", _
            FileFormat:=wdFormatXMLDocument                                      ' Save "singleDoc" as a word docx with the details provided in the DocFolderPath and DocFileName fields in the MailMerge data
        singleDoc.ExportAsFixedFormat _
            OutputFileName:=masterDoc.MailMerge.DataSource.DataFields("PdfFolderPath").Value & Application.PathSeparator & _
                masterDoc.MailMerge.DataSource.DataFields("PdfFileName").Value & ".pdf", _
            ExportFormat:=wdExportFormatPDF                                      ' Export "singleDoc" as a PDF with the details provided in the PdfFolderPath and PdfFileName fields in the MailMerge data
        singleDoc.Close False                                                    ' Close "singleDoc", the variable "singleDoc" can now be used for the next record when created
        If masterDoc.MailMerge.DataSource.ActiveRecord >= lastRecordNum Then     ' test if we have just created a document for the last record
            lastRecordNum = 0                                                    ' if so we set lastRecordNum to zero to indicate that the loop should end
        Else
            masterDoc.MailMerge.DataSource.ActiveRecord = wdNextRecord           ' otherwise go to the next active record
        End If

    Loop                                                                         ' loop back to the Do start
End Sub                                                                          ' Mark the end of the Subroutine

[tinytinnie]

Wat ga je doen met die PDF's? Als de volgende stap is ze te mailen zou ik eens kijken naar de tool van Graham Mayor. Many to One is een tijdje uitzoeken totdat je zijn logica snapt maar het heeft me al ontzettend veel tijd bespaard: individuele PDF's mailen. De tool kan ook doen wat jij wilt maar is daarvoor overkill, dan is de macro hierboven veel simpeler.

[barry86]

NESFreak schreef op dinsdag 14 mei 2024 @ 15:02:
[...]


De eerste bruikbare link in google geeft je een macro wat dit zou kunnen doen. Ik heb het zelf nog niet getest, maar misschien heb je er wat aan. Er lijkt op het eerste gezicht geen dubieuze commandos in te zitten (maar vertrouw nooit blind macros die je van een random gast op het internet krijgt )

https://imnoss.com/word-mail-merge-to-separate-pdfs/

[...]

code:

Sub MailMergeToPdfBasic()                                                        ' Mark the start of the Subroutine (i.e. Macro) and name it "MailMergeToPdf"
' Macro created by Imnoss Ltd
' Please share freely while retaining attribution
' Last Updated 2021-05-03
    Dim masterDoc As Document, singleDoc As Document, lastRecordNum As Long   ' Create variables ("Post-it Notes") for later use
    Set masterDoc = ActiveDocument                                               ' Identify the ActiveDocument (foremost doc when Macro run) as "masterDoc"

    masterDoc.MailMerge.DataSource.ActiveRecord = wdLastRecord                   ' jump to the last active record (active = ticked in edit recipients)
    lastRecordNum = masterDoc.MailMerge.DataSource.ActiveRecord                  ' retrieve the record number of the last active record so we know when to stop

    masterDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord                  ' jump to the first active record (active = ticked in edit recipients)
    Do While lastRecordNum > 0                                                   ' create a loop, lastRecordNum is used to end the loop by setting to zero (see below)
        masterDoc.MailMerge.Destination = wdSendToNewDocument                    ' Identify that we are creating a word docx (and no e.g. an email)
        masterDoc.MailMerge.DataSource.FirstRecord = masterDoc.MailMerge.DataSource.ActiveRecord              ' Limit the selection to just one document by setting the start ...
        masterDoc.MailMerge.DataSource.LastRecord = masterDoc.MailMerge.DataSource.ActiveRecord               ' ... and end points to the active record
        masterDoc.MailMerge.Execute False                                        ' run the MailMerge based on the above settings (i.e. for one record)
        Set singleDoc = ActiveDocument                                           ' Identify the ActiveDocument (foremost doc after running the MailMerge) as "singleDoc"
        singleDoc.SaveAs2 _
            FileName:=masterDoc.MailMerge.DataSource.DataFields("DocFolderPath").Value & Application.PathSeparator & _
                masterDoc.MailMerge.DataSource.DataFields("DocFileName").Value & ".docx", _
            FileFormat:=wdFormatXMLDocument                                      ' Save "singleDoc" as a word docx with the details provided in the DocFolderPath and DocFileName fields in the MailMerge data
        singleDoc.ExportAsFixedFormat _
            OutputFileName:=masterDoc.MailMerge.DataSource.DataFields("PdfFolderPath").Value & Application.PathSeparator & _
                masterDoc.MailMerge.DataSource.DataFields("PdfFileName").Value & ".pdf", _
            ExportFormat:=wdExportFormatPDF                                      ' Export "singleDoc" as a PDF with the details provided in the PdfFolderPath and PdfFileName fields in the MailMerge data
        singleDoc.Close False                                                    ' Close "singleDoc", the variable "singleDoc" can now be used for the next record when created
        If masterDoc.MailMerge.DataSource.ActiveRecord >= lastRecordNum Then     ' test if we have just created a document for the last record
            lastRecordNum = 0                                                    ' if so we set lastRecordNum to zero to indicate that the loop should end
        Else
            masterDoc.MailMerge.DataSource.ActiveRecord = wdNextRecord           ' otherwise go to the next active record
        End If

    Loop                                                                         ' loop back to the Do start
End Sub                                                                          ' Mark the end of the Subroutine

Ook dit heb ik werkend, perfect joh! Dit gaat zoveel tijd schelen!

[barry86]

tinytinnie schreef op dinsdag 14 mei 2024 @ 15:35:
Wat ga je doen met die PDF's? Als de volgende stap is ze te mailen zou ik eens kijken naar de tool van Graham Mayor. Many to One is een tijdje uitzoeken totdat je zijn logica snapt maar het heeft me al ontzettend veel tijd bespaard: individuele PDF's mailen. De tool kan ook doen wat jij wilt maar is daarvoor overkill, dan is de macro hierboven veel simpeler.

Inderdaad, een volgende stap is dat wij een specifiek PDF bestand hebben waarop samenvattend staat welke bijbehorende PDF'en naar de klant gestuurd worden.
Als er een programma / macro bestaat waarbij de samenvattende PDF ingelezen kan worden en daarbij de bijbehorende PDF'en verzameld dan zijn we weer een heeeeel stap verder in efficient werken!

[tinytinnie]

De bedoeling van de tool is precies dat: Samenvoegen van mailings met voor iedereen dezelfde PDF (je leveringsvoorwaarden bv) en de individuele PDF (waarvan de bestandsnaam in de samenvoeger broninfo, dus in excel staat).

De tool is wel wat lastig om goed te begrijpen, maar met goed lezen en veel proberen kom je eruit. Hij is minder flexibel dan Word in filteren wat hij uit de excel haalt, meestal werk ik het snelst met degenen die ik nog moet mailen even op een ander tabblad zetten en dat tabblad als bron gebruiken, dat gaat sneller dan uitzoeken welke filters wel en welke niet werken. Maar succes met proberen!

[barry86]

Dank je, hier ga ik mij zeker in verdiepen.

Andere vraag, ik zit nog met massa oude data die ik ook graag geautomatiseerd wil laten lopen.
Echter staat het excel bestand nog niet correct en moet ik de volgende handeling uitvoeren


10x knippen plakken is geen probleem. Maar ik heb +10.000 rijen te gaan.
Excel moet dit toch ook "automatisch" kunnen? Iemand tips?

En uiteindelijk moeten de dubbele rijen verwijderd worden

[F_J_K]

Cel linksboven, shift inhouden, cel rechtsonder, knip, pijltje omhoog, plak? Of veel makkelijker: selecteer alleen de vier leeg cellen, rechtermuisknop, verwijderen. Dan wordt de vraag gesteld wat er met de data moet gebeuren: cellen naar boven verplaatsen.

[barry86]

Dat snap ik nog , maar dat 10.000keer handmatig doen moet vast eenvoudiger kunnen. Ik google ook even door, maar je verzuipt snel!

[dix-neuf]

barry,
Als ik je goed begrijp staan er lege rijen her en der over het bestand verspreid.
Zijn die rijen allemaal volledig leeg of is (zoals in je afbeelding) kolom A steeds niet-leeg ?

[SinergyX]

barry86 schreef op woensdag 15 mei 2024 @ 12:20:
Dat snap ik nog , maar dat 10.000keer handmatig doen moet vast eenvoudiger kunnen. Ik google ook even door, maar je verzuipt snel!

Kan je daar niet beter de FILTER functie voor gebruiken? Heb je wel een recente versie voor nodig (dacht een nieuwe functie sinds 2022), maar deze 'programmeer' je in 1 cel en wordt dynamisch aangepast als aparte selectie van je blad. En voor dubbele waarden de functie UNIEK (Office 365 functie).

[ Voor 6% gewijzigd door SinergyX op 15-05-2024 15:26 ]

[F_J_K]

barry86 schreef op woensdag 15 mei 2024 @ 12:20:
Dat snap ik nog , maar dat 10.000keer handmatig doen moet vast eenvoudiger kunnen. Ik google ook even door, maar je verzuipt snel!

Ah, ik dacht 1x een gat In welke mate is het misplaatst zijn van de rijen / kolommen voorspelbaar? Zit er een patroon in, bv elke 500 rijen gaat het een rij mis?

offtopic:
Hoe ontstaan dat soort gaten?

[barry86]

dix-neuf schreef op woensdag 15 mei 2024 @ 13:02:
barry,
Als ik je goed begrijp staan er lege rijen her en der over het bestand verspreid.
Zijn die rijen allemaal volledig leeg of is (zoals in je afbeelding) kolom A steeds niet-leeg ?

Correct. zoals in het voorbeeld. zijn pos 2+3+4+5 niet gevuld in de eerste regel. Dit moet wel.
En dit dan vele malen in het excel bestand.


Hoe kom ik hier op?
Ik heb 2 excel tabbladen naar 1 tabblad gekopieerd om mailtomerge zijn werk te laten doen.
Echter werkt mail to merge niet goed met onderstaande voorbeeld.

[barry86]

F_J_K schreef op woensdag 15 mei 2024 @ 16:09:
[...]

Ah, ik dacht 1x een gat In welke mate is het misplaatst zijn van de rijen / kolommen voorspelbaar? Zit er een patroon in, bv elke 500 rijen gaat het een rij mis?

offtopic:
Hoe ontstaan dat soort gaten?

Nee niet voorspelbaar. Soms bestaat bovengenoemd voorbeeld uit 3 rijen, soms uit 8 rijden, soms uit 5 rijen, etcetera.
Maar elke keer is de bovenste rij leeg op pos2+3+4+5, wat dus gevuld moet worden voor "mail to merge"

[dix-neuf]

barry86 schreef op donderdag 16 mei 2024 @ 08:41:
Correct. zoals in het voorbeeld. zijn pos 2+3+4+5 niet gevuld in de eerste regel. Dit moet wel.
En dit dan vele malen in het excel bestand.

Met onderstaande macro kun je alle rijen verwijderen waarin de B t/m E-kolommen niet zijn ingevuld.
Er is vanuitgegaan dat de gegevens in het eerste blad van je bestand staan.

code:

Sub Verwijder_rijen()
Dim a As Integer, r As Integer, x As Integer
With Sheets(1)
r = .Cells(.Rows.Count, 1).End(xlUp).Row
Do Until r = 1
a = 0
For x = 2 To 5
If IsEmpty(.Cells(r, x)) Then
a = a + 1
End If
Next x
If a = 4 Then
.Rows(r).EntireRow.Delete
a = 0
End If
r = r - 1
Loop
End With
End Sub

[ Voor 7% gewijzigd door dix-neuf op 16-05-2024 08:55 ]

[barry86]

Tsja... waarom niet direct? maar hierbij het "live" voorbeeld.

Stap1:
De bedoeling is om de kolomen met data in de groen/rood/blauwe arcering in de rijen te krijgen met dezelfde code als in kolom1.
Dit herhaalt zich +10.000rijen en zoals je ziet is er een variatie in aantal rijen. Soms 1 is het 1, soms zijn het wel 20 rijen.

Stap2: Is om de paarse rijen te verwijderen. Maar deze stap lijkt mij minder complex

Ik kom op youtube leerzame tips tegen, maar stap 1 krijg ik nog niet gevonden

[ Voor 7% gewijzigd door barry86 op 16-05-2024 09:10 ]

[dix-neuf]

barry86 schreef op donderdag 16 mei 2024 @ 09:07:
Tsja... waarom niet direct?

Wat bedoel je daar mee?

[barry86]

Dat ik niet direct het voorbeeld deel waar het om draait! Dat is veel duidelijker dan mijn eerste vraag. Dus het was een opmerking naar mij zelf 😉

[dix-neuf]

Als je tabel (net zoals hierboven) er zo uitziet:


dan ziet die er na het uitvoeren van deze macro:

code:

Sub macro1()
Dim x As Long, r As Long
With Sheets(1)
Application.ScreenUpdating = False
x = 2
Do Until x > .Cells(Rows.Count, 1).End(xlUp).Row
If IsEmpty(.Cells(x, 6)) = False Then
r = x
While .Cells(x + 1, 2) = .Cells(x, 2)
.Range(.Cells(x, 6), .Cells(x, 8)).Copy .Range(.Cells(x + 1, 6), .Cells(x + 1, 8))
x = x + 1
Wend
.Rows(r).EntireRow.Delete
x = x - 1
End If
x = x + 1
Loop
Application.ScreenUpdating = True
End With
End Sub

zo uit:


en dan heb je wat je wou, denk ik.
Aanname is dat de gegevens in het eerste blad staan.

[dix-neuf]

En toen hoorden we van barry86 niets meer....

[CH4OS]

dix-neuf schreef op maandag 20 mei 2024 @ 15:11:
En toen hoorden we van barry86 niets meer....

Misschien omdat het weekend is en Tweede Pinksterdag?

[dix-neuf]

CH4OS schreef op maandag 20 mei 2024 @ 15:27:
Misschien omdat het weekend is en Tweede Pinksterdag?

Zou kunnen, maar feit blijft dat mensen meestal wel tijd hebben om een vraag te stellen, maar niet altijd als redelijkerwijs een antwoord verwacht mag worden. Een vraag behoor je als vragensteller op te volgen (vind ik).

[CH4OS]

Er kunnen genoeg redenen zijn waarom iemand even een paar dagen er niet is, dat betekend toch ook niet automatisch dat er geen opvolging meer komt?

[barry86]

Ik begrijp de de reactie van @dix-neuf wel. Hij heeft er tijd in gestopt en mij tools gegeven en daarna geen reactie meer...
Als je op mij profiel kijkt zie je dat ik wel een actieve user ben. Dus een reactie komt wel.
Maar dat is ook de hedendaagse tijd, geduld bestaat niet meer.

Ik probeer het de komende dagen weer op te pakken!

[ Voor 11% gewijzigd door barry86 op 21-05-2024 08:07 ]