I have a Word document with template contents where I will use VBA code to replace a textbox in the Word document with my user name to generate a pdf report for each user.
In my Excel VBA code, where I open the Word document, I need the path of the Word document.
If I hard code the Word document path, everything works.
When I store the path in a cell and assign it to a variable, it causes an error 13 type mismatch.
I declared the variable coverLocation as Variant.
I checked that the path is correct.
When I declare the variable as String it gives the error
"Object Required"
at Set coverLocation.
My simplified code to show the error.
Sub Test()
'Create and assign variables
Dim wb As Workbook
Dim ws1 As Worksheet
Dim saveLocation2 As String
Dim userName As Variant
Dim coverLocation As Variant
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("Sheet1")
Set userName = ws1.Range("B4")
Set coverLocation = ws1.Range("B2")
MsgBox coverLocation, vbOKOnly 'MsgBox showing correct path location
'Word variables
Dim wd As Word.Application
Dim doc As Word.Document
Set wd = New Word.Application
wd.Visible = True
saveLocation2 = wb.Path & Application.PathSeparator & userName & "cover.pdf"
'Word to PDF code
Set doc = wd.Documents.Open(coverLocation) ' "error 13 Type Mismatch" at this line
With doc.Shapes("Text Box Name").TextFrame.TextRange.Find
.Text = "<<name>>"
.Replacement.Text = userName
.Execute Replace:=wdReplaceAll
End With
doc.ExportAsFixedFormat OutputFileName:=saveLocation2, _
ExportFormat:=wdExportFormatPDF
Application.DisplayAlerts = False
doc.Close SaveChanges:=False
Application.DisplayAlerts = True
'Ending
wd.Quit
End Sub
I'm posting my comment as answer to make it more readable. The problem is, that in your code coverLocation is a Range object, not a string, and the same goes for userName.
The best way to fix this, is to replace this line:
Set coverLocation = ws1.Range("B2")`
with this:
coverLocation = ws1.Range("B2").Value
and additionally replace
Dim coverLocation As Variant
with
Dim coverLocation As String
Also, you should replace
Set userName = ws1.Range("B4")
with
userName = ws1.Range("B4").Value
In that case, replacing
Dim userName As Variant
with
Dim userName As String
is also advisable.
The final code could look like this:
Sub Test()
'Create and assign variables
Dim wb As Workbook
Dim ws1 As Worksheet
Dim saveLocation2 As String
Dim userName As String
Dim coverLocation As String
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("Sheet1")
userName = ws1.Range("B4").Value
coverLocation = ws1.Range("B2").Value
MsgBox coverLocation, vbOKOnly 'MsgBox showing correct path location
'Word variables
Dim wd As Word.Application
Dim doc As Word.Document
Set wd = New Word.Application
wd.Visible = True
saveLocation2 = wb.Path & Application.PathSeparator & userName & "cover.pdf"
'Word to PDF code
Set doc = wd.Documents.Open(coverLocation) ' "error 13 Type Mismatch" at this line
With doc.Shapes("Text Box Name").TextFrame.TextRange.Find
.Text = "<<name>>"
.Replacement.Text = userName
.Execute Replace:=wdReplaceAll
End With
doc.ExportAsFixedFormat OutputFileName:=saveLocation2, _
ExportFormat:=wdExportFormatPDF
Application.DisplayAlerts = False
doc.Close SaveChanges:=False
Application.DisplayAlerts = True
'Ending
wd.Quit
End Sub