Issue with coding for using excel to fill text forms in word

Z

zoog25

Active Member
Joined
Nov 21, 2011
Messages
418
Hello all,

I have the following code that i have been piecing together in order to use excel cells to fill in a word document i have several text form fields. Now this is a sub macro within a larger program. The main information that come in is the Target.row information which gives us the location of the information being extracted and placed in the word document.

Rich (BB code): 
Sub NOCDoc(target As Long)


Dim Wordapp As Word.Application
Dim Location As Variant
Set NOCws = ThisWorkbook.Worksheets("NOC")
Set Wordapp = CreateObject("Word.Application")
Wordapp.Documents.Open ("E:\Special Project\NOC Templates\NOC Template.docx")


'Project Entry
If Not NOCws.Cells(target, "G").Value Is Nothing Then
    If NOCws.Cells(target, "F").Value < 1000 Then
        Wordapp.ActiveDocument.FormFields("TXProject").Result = "Tract " & NOCws.Cells(target, "F").Value & "Unit" & NOCws.Cells(target, "G").Value
    Else
        Wordapp.ActiveDocument.FormFields("TXProject").Result = "Parcel Map " & NOCws.Cells(target, "F").Value & "Phase" & NOCws.Cells(target, "G").Value
    End If
Else
    If NOCws.Cells(target, "F").Value < 1000 Then
        Wordapp.ActiveDocument.FormFields("TXProject").Result = "Tract " & NOCws.Cells(target, "F").Value
    Else
        Wordapp.ActiveDocument.FormFields("TXProject").Result = "Parcel Map " & NOCws.Cells(target, "F").Value
    End If
End If


'Location
Location = InputBox("Provide the site location of this project.", "Tract / Parcel Location")


Wordapp.ActiveDocument.FormFields("TXLocation").Result = Location


'Developer Information
Wordapp.ActiveDocument.FormFields("TXDeveloper").Result = NOCws.Cells(target, "I").Value


'Project Completion Date
Wordapp.ActiveDocument.FormFields("TXCompletionDate").Result = NOCws.Cells(target, "K").Value
End Sub

I have been testing the above code and the problem i am getting is in the red part. First the word document in question "NOC Template.docx" doesn't physically open and then i get an error when the code exam cell G of the target row. Please let me know what i'm doing wrong.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Try this

Code: 
Sub NOCDoc(target As Long)
    
    Dim Wordapp As Word.Application
    Dim Location As Variant
    Set NOCws = ThisWorkbook.Worksheets("NOC")
    Set Wordapp = CreateObject("Word.Application")
    
