VBA code error help, Issue with a loop. Issue with saving as pdfs with constantly changing names.

[jimmyismyname]

Hello everyone, VBA newbie here. I didnt write this code but Im attempting fix some errors.
Whats supposed to happen is that a loop that is supposed to iterate over certain rows and create subsequent word and pdf files related to isnt working.
The debugger gives an error at the part which creates the pdf files.

Any help would be greatly appreciated

Thanks!


Sub WordFindAndReplace()

Dim CA As Worksheet
Dim ws As Worksheet
Dim objWord As Object
Dim wDoc As Object
Dim ISIN As String
Dim name As String
Dim lastrow As Integer
Dim Path As String
Path = ThisWorkbook.Path

Set CA = ThisWorkbook.Worksheets("Corporate Action")

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.ScreenUpdating = False




'Template file
Set wDoc = objWord.Documents.Open(ActiveWorkbook.Path & "\Temps\Corporate Action.docx")

lastrow = CA.Cells(Rows.count, "A").End(xlUp).Row

For iter_row = 3 To lastrow

name = Split(CA.Range("J" & iter_row).Text, " ")(0)
ISIN = Range("A" & iter_row).Text

'Set the required values as Custom Document Properties
'Can be found in the word template. File => Info => Properties => Advanced Properties => Custom
With wDoc
.CustomDocumentProperties("ISIN").Value = ISIN 'ISIN
.CustomDocumentProperties("Seriennummer").Value = Range("B" & iter_row).Text 'Seriennummer
.CustomDocumentProperties("Produkt").Value = Range("C" & iter_row).Text 'Produkt
.CustomDocumentProperties("Basispreis neu").Value = Range("E" & iter_row).Text 'Basispreis neu
.CustomDocumentProperties("Barrier Level neu").Value = Range("F" & iter_row).Text 'Barrier Level neu
.CustomDocumentProperties("Bezugsverhältnis neu").Value = Range("I" & iter_row).Text 'Bezugsverhältnis neu
.Fields.Update

'Save as word document
.SaveAs Filename:=Path & "\Dokumente\" & ISIN & "_CA_Announcement_" & name & ".docx", FileFormat:=wdFormatDocumentDefault

'Export as PDF
'.ExportAsFixedFormat OutputFileName:=Path & "\Dokumente\" & ISIN & "_CA_Announcement_" & name & ".pdf", _
'ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
'Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False

End With
Application.StatusBar = "Finished creating docs for " & iter_row - 2 & " ISIN out of " & lastrow - 2
Next iter_row

'Close Word
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
objWord.Quit
Set objWord = Nothing


MsgBox ("Exported " & CStr(iter_row - 3) & " files as both .pdf and .doc in /Dokumente/")

 

[John_w]

What is the error?

Does the code have a reference to Microsoft Word xx.0 Object Library? Verify this via Tools - References in the VBA editor. Without that, none of the Word constants such as wdExportDocumentContent are defined, so the PDF export will fail.

Also, do you have the line Option Explicit at the top of the module? That forces you to define all variables, either with Dim statements or with library references where constants are defined, otherwise the code won't compile or run correctly.

 

[jimmyismyname]

i got it to create the pdf files , but now the problem is it does not choose the name "name = Split(CA.Range("J" & iter_row).Text, " ")(0)" unless you put it manually. After that the word and pdf files are being created , we did some adjustment to the template and now when it creates the word and pdf files it does not upload the numbers in the word table. when i check on advance properties of the word file created it shows the right value but not in the word sheet.

Please help its very urgent

 

[jimmyismyname]

i got it to create the pdf files , but now the problem is it does not choose the name "name = Split(CA.Range("J" & iter_row).Text, " ")(0)" unless you put it manually. After that the word and pdf files are being created , we did some adjustment to the template and now when it creates the word and pdf files it does not upload the numbers in the word table. when i check on advance properties of the word file created it shows the right value but not in the word sheet.

Please help its very urgent

Also the new columns that are added to the table i wrote it in the code aswell

 

[John_w]

now the problem is it does not choose the name "name = Split(CA.Range("J" & iter_row).Text, " ")(0)" unless you put it manually

Cells J3 and down on the "Corporate Action" sheet must contain text before you run the macro.

it creates the word and pdf files it does not upload the numbers in the word table

Also the new columns that are added to the table i wrote it in the code aswell

There is nothing in your code which updates a table in the Word document.