Solved: Customizing How Word Displays Information from Excel

[IcePirates]

Hey,

You know what, I think I had the same problem, I was playing with the code earlier and I was able to get the combo box to display "Transmittal 1", "Transmittal 2", "Transmittal 3" with the spaces but it would not transfer the data, it would just create blank word documents (Because I clearly messed something up), but I just opened your post and when you do it without the spaces it works as well...So I encoutered the same problem.

Thats strange isn't it?

I have one last question (I promise this time) Ive been staring at another C# project of mine in Visual Studio for the last 9 hours and so maybe Im missing something...but in Excel, I see the options in the combo-box have changed but after looking at the code for the data sheet and the module, I do not see where there was anything added, in the data-sheet
this is stil the same,
[vba]
Private Sub ComboBox1_Change()
End Sub
Private Sub CommandButton1_Click()
TransferData
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Target.Row = Me.CommandButton1.TopLeftCell.Row Then Exit Sub
Me.ComboBox1.Top = Range("F" & Target.Row).Top
Me.ComboBox1.Left = Range("F" & Target.Row).Left
Me.CommandButton1.Top = Range("G" & Target.Row).Top
Me.CommandButton1.Left = Range("G" & Target.Row).Left

End Sub[/vba]

And in the module sheet, the only thing I can see thats different is this part, it reads this:
[vba]If Sheets("Data").ComboBox1.Value = "Transmittal1" Then[/vba]

Instead of [vba]If Sheets("Data".ComboBox1.Value = "Transmittal 1" Then[/vba]

Are the spaces are the only thing that you changed to change the value in the combo boxes? If so, I tried that so many times, my sheet just kept screwing up, everytime I altered the value the combo-boxes, the command button would just creat blank word documents...

Do you know why that is?

If not its ok, Ill mark this topic as solved ,but I just thought there might be an explanation for it....I learn quite a bit, by seeing how scripters as yourself alter the code...

Anyways...Thanks for all your help!

- Jeff

[georgiboy]

The code for the Combobox is in the workbook tab not the worksheet tab, where you are going into the worksheet tab in the code window you will see a workbook tab, have a look in there.

As for the spaces question you can have it with or without spaces, all you need to do is ammend the code in the Workbook tab (in the code window) to populate the combobox with spaces and ammend the code in the module itself to to have spaces.

So if the combobox has "Transmittal 1" then the module code needs to be "Transmittal 1" and vice versa.

Hope this answers all the questions you had.

George

[IcePirates]

Hey,

haha, I actually just was playing with it, and got it all figured out excatly what you said!

Thanks for everything George, it was a big help!!

- Jeff

[Kenneth Hobs]

When using MSWord object code, be sure to test it first by opening MSWord before trying the code and vice-versa. Then close each. If the code does not account for this scenario, closing takes a bit of work.

I normally just use late binding methods for MSWord creations which makes it easier to account for the scenario above. However, I prefer early binding so that I can use intellisense while coding. In this example, I use both to get the best of both methods. There are some other methods to account for this scenario but they can get busy.

Focus can be an issue so I added some API routines. You may still have a bit of a focus issue.

If you don't need to edit the MSWord file when it is created, putting the code into an MSWord object and saving it as a doc file is handy. This method offers some other advantages.

When creating the doc file from scratch like this, record a macro in MSWord and this will show how to code the commands that you might want to use in your code.

You can easily use this code in the previous example xls. Rename the other sub or reference this sub with another name. Put this code in its own module. API declarations must be made at the top of a module.

[VBA]'TypeText methods:
'http://www.excelforum.com/excel-programming/650672-populate-word-document-from-excel.html#post1946784
'http://www.excelforum.com/showthread.php?p=1946784
Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Declare Function BringWindowToTop _
Lib "user32" _
(ByVal hwnd As Long) As Long
Sub TransferData()
'Add Reference for early binding: Microsoft Word 11.00 Object Libray (MSWord.olb)
'Early Binding
Dim cRow As Long
Dim doc As Word.Document
Dim wa As Word.Application
Dim wordRunning As Boolean, rc As Long

rc = FindWindow("OpusApp", vbNullString)
wordRunning = Not rc = 0
On Error Resume Next
Set wa = GetObject(, "Word.Application")
If Not wordRunning Then Set wa = New Word.Application

cRow = ActiveCell.Row
Set doc = wa.Documents.Add(DocumentType:=wdNewBlankDocument)
doc.Activate
wa.Visible = True
wa.ScreenUpdating = False
'BringWindowToTop FindWindow(vbNullString, wa.Caption)
With wa.Selection
'Transfer the data
.TypeText Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose( _
Range("A" & cRow & "" & cRow))), vbCrLf)
HR wa, 3 'enter .TypeParagraph 3 times
.Font.Bold = True
.TypeText "Attention: "
.Font.Bold = False
.TypeText Excel.Range("E" & cRow).Value
HR wa, 3
.TypeText "This is to show you an example of " & _
ActiveSheet.ComboBox1.Value & ":"
.TypeParagraph

