Macro for Word in Excel

[rlc8981]

I am trying to do the following:

I am creating an order checklist in Excel. I want it that if the sales rep clicks on a certain field that a macro will populate the form that is needed. If the rep does not click on that box then the certain form will not be populated. Is this possible?

[royUK]

I would think it is possible, but it's not clear exactly what you are asking

[Kenneth Hobson]

Do you mean that you want to fill formfields in an MSWord document with data from Excel? If so, see: http://www.mrexcel.com/forum/showthread.php?p=1639696

[rlc8981]

Im sorry if i didnt explain it well. We want to create a main checklist in excel that our sales rep can go through and click on what applys for that order. Examples would be, is it a lease, does it need connected, etc. If they click on if it needs connected a macro will populate the connectivity form that is needed. If it is a lease then the lease paperwork will be populated to fill out. Let me know it that makes sense.

[royUK]

Where are the forms to be filled in, are they word documents?

[rlc8981]

Most forms will be in Word format.

[royUK]

It would be much easier to create the forms in the Excel workbook

[rlc8981]

I could do that, that is no problem. But I would still need to know how to write a macro that only populates the formst that are needed. Is this possible? Thanks!!!

[royUK]

Yes it's possible, but it would be necessary to see your workbook

[Kenneth Hobson]

In the link that I referenced, it has 2 methods to write to an MSWord fiile from Excel data. You can create the MSWord file totally new or use a pre-built DOC using formfields. I have also used bookmarks to mark locations to fill data from Excel to an MSWord file.

If you want to do it all in Excel, see: http://www.j-walk.com/ss/excel/tips/tip92.htm

[rlc8981]

Please see attached.
I dont have the checklist created yet, I wanted to see if it is possible first. but i created a test sheet that has one of the questions that will be on the checklist. (is connectivity needed?) I have a column for yes and a coumn for no. What I want is, if someone puts an X in the Yes column then the other attachment. (edra.doc) will come up, if they click X inthe NO column nothign comes up.

[Kenneth Hobson]

You don't want to insert data from excel into the doc?

If all you are doing is opening a doc file, insert a hyperlink.

[rlc8981]

The problem with that is our sales reps get confused on what doc is needed when. We want to create it so they type in the info they know about the customer and the appropriate docs come up.

[Kenneth Hobson]

I could do it easily if I knew the rules. Just make a few simple doc files and sample data in excel that has the options that make a selection viable.

Most likely, a drill down method would be best.

Scenarios:
1. Pick Doc ID: id.doc opens.
2. Pick Customer Last Name, First Name list pops up, ID list pops up, id.doc opens.
etc.

I would probably just do these drop lists in a userform. Data validation and/or Control Toolbox toolbar controls can be used as well. Another method is to add descriptions and comments and other information to a doc's file properties. Selections can then be made more easily from the File Open dialog or Explorer.

[rlc8981]

ok...So please see attached. I have test.xls (this is the start to the main checklist) I have also attached different lease docs for different banks. An example of how I woudl want it to work is, If you click an X under USB in cell B18 then the lease doc for USB comes up, if you put an X under GE in F18 then the lease doc for GE comes up. Could you show me a formula that would allow this to work? I was only able to upload the lease for GE...but if you could get me started I can go from there. Thanks!!!

[Kenneth Hobson]

Normally, one would just use the sheet's selection or change event for this. It is posiblie to have both a selection and a change event to trigger code. However, it seems that you want an "X" and then a selection.

I used the name of the row just above the "X" as part of the filename. You would need to change the path for your files.

You can be more specific if needed. Use a Select Case for that scenario when your filename does not match the naming convention that I guessed.

To use this, right click your sheet's tab, View Code, and paste. Enter and X in one of B19:F19. Then click some other cell and then click the X cell.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Intersect(Target, Range("B19:F19")) Is Nothing Then Exit Sub
  If Target.Value = "X" Then OpenDoc "c:\myfiles\msword\" & Target.Offset(-1, 0) & ".doc"
End Sub

Sub OpenDoc(sDocName As String)
  If Dir(sDocName) = "" Then Exit Sub
  Shell "cmd /c " & sDocName, vbNormalFocus
End Sub

[rlc8981]

Here is the code that I used and it is not coming up.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B19:F19")) Is Nothing Then Exit Sub
If Target.value = "X" Then OpenDoc "N:/ORDER PACKET FORMS/CITI LEASE DOC GLOIMS29 02-05" & Target.Offset(-1, 0) & ".doc"
End Sub

Sub OpenDoc(sDocName As String)
If Dir(sDocName) = "N:/ORDER PACKET FORMS/CITI LEASE DOC GLOIMS29 02-05" Then Exit Sub
Shell "cmd /c " & sDocName, vbNormalFocus
End Sub