Consulting

Results 1 to 5 of 5

Thread: Excel to Word loop

  1. #1

    Excel to Word loop

    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

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    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.

  3. #3
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by JKwan View Post
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    Quote Originally Posted by macropod View Post
    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

  5. #5
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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

    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •