As I'm trying my first Excel with macro's I could really use some help. I'm not a programmer but I can edit some code very well.
My goal is to generate some different word documents by the click of a button. The excel file is a list with achievements of students. The results are listed in the different word documents. It's kind of a mail merge but without opening Word.
The code I have now is for a button in the same sheet to generate those word documents. Now I changed the whole excel file...and I'm lost with the VBA. I know it has something to do with:
Sub Selecteren_Cijferlijst()
' Selecteren_Cijferlijst Macro
Sheets("Cijferlijst").Select
End Sub
The code I got from a kind user on a forum is this:
Option Explicit
Sub Vooraanmelding()
Dim lonLaatsteRij As Long
Dim rngData As Range
Dim strGeboortedatum As String, strStudentnummer As String, strVoornaam As String, strAchternaam As String, strAdres As String, strPostcode As String, strWoonplaats As String, strTelefoon As String, strEmail As String, strCrebo As String, strKlas As String, strProfiel As String, strSlber As String
Dim c As Range
With ActiveSheet
'bepaal de onderste rij van het actieve excel-werkblad
lonLaatsteRij = .Cells(Rows.Count, "A").End(xlUp).Row
'stel bereik in
Set rngData = .Range(.Cells(2, 1), .Cells(lonLaatsteRij, 1))
End With
For Each c In rngData
c.Select
strGeboortedatum = c.Offset(0, 7).Value
strStudentnummer = c.Offset(0, 2).Value
strVoornaam = c.Value
strAchternaam = c.Offset(0, 1).Value
strAdres = c.Offset(0, 4).Value
strPostcode = c.Offset(0, 5).Value
strWoonplaats = c.Offset(0, 6).Value
strTelefoon = c.Offset(0, 8).Value
strEmail = c.Offset(0, 9).Value
strCrebo = c.Offset(0, 10).Value
strKlas = c.Offset(0, 3).Value
strProfiel = c.Offset(0, 11).Value
strSlber = c.Offset(0, 12).Value
Call maakWordDocument(strGeboortedatum, strStudentnummer, strVoornaam,
strAchternaam, strAdres, strPostcode, strWoonplaats, strTelefoon, strEmail,
strCrebo, strKlas, strProfiel, strSlber)
Next c
End Sub
Private Sub maakWordDocument(strGeboortedatum As String, strStudentnummer As String, strVoornaam As String, strAchternaam As String, strAdres As String, strPostcode As String, strWoonplaats As String, strTelefoon As String, strEmail As String, strCrebo As String, strKlas As String, strProfiel As String, strSlber As String)
'maak een verwijzing naar de Microsoft Word 16.0 Object Library!!
Dim wordApp As Object, WordDoc As Object
On Error Resume Next
'kijk of word al open staat
Set wordApp = GetObject(, "Word.Application")
'open word
If wordApp Is Nothing Then
'If Not open, open Word Application
Set wordApp = CreateObject("Word.Application")
End If
'toon word (of niet, dan op false)
wordApp.Visible = False
'open het 'bron'-bestand
Set WordDoc = wordApp.Documents.Open(ThisWorkbook.Path & "Vooraanmelding\Vooraanmelding.docx")
'bladwijzers invullen
Call InvullenBladwijzer(wordApp, "geboortedatum", strGeboortedatum)
Call InvullenBladwijzer(wordApp, "studentnummer", strStudentnummer)
Call InvullenBladwijzer(wordApp, "voornaam", strVoornaam)
Call InvullenBladwijzer(wordApp, "achternaam", strAchternaam)
Call InvullenBladwijzer(wordApp, "adres", strAdres)
Call InvullenBladwijzer(wordApp, "postcode", strPostcode)
Call InvullenBladwijzer(wordApp, "woonplaats", strWoonplaats)
Call InvullenBladwijzer(wordApp, "telefoon", strTelefoon)
Call InvullenBladwijzer(wordApp, "email", strEmail)
Call InvullenBladwijzer(wordApp, "crebo", strCrebo)
Call InvullenBladwijzer(wordApp, "klas", strKlas)
Call InvullenBladwijzer(wordApp, "profiel", strProfiel)
Call InvullenBladwijzer(wordApp, "slber", strSlber)
'bestand opslaan en alles netjes afsluiten
wordApp.DisplayAlerts = False
WordDoc.SaveAs Filename:=ThisWorkbook.Path & "Vooraanmelding\Vooraanmelding " & strVoornaam & Space(1) & strAchternaam, FileFormat:=wdFormatDocument
WordDoc.Close
wordApp.Quit
Set WordDoc = Nothing
Set wordApp = Nothing
wordApp.DisplayAlerts = True
On Error GoTo 0
End Sub
Sub InvullenBladwijzer(wordApp As Object, strBladwijzer As String, strTekst As String)
'tekst invullen in relevante strBladwijzer
wordApp.Selection.Goto What:=wdGoToBookmark, Name:=strBladwijzer
wordApp.Selection.TypeText strTekst
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
This code is what someone gave me, it was a quick 'n' dirty solution for the file I had. Now I changed the setup of my excel so my colleagues also can work with it. That's why I decided to put all buttons on a separate sheet.
You need to directly qualify your range rngData with a sheet and not rely on ActiveSheet.
Delete first sub and link buttons to Sub Vooraanmelding
With Sheets("Cijferlijst")
lonLaatsteRij = .Cells(Rows.Count, "A").End(xlUp).Row
Set rngData = .Range(.Cells(2, 1), .Cells(lonLaatsteRij, 1))
End With