I have a type mismatch error in vba excel, when I tried to open a Word document with a path stored i-CodePudding

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 vba code in excel file, where I open the Word document, I need to have the path of the Word document. If I hard code the Word document path, everything works fine, no error. But whenever I try to store the path in an excel cell and assign it to a variable, it caused an error 13 type mismatch.

I have already Dim the variable coverLocation as Variant data type. I have double check that the path is correct.

I tried to declare it as String, but it will give another error at the line where I Set coverLocation. The error is "Object Required".

Anyone could please tell me what is the correct way to do this? Thank you so much

Below are my simplified code just to show the same error I face in my full code

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

CodePudding user response：

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