Create Word Docs From Excel Cell Data

[PumaMan]

Forgive me, I'm learning VBA now, so I'm a rookie:

I have an excel sheet where a user is selecting certain values in 3 cells
from a drop down list (created with data validation). I then have a submit
button that they will hit and depending on the values within those 3 cells, I
want to open the appropriate word templates based on those values and then
populate those values into bookmarks.

You can call the excel and bookmark values whatever you like, I have an
understanding of logic, i'm just a bit of a rookie @ VBA.

Thanks in advance!
Elbio

 

[OssieMac]

Need a little more information on how to identify which template to use from
the selections in 3 cells. Depending on the number of options in each
dropdown there could be a hugh number of variations.

How many templates do you anticipate having?

Can you provide a sample of what the list of options are for each of the 3
cells and what combinations are applicable to specific templates?

 

[OssieMac]

Hi Elbio,

I did some work on this and thought that I should post what I have done
because I will not be able to reply again until sometime tomorrow.

It still needs the code to establish which Word Template to use based on the
selections. Thought that you might be able to do that yourself or maybe
another post with some explicit instructions on the criteria. (Feel free to
reply to this post if you can wait for the code.)

Anyway the following Excel VBA code opens a word document with a specified
template and populates the document at bookmarks.

When you open the Excel VBA editor you need to select Tools - > References.
Scroll down until you find Microsoft Word nn.0 Object Library.
Where nn.0 is the version of Office
10.0 is Office XP (or 2002)
11.0 is Office 2003
12.0 is Office 2007
Check the box and click OK. (Ensure you check the box; don't just select the
line)

You will see some lines of code commented out that give you options re
printing and closing the document without saving.

Sub WriteToWordDoc()

Dim objWord As Object 'Word Object
Dim strTemplateName As String 'Template name
Dim strPathTemplateName As String 'Path & Template
Dim strFirstOption As String '1st Dropdown
Dim strSecondOption As String '2nd Dropdown
Dim strThirdOption As String '3rd Dropdown

With Sheets("Sheet1")
strFirstOption = .Range("E1")
strSecondOption = .Range("F1")
strThirdOption = .Range("G1")
End With

'Need code to replace the following line to select the _
correct template based on the 3 dropdown selections.
strTemplateName = "TestTemplate.dot"

'Assign path and template file name to variable
strPathTemplateName = CurDir & "\" & strTemplateName

'Open Word and assign Word Application to a variable
Set objWord = CreateObject("Word.Application")

With objWord
'Create a new Word document based on the template
.documents.Add template:=strPathTemplateName
.Visible = True 'Can be False
.ActiveDocument.Bookmarks("BM_Name").Range = strFirstOption
.ActiveDocument.Bookmarks("BM_Address").Range = strSecondOption
.ActiveDocument.Bookmarks("BM_Locality").Range = strThirdOption

'.ActiveDocument.PrintOut 'Uncomment this line if required

'Close document without saving
'.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

'Alternative to previous line of code.
.ActiveDocument.Close ' Opens Save Dialog box
.Quit 'Close Word
End With

'Cleanup
Set objWord = Nothing

End Sub

 

[PumaMan]

Thank you so much!!!! I got it to do what my goal was for today! I'll be
adding more from what I'm familiar with. I would like to add a dialogbox
that will display the values the user has selected before it gives the option
to save, so I'll work on that too. Here is my code incase you have any other
suggestions:

Private Sub SigCardGenerator_Click()
Call WriteToWordDoc
End Sub
Sub WriteToWordDoc()

Dim WordObject As Object 'Word Object
Dim SigCardTemplateString As String 'Template name
Dim SigCardPathString As String 'Path & Template
Dim AcctNumberString As String '1st Dropdown
Dim AcctTypeString As String '2nd Dropdown
Dim AcctStateString As String '3rd Dropdown

With Sheets("Sheet1")
AcctNumberString = .Range("E1")
AcctTypeString = .Range("E2")
AcctStateString = .Range("E3")
End With

If AcctStateString = "Florida" Then
SigCardTemplateString = "FL W9 Sig.dot"
SigCardPathString = "C:\Documents and Settings\nbk337h\Desktop\Excel
Process Docs\FL W9 Sig.dot"
End If

Set WordObject = CreateObject("Word.Application")

With WordObject
'Create a new Word document based on the template
..Documents.Add Template:=SigCardPathString
..Visible = True 'Can be False
..ActiveDocument.Bookmarks("AccountNumber").Range = AcctNumberString
..ActiveDocument.Bookmarks("AccountType").Range = AcctTypeString
..ActiveDocument.Bookmarks("AccountState").Range = AcctStateString

'.ActiveDocument.PrintOut 'Uncomment this line if required

'Close document without saving
'.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

'Alternative to previous line of code.
..ActiveDocument.Close ' Opens Save Dialog box
..Quit 'Close Word
End With

'Cleanup
Set objWord = Nothing

End Sub

 

[OssieMac]

I've got a few minutes before I go out today so just a couple of comments.

You haven't changed the object variable from objWord to WordObject in the
clean up. I understand that memory problems can result if these object
variables are not set to nothing when no longer required. As the very first
line in the declarations section of the modules enter Option Explicit and
then when you are finished editing code click Debug -> Compile VBA Project
and it picks up any code errors inclucing undeclared variables.

You can force Excel to automatically insert Option Explicit at the top of
modules in NEW projects /modules if you select Tools - > Options and on the
Editor tab check Require Variable Declaration.

You have included the template name with the path string
SigCardPathString = "C:\Documents and Settings\nbk337h\Desktop\Excel Process
Docs\FL W9 Sig.dot"

I would do it this way by concatenating the path and the variable
SigCardTemplateString :-

SigCardTemplateString = "FL W9 Sig.dot"

SigCardPathString = "C:\Documents and Settings\nbk337h\" & _
"Desktop\Excel Process Docs\" & SigCardTemplateString

I pointed out the above for two reasons. One for concatenating the path with
the filename variable.
The other shows the method of inserting a line break in an otherwise single
line of code when the break is required in the middle of a string enclosed in
double quotes.
Close off the double quotes and use an ampersand and then the space and
underscore for the line break and then open the double quotes on the next
line for the remainder of the string.

You may have this done by the time you get this but the following is a code
sample for a message to user re the selections:-

Dim Response

Response = MsgBox("Account Number = " & AcctNumberString & vbCrLf & _
"Account Type = " & AcctTypeString & vbCrLf & _
"Account State = " & AcctStateString & vbCrLf & _
vbCrLf & "Click OK to confirm or Cancel to exit", _
vbOKCancel, "Selections for word document ")

If Response = vbCancel Then
MsgBox "Processing will terminate"
Exit Sub
End If