Excel to Word loop

[rottingtom]

Hello,

Trying to feed excel data to word document with bookmarks, save as the name + todays date in the same location of the template, close application, and open the template again to continue the loop until its complete. I am getting stuck at the bold line and getting run-time error '91': Object variable or With block variable not set.


Sub XltoWd()
Dim Wdobj As Object
Dim Dcobj As Object
Dim ws As Worksheet
Dim Row As Long
Dim Col As Integer
Dim Lrow As Integer




Set ws = ThisWorkbook.Sheets("Sheet1")


Set Wdobj = CreateObject("Word.Application")


Wdobj.Visible = True





Dcobj = Wdobj.Documents.Open("J:\Service Center\RMS HUD Project\RMStemplate\Template.docx")

'Set starting point for loop at row 3
Row = 3
'Sets last row
Lrow = ws.Range("B" & Rows.Count).End(xlUp).Row

'Sets filename as borrower name
savename = ws.Range("B" & Row).Value & Format(Now, "mmddyyyy")

'loop starts
Do Until Row = Lrow

With Wdobj.ActiveDocument
.Bookmarks("SKEY").Range.Text = ws.Range("A" & Row).Value
.Bookmarks("Borrower").Range.Text = ws.Range("B" & Row).Value
.Bookmarks("CoBorrowerName").Range.Text = ws.Range("C" & Row).Value
.Bookmarks("PropertyAddress").Range.Text = ws.Range("D" & Row).Value
.Bookmarks("PropertyCity").Range.Text = ws.Range("E" & Row).Value
.Bookmarks("PropertyState").Range.Text = ws.Range("F" & Row).Value
.Bookmarks("PropertyZip").Range.Text = ws.Range("G" & Row).Value
.Bookmarks("Expiration").Range.Text = ws.Range("H" & Row).Value
Wdobj.ActiveDocument.SaveAs2 Filename:=savename & ".docx"
Wdobj.Quit
Application.Wait (Now + TimeValue("0:00:03"))

End With
Set Wdobj = Nothing
Wdobj.Documents.Open "J:\Service Center\RMS HUD Project\RMStemplate\Template.docx"
Row = Row + 1
Loop


Set Wdobj = Nothing


End Sub

[JKwan]

I am no Word expert, however, looking at your code.... You destroyed the Wdobj object... This is why you have the error. Don't set it to nothing (Wdobj) until at the very end, when you know you are done with the object.

[macropod]

You destroyed the Wdobj object... This is why you have the error. Don't set it to nothing (Wdobj) until at the very end, when you know you are done with the object.
The code 'Set Wdobj = Nothing' appear both inside the loop and after the loop has ended, so it's the one inside the loop that's an issue. Another issue is that 'Wdobj.Quit' is also inside the loop.

[rottingtom]

The code 'Set Wdobj = Nothing' appear both inside the loop and after the loop has ended, so it's the one inside the loop that's an issue. Another issue is that 'Wdobj.Quit' is also inside the loop.

Thanks for the feedback guys, I've updated the code. So my logic in putting the Wdobj.Quit on that line is I want the template doc to filled out, saved, closed(quit), wait some time and open a new template to continue the loop.

I'm getting a Run-Time error '462': The remove server machine does not exist or is unavailable in the bolded line. Any thoughts ?

Sub XltoWd()
Dim Wdobj As Object
Dim Dcobj As Object
Dim ws As Worksheet
Dim Row As Long
Dim Col As Integer
Dim Lrow As Integer




Set ws = ThisWorkbook.Sheets("Sheet1")


Set Wdobj = CreateObject("Word.Application")


Wdobj.Visible = True




'Change the pathway to retrieve the word template between the " "
Wdobj.Documents.Open "J:\Service Center\Operational Integrity Team\SIT\PVT\Accounts\RMS\Test\Template.docx"

'Set starting point for loop at row 3
Row = 3
'Sets last row
Lrow = ws.Range("B" & Rows.Count).End(xlUp).Row

'Sets filename as borrower name
savename = ws.Range("B" & Row).Value & " " & Format(Now, "mm-dd-yyyy")

'loop starts
Do Until Row = Lrow

With Wdobj.ActiveDocument
.Bookmarks("SKEY").Range.Text = ws.Range("A" & Row).Value
.Bookmarks("Borrower").Range.Text = ws.Range("B" & Row).Value
.Bookmarks("CoBorrowerName").Range.Text = ws.Range("C" & Row).Value
.Bookmarks("PropertyAddress").Range.Text = ws.Range("D" & Row).Value
.Bookmarks("PropertyCity").Range.Text = ws.Range("E" & Row).Value
.Bookmarks("PropertyState").Range.Text = ws.Range("F" & Row).Value
.Bookmarks("PropertyZip").Range.Text = ws.Range("G" & Row).Value
.Bookmarks("Expiration").Range.Text = ws.Range("H" & Row).Value
Wdobj.ActiveDocument.SaveAs2 Filename:=savename & ".docx"
Wdobj.Quit
Application.Wait (Now + TimeValue("0:00:03"))
Wdobj.Documents.Open "J:\Service Center\RMS HUD Project\RMStemplate\Template.docx"
End With


Row = Row + 1
Loop


Set Wdobj = Nothing


End Sub

[macropod]

You still have 'Wdobj.Quit' inside the loop! Instead of:

Wdobj.ActiveDocument.SaveAs2 Filename:=savename & ".docx"
Wdobj.Quit
You should have:

.SaveAs2 Filename:=savename & ".docx"
.Close False