[COLOR=#ff0000]    Dim wFile As String[/COLOR]
[COLOR=#ff0000]    wFile = "E:\Special Project\NOC Templates\NOC Template.docx"[/COLOR]
[COLOR=#ff0000]    If Dir(wFile) <> "" Then[/COLOR]
[COLOR=#ff0000]        Wordapp.Documents.Open (sfile)[/COLOR]
[COLOR=#ff0000]        Wordapp.Visible = True[/COLOR]
[COLOR=#ff0000]    Else[/COLOR]
[COLOR=#ff0000]        MsgBox "File does not exists"[/COLOR]
[COLOR=#ff0000]        'do actions[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
    
    'Project Entry
    '[COLOR=#0000ff]If you only want to check the contents of the cell, then[/COLOR]
[COLOR=#ff0000]    If NOCws.Cells(target, "G").Value <> "" Then[/COLOR]
        If NOCws.Cells(target, "F").Value < 1000 Then
            Wordapp.ActiveDocument.FormFields("TXProject").Result = "Tract " & NOCws.Cells(target, "F").Value & "Unit" & NOCws.Cells(target, "G").Value
        Else
            Wordapp.ActiveDocument.FormFields("TXProject").Result = "Parcel Map " & NOCws.Cells(target, "F").Value & "Phase" & NOCws.Cells(target, "G").Value
        End If
    Else
        If NOCws.Cells(target, "F").Value < 1000 Then
            Wordapp.ActiveDocument.FormFields("TXProject").Result = "Tract " & NOCws.Cells(target, "F").Value
        Else
            Wordapp.ActiveDocument.FormFields("TXProject").Result = "Parcel Map " & NOCws.Cells(target, "F").Value
        End If
    End If
    
    
    'Location
    Location = InputBox("Provide the site location of this project.", "Tract / Parcel Location")
    
    
    Wordapp.ActiveDocument.FormFields("TXLocation").Result = Location
    
    
    'Developer Information
    Wordapp.ActiveDocument.FormFields("TXDeveloper").Result = NOCws.Cells(target, "I").Value
    
    
    'Project Completion Date
    Wordapp.ActiveDocument.FormFields("TXCompletionDate").Result = NOCws.Cells(target, "K").Value
End Sub
 
Upvote 0
Dante Amor: wFile or sfile???

zoog25: Writing to documents and formfields that way is risky. Try using bookmarks instead of formfields and a true Word template (i.e. a dotx file instead of a docx file):
Code: 
Sub NOCDoc(target As Long)
    
    Dim WordApp As New Word.Application, WordDoc As Word.Document
    Dim NOCws As Worksheet, Location As Variant, StrTxt As String
    Const sFile As String = "E:\Special Project\NOC Templates\NOC Template.dotx"
    Set NOCws = ThisWorkbook.Worksheets("NOC")
    WordApp.Visible = True
    
    If Dir(wFile) <> "" Then
        Set WordDoc = WordApp.Documents.Add(sFile)
    
        'Project Entry
        'If you only want to check the contents of the cell, then
        If NOCws.Cells(target, "G").Value <> "" Then
            If NOCws.Cells(target, "F").Value < 1000 Then
                StrTxt = "Tract " & NOCws.Cells(target, "F").Value & "Unit" & NOCws.Cells(target, "G").Value
            Else
                StrTxt = "Parcel Map " & NOCws.Cells(target, "F").Value & "Phase" & NOCws.Cells(target, "G").Value
            End If
        Else
            If NOCws.Cells(target, "F").Value < 1000 Then
                StrTxt = "Tract " & NOCws.Cells(target, "F").Value
            Else
                StrTxt = "Parcel Map " & NOCws.Cells(target, "F").Value
            End If
        End If
        WordDoc.Bookmarks("TXProject").Range.Text = StrTxt
    
        'Location
        Location = InputBox("Provide the site location of this project.", "Tract / Parcel Location")
        WordDoc.Bookmarks("TXLocation").Range.Text = Location
        'Developer Information
        WordDoc.Bookmarks("TXDeveloper").Range.Text = NOCws.Cells(target, "I").Value
        'Project Completion Date
        WordDoc.Bookmarks("TXCompletionDate").Range.Text = NOCws.Cells(target, "K").Value
    Else
        MsgBox "File does not exists"
        'do actions
    End If
End Sub
 
Upvote 0
Macropod said:
Dante Amor: wFile or sfile???

zoog25: Writing to documents and formfields that way is risky. Try using bookmarks instead of formfields and a true Word template (i.e. a dotx file instead of a docx file):
Code: 
Sub NOCDoc(target As Long)
    
    Dim WordApp As New Word.Application, WordDoc As Word.Document
    Dim NOCws As Worksheet, Location As Variant, StrTxt As String
    Const sFile As String = "E:\Special Project\NOC Templates\NOC Template.dotx"
    Set NOCws = ThisWorkbook.Worksheets("NOC")
    WordApp.Visible = True
    
    If Dir(wFile) <> "" Then
        Set WordDoc = WordApp.Documents.Add(sFile)
    
        'Project Entry
        'If you only want to check the contents of the cell, then
        If NOCws.Cells(target, "G").Value <> "" Then
            If NOCws.Cells(target, "F").Value < 1000 Then
                StrTxt = "Tract " & NOCws.Cells(target, "F").Value & "Unit" & NOCws.Cells(target, "G").Value
            Else
                StrTxt = "Parcel Map " & NOCws.Cells(target, "F").Value & "Phase" & NOCws.Cells(target, "G").Value
            End If
        Else
            If NOCws.Cells(target, "F").Value < 1000 Then
                StrTxt = "Tract " & NOCws.Cells(target, "F").Value
            Else
                StrTxt = "Parcel Map " & NOCws.Cells(target, "F").Value
            End If
        End If
        WordDoc.Bookmarks("TXProject").Range.Text = StrTxt
    
        'Location
        Location = InputBox("Provide the site location of this project.", "Tract / Parcel Location")
        WordDoc.Bookmarks("TXLocation").Range.Text = Location
        'Developer Information
        WordDoc.Bookmarks("TXDeveloper").Range.Text = NOCws.Cells(target, "I").Value
        'Project Completion Date
        WordDoc.Bookmarks("TXCompletionDate").Range.Text = NOCws.Cells(target, "K").Value
    Else
        MsgBox "File does not exists"
        'do actions
    End If
End Sub
Click to expand...

Sorry, typo error.
Is wFile
 
Upvote 0
Thank you guys. The coding is working to pull up the word document and fill in the form fields. I will look into taking your advise about either change from form fields to bookmarks. I'll have to do a little trial and error but atleast the coding to access the word document is in place. Thank you once again.
 
Upvote 0
zoog25 said:
Thank you guys. The coding is working to pull up the word document and fill in the form fields. I will look into taking your advise about either change from form fields to bookmarks. I'll have to do a little trial and error but atleast the coding to access the word document is in place. Thank you once again.
Click to expand...

I'm glad to help you. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question
Help with conversion of code to Late Binding
Replies
3
Views
495
RoryA
RoryA
Z
  • Question
Update Cross Reference in Word from Excel
Replies
0
Views
642
zoog25
Z
C
  • Question
Running VBA Code from Excel to delete a Rich Text Format from a Word Document Dependent on A Dropdown Box in Excel
Replies
4
Views
940
Dan_W
D
Z
  • Question
Range selection with generated row location
Replies
2
Views
554
zoog25
Z
M
  • Question
Extract data from Word to Excel
Replies
1
Views
1K
MHamid
M

Forum statistics

Threads
1,214,521
Messages
6,120,018
Members
448,937
Latest member
BeerMan23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top