Object variable or With block variable not set (Word through Excel, VBA)

[Garvin]

Hey,

I'm discovering VBA during my internship and there is no one at work who can really help me so I came here to ask for some explanations.

In my procedure (Sub WordTreatment () ),
the code stops running at the bold line and I don't really understand why. (Error : Object variable or With block variable not set)
I'm sure it's a stupid mistake but if anyone could help me that would be fantastic.

What I'm trying to do is open a Word document from Excel using VBA and copy/paste some data from a specific worksheet in an Excel Workbook into my word document.

Here is the code :

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

Sub Source()
'Opens the excel file
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open("C:\Users\x\Desktop\Folder\Sources 2014.xlsx")
End Sub


Sub WordTreatment()


Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open("C:\Users\x\Desktop\Folder\fichemodele.doc")



'Contrat
Dim Cont As Worksheet
Set Cont = xlBook.Worksheets("Contract")

LastRow = Cont.Cells(Rows.Count, 1).End(xlUp).Row
'Or Workbooks("Sources 2014").Worksheets("Contract").Cells(Rows.Count, 1).End(xlUp).Row
For x = 14 To LastRow
'xlBook.Worksheets("Contract").Cells(x, 3).Select
'SelRow = Selection.Row

'Country
xlBook.Worksheets("Contract").Cells(x, 2).Copy
With wrdDoc
.Application.Selection.Find.Text = "Country"
.Application.Selection.Paste
End With

ChangeFileOpenDirectory "C:\Users\x\Desktop\Folder"
ActiveDocument.SaveAs Filename:="Contract" & x, FileFormat:= _
wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
:=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False

Next x

End Sub


Thank you in advance for you help.

Garvin

 

[taurean]

Welcome to MrExcel.

If you run the second macro i.e. "Wordtreatment" without running the first macro "Source", you will get that error message.

You can combine both codes unless there is some reason for keeping them separate.

 

[Garvin]

Thank you very much for you precious help !

I can now focus on the really interesting stuff.