0

I made a nice Excel file with several (almost the same) macros. Goal is to fill open a Word template, fill in the bookmarks and save every individual document with predefined fields in the filename. Works like a charm... but it doesn't go further then the 10th row of my Excel file. All 12 macros have the same issue, basically the macro is the same only the fields are different.

The VBA I have now is this:

Option Explicit

Sub Akkoordverklaring()
    Dim lonLaatsteRij As Long
    Dim rngData As Range
    Dim strVoornaam As String, strAchternaam As String, strSlber As String
    Dim c As Range
    With Sheets("Cijferlijst")
        lonLaatsteRij = .Cells(Rows.Count, "A").End(xlUp).Row
        Set rngData = .Range(.Cells(2, 1), .Cells(lonLaatsteRij, 1))
    End With
    For Each c In rngData
      strVoornaam = c.Value
      strAchternaam = c.Offset(0, 1).Value
      strSlber = c.Offset(0, 12).Value
      Call maakWordDocument(strVoornaam, strAchternaam, strSlber)
    Next c
End Sub

Private Sub maakWordDocument(strVoornaam As String, strAchternaam As String, _
strSlber As String)    
    Dim wordApp As Object, WordDoc As Object
    On Error Resume Next
    Set wordApp = GetObject("", "Word.Application")
    If wordApp Is Nothing Then
      Set wordApp = CreateObject("Word.Application")
    End If
    wordApp.Visible = False
    Set WordDoc = wordApp.Documents.Open(ThisWorkbook.Path & _
        "Formulieren\Akkoordverklaring.docx")
    Call InvullenBladwijzer(wordApp, "voornaam", strVoornaam)
    Call InvullenBladwijzer(wordApp, "achternaam", strAchternaam)
    Call InvullenBladwijzer(wordApp, "slber", strSlber)
    wordApp.DisplayAlerts = False
    WordDoc.SaveAs Filename:=ThisWorkbook.Path & _
        "Akkoordverklaring\Akkoordverklaring " & strVoornaam & Space(1) & _
        strAchternaam, FileFormat:=wdFormatDocumentDefault
    WordDoc.Close
    wordApp.Quit
    Set WordDoc = Nothing
    Set wordApp = Nothing
End Sub

Sub InvullenBladwijzer(wordApp As Object, strBladwijzer As String, _
strTekst As String)
    wordApp.Selection.Goto What:=wdGoToBookmark, Name:=strBladwijzer
    wordApp.Selection.TypeText strTekst
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

As I'm not a programmer I just know how to read some VBA. Some users in here also helped me out with the VBA above:

Excel: change VBA action frome same sheet to another sheet

Toni
  • 1,555
  • 4
  • 15
  • 23
Sypie
  • 15
  • 1
  • 1
  • 6
  • can you post a data sample of the Excel sheet? If you step through the code, what is the initial value of `lonLaatsteRij` ? Do you have empty rows? – teylyn Aug 29 '18 at 09:07
  • For this macro it's this data: Column A: surname (voornaam) Column B: last name (achternam) Column M: mentor (SLB'er in Dutch) There are no empty rows between the first and last row. There are also no special characters in these data. Due to privacy I can't post my data. – Sypie Aug 29 '18 at 09:08
  • The initial value of lonLaatsteRij: I don't understand exactly what you mean by this. My data starts at row 2 and ends (in this file) on row 14. In another file (as teachers we have a file for every class) there might be even more rows. – Sypie Aug 29 '18 at 09:15
  • Just before the `End Sub` in your first sub routine (Akkoordverklaring), add the following code `MsgBox lonLaatsteRij` run the sub routine and it will display a message box with the number of rows it has counted with value data. – 5202456 Aug 29 '18 at 10:16
  • @5202456: I did. Thanks, that also works nice as a confirmation of what is done. – Sypie Aug 29 '18 at 10:41
  • 1
    I solved it. It was just stupid of me. I have like 10 sheets visible and 2 sheets invisible. One of the invisible sheets gathers information from all the sheets. In this way my VBA only has to look into 1 sheet. But... in this invisible sheet my formulas, to get data from other sheets, didn't go further then row 10... So copying the formula further was my solution to this issue. Just stupid of me. – Sypie Aug 29 '18 at 10:53
  • I am glad you have sorted the issue. An oversight when working on complex worksheet is not you being stupid, many of us have had oversights like this. It may be possible to solve the issue in the future by checking how many rows of formula are on your hidden sheet and if not enough exist, then use vba to create the extra rows of formulas. – 5202456 Aug 29 '18 at 11:41

0 Answers0