Select Case ActiveSheet.ComboBox1.Value
Case "Transmittal1"
.TypeText "Case1"
Case "Transmittal2"
.TypeText "Case2"
Case "Transmittal3"
.TypeText "Case3"
Case Else
.TypeText "Case Else"
End Select
'Save and close doc 'Only if you want a specific document
'doc.Save
'doc.Close(False)
End With

wa.ScreenUpdating = True
Set doc = Nothing
If Not wordRunning Then Set wa = Nothing
End Sub
Sub HR(wd As Word.Application, Optional hrCount As Integer = 1)
Dim i As Integer
For i = 1 To hrCount
wd.Selection.TypeParagraph
Next i
End Sub

[/VBA]

[IcePirates]

Hello Kenneth,

I reviewed your code, and it seems pretty functional...

My question I guess, is what are the advantages of using API's and approaching the code this way...as opposed to the other way you worked on with me?

Reason I ask is because, your method seems to work well, but I have to look at this from two perspectives, 1. Do I understand whats going on in the code, and 2. How can we adapt the code so it's easist for the user.

One thing Im trying to alter in the code as we speak is...Say you created a Word document based on row 1...But in column "P" (where the file numbers are) (I think its column P) - there might be two or three rows that I need the file numbers from, so while script does what I need it too now...I need to figure out how I retrieve file numbers from multiple lines but just create a document still based on the information in the one specific row.

Anyways I know thats a little off topic, but as mentioned, I guess what Im really asking, is since learning from you guys here...What does adding API functions deliver to the script that makes it more efficient for the user?

Again thanks for the input and taking the time to explain.

[Kenneth Hobs]

The API command's are handy for some things. I used FindWindow() to check and see if MSWord was already open. I used BringWIndowToTop() to try and set the focus.

As for the cell references, just add what you need.
e.g. For absolute cell reference:
[VBA].TypeText Excel.Range("P1").Value[/VBA]
to offset to the preceeding row in column P from the current row:
[VBA].TypeText Excel.Range("P" & cRow).Offset(-1,0).Value[/VBA]

Generally, I would prefer to use bookmarks or FormFields to poke data into a pre-build doc.

Here are some threads that I have posted to.
'TypeText method
'http://www.excelforum.com/excel-programming/650672-populate-word-document-from-excel.html#post1946784
'http://www.excelforum.com/showthread.php?p=1946784

'FormFields
'http://www.mrexcel.com/forum/showthread.php?p=1639696
'http://www.mrexcel.com/forum/showthread.php?t=333200

'Add Hyperlink to Bookmark
'http://www.excelforum.com/excel-programming/664078-use-excel-vba-to-add-a-hyperlink-to-a-word-document.html#post2006430

'Save OLEObject as MSWord Document
'http://vbaexpress.com/forum/showthread.php?t=21619

'Add Table to MSWord
'http://vbaexpress.com/forum/showthread.php?t=23975
'http://vbaexpress.com/forum/showthread.php?p=168731

[IcePirates]

Hey Kenneth,

Awesome, thanks for the tip! Im going to read up on your posts, and Ill post back here or send you a message with any questions I might have (if thats ok)

To offsite as you mentioned
[vba]

.TypeText Excel.Range("P" & cRow).Offset(-1,0).Value [/vba]

Where the "-1" is, can I change that value to any number value I need based on how many rows in column P may need to be selected?

(Does that make sense?) I hope it does, if not let me know and I can re-wword the sentence and provide an example.

Thanks again Kenneth!

[Kenneth Hobs]

IF you select more than one cell for some reason and then press the Create Transmittal button, expect to get results for the last row, not the activecell's row. Try it and play this macro to see what I mean.

[VBA]Sub sadd()
MsgBox Selection.Address, , "Selection.Address"
MsgBox Selection.Row, , "Selection.Row"
MsgBox ActiveCell.Address, , "ActiveCell.Address"
MsgBox ActiveCell.Row, , "ActiveCell.Row"
End Sub[/VBA]

Most any situation can be coded if the rules are constant and known. As you will see, in that scenario, Selection.Row is going to fit better than Activecell.Row.

If you need the number of rows selected, Selection.Rows.Count. The -1 in the Offset means to go up 1 row from the activecell's row.

Did you see how I made a row of cells into one string separated by vbcrlf? If you are doing a column, just use one WorksheetFunction.Transpose. There are some other ways to this as well.

When you have a new question, just post it. If it relates to a thread like this one, then post a link. I generally look at this forum every day. You can PM me if you get no responses. The benefit is that you will probably get more timely answers that may be even better than mine.