Solved: Customizing How Word Displays Information from Excel

[IcePirates]

Hello,

This post is some-what related to my last post located here:
http://www.vbaexpress.com/forum/showthread.php?t=23975

The post relates to creating a command button in Excel that executes a Macro, and builds a word document...Now that word document has information that is based on which ever row you press the command button on. IE: If you press the command button when your on Row 4, the Macro will build a word document with information only from Row 4 of your Excel document.

Phase 2 of this project is customizing how the Word document is going to display. I have read that I need book-marks in the Word document, but if the Word-document is non-existant then there is no way to place book marks in the Word document...I have attached a Word document showing how I want the information to display the information from the Excel sheet.

Im wondering if anyone has any advice on how to accomplish this...The code that is used to create the Word document is below...

[vba]Option Explicit

'http://vbaexpress.com/forum/showthread.php?p=168731
Public Sub TransferData()
'This macro transfers the data range "A1:E11" to a table in Word
'
'Constants:
'docFullName = The full name of an already existing Word document
'
'Variables:
'doc = The Word document (assumed to be empty)
'i = A counter (for rows)
'j = A counter (for columns)
'tbl = A Word table
'wdRng = A Word range (the first paragraph of doc)
'wks = The worksheet "data" that contains the data range
'
'Const docFullName = "C:\OLE Automation\Word.doc" '
Dim doc As Object
Dim i As Long
Dim j As Long
Dim tbl As Object
Dim wdApp As Object 'Only if you require a new document each time
Dim wdRng As Object
Dim wks As Worksheet
'Assing Word objects 'Only if you require a new document each time
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
Set doc = wdApp.Documents.Add
'Assign variables and objects
'Set doc = GetObject(docFullName) 'Only if you want a specific document
Set wdRng = doc.Paragraphs(1).Range
'Set tbl = doc.Tables.Add(wdRng, 11, 5)
Set tbl = doc.Tables.Add(wdRng, 1, 5)
Set wks = ThisWorkbook.Worksheets("data")
'Transfer the data
With tbl
'For i = 1 To 11
For i = ActiveCell.Row To ActiveCell.Row
For j = 1 To 5
.Cell(i, j) = wks.Cells(i, j)
Next j
Next i
End With
'Save and close doc 'Only if you want a specific document
'Call doc.Save
'Call doc.Close(False)
'Clean
Set doc = Nothing
Set wks = Nothing
End Sub
[/vba]

[georgiboy]

I have come up with this you may be able to use the vbNewline part within your original code.

Hope this helps

[georgiboy]

This one makes use of the ActiveCell.Row part you needed

Hope this helps

[Kenneth Hobs]

Here are some threads with various methods that I have helped with.
'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 Georgiboy,

Your book1.xls works great!!

Thanks for the input...I have two questions...

1. Not to say anything bad against anyone, but the button that creates the document, is there anyway to get that to follow the user regardless of which line they go to (see my attachment, Im not sure excatly what does that, so I wondering if you knew how to do it...a guy in my last thread helped me with that example) The only reason I want it that way is because - I actually really like it your way, but the truth of the matter is employee's where Im at may not completely grasp the concept they have to go to a line > then scroll up and hit the <Command Button>...I just figured having it always at the line the user is at would make it easier for them to understand.

2. Right now when the Word document is created, the information is within a table or Border? (Whatever you want to call it)...Is there anyway to remove that completly?

Thanks again!


The VBNewLine works great!

- jEFF

[georgiboy]

You will find the code for the button in the Sheet named "Data" double click on this sheet in the code window, you can change the column in there.

The data is no longer in a table just on the document like normal.

Hope this helps

[IcePirates]

I just wanted to login and say thank you for your help with this script, its been very benifical. Im going to go ahead and mark this topic as solved, but before I mark it as solved, I have one last small question

The Column headers are as follows:
| Company Name | Address | Contact Name | File Type |

Now, in the address column address's are written like this the cell:
2400, 300 Smith St. SW, Sulphur, La, 70663

How I need it to display in the word document is like this

2400, 300 Smith St. SW
Calgary, Ab
T2W-3N3

So within the VB code at this line:
[vba]wdRng = "Company-Name " & wks.Cells(ActiveCell.Row, 1) _
& vbNewLine & _[/vba]

Is there any way to add in a script that will automatically format address's in the correct format? Or is there a way to get the script to use vbNewLine to solve this? Do you have any suggestions?

The user has to be-able to enter the entire address in one cell in the Excel sheet, but when the command button is clicked and a word document is created the address needs to appear in the Word document it correctly, not just the whole address on one line...Any tips?

Let me know - thanks again everyone

[georgiboy]

A workbook module should do it...

Goes in the workbook code window

[vba]Private Sub Workbook_Open()
Sheets("Data").ComboBox1.Clear
Sheets("Data").ComboBox1.AddItem "Transmittal1"
Sheets("Data").ComboBox1.AddItem "Transmittal2"
Sheets("Data").ComboBox1.AddItem "Transmittal3"
Sheets("Data").ComboBox1.Text = Sheets("Data").ComboBox1.List(0)
End Sub[/vba]

The last code worked but only when you switched sheets because it was a sheet activate

[IcePirates]

Hey,

I included your script above in my new sheet (attached) take a look at module2, see if I did that right. (Not quite sure if I did)...But I didn't remove columns J, K, L - just yet.

Talk to you soon.

- Jeff

[georgiboy]

The code i provided is to go into the workbook not the worksheet, it fills the combobox when the workbook is opened.

I am not at a computer with Excel installed so i cant check your sheet yet.

You need to remove the link area from the properties window of the combobox as i touched on in my post above.

Hope this helps

[IcePirates]

Hey, ok I added the code to the work-book, and Im getting an error saying, "Could not set the box property, unspecified error", and for some reason the combo box is has 'Transmittal 1' listed, but still not being populated with <Transmittal 2> and <Transmittal 3>
I think Transmittal 1 is there, because I placed it there by editing the text field in the properties window...but the issue still exists, when I try and add
[vba] Sheets("Data").ComboBox1.Clear
Sheets("Data").ComboBox1.AddItem "Transmittal 1"
Sheets("Data").ComboBox1.AddItem "Transmittal 2"
Sheets("Data").ComboBox1.AddItem "Transmittal 3"
Sheets("Data").ComboBox1.Text = Sheets("Data").ComboBox1.List(0)[/vba]

to the work-book...I get the error I spoke of above.

I included an attachment

[georgiboy]

I have done it for you.

EDIT: Had to take out the spaces from the "Transmittal 1" to be "Transmittal1" to make it work.

[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