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...

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

[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:banghead:

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:
wdRng = "Company-Name " & wks.Cells(ActiveCell.Row, 1) _
& vbNewLine & _

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

[Kenneth Hobs]

Whoever set up the data like that has little knowledge about databases. Poor structure lends itself to headaches later. Address and Names are 2 Fields where these problems occur often.

This kind of approach is the only method that I know to fix 95% of the problem.
Sub t()
Dim s As String, a() As String, b() As String, ac As Integer
s = "2400, 300 Smith St. SW, Sulphur, La, 70663"
a() = Split(s, ",")
b() = a()
ac = UBound(a)
ReDim Preserve b(0 To ac - 3)
s = Join(b, ", ") & vbCrLf & a(ac - 2) & ", " & a(ac - 1) & vbCrLf & a(ac)
MsgBox s
End Sub

Obviously, you need to replace s with the value of your address in that cell. Replace the MsgBox with the line where you set the value with your range object.

[georgiboy]

Nice Ken i like it, not alot of code for a good result.

In the UK where i live addresses are set up a little different, more like...

Name, Road, Town, County, Postcode

Name
Road
Town
County
Postcode

I have used something like this in the past...

Sub Add()
Dim AddR As String
Dim NameR As String, NumStreet As String, Town As String
Dim County As String, PstCode As String
Dim Rest1 As String, Rest2 As String, Rest3 As String

AddR = Range("A1").Value

NameR = Left(AddR, InStr(AddR, ",") - 1)
Rest1 = Right(AddR, Len(AddR) - InStr(AddR, ",") - 1)
NumStreet = Left(Rest1, InStr(Rest1, ",") - 1)
Rest2 = Right(Rest1, Len(Rest1) - InStr(Rest1, ",") - 1)
Town = Left(Rest2, InStr(Rest2, ",") - 1)
Rest3 = Right(Rest2, Len(Rest2) - InStr(Rest2, ",") - 1)
County = Left(Rest3, InStr(Rest3, ",") - 1)
PstCode = Right(Rest3, Len(Rest3) - InStr(Rest3, ",") - 1)

MsgBox NameR & vbNewLine & NumStreet & vbNewLine & Town & _
vbNewLine & County & vbNewLine & PstCode
End Sub
Just thought i would add for info although Ken's solution to this address issue is more suited for you

[Kenneth Hobs]

I don't want to wonder from IcePirate's question too much but here is how I would do your scenario georgiboy if I understand it georgiboy.
Sub t()
Dim s As String
s = "2400, 300 Smith St. SW, Sulphur, La, 70663"
s = Replace(s, ",", vbCrLf)
MsgBox s
End Sub

Of course this will fail to work as one wants under some conditions. e.g. A City or Street name has a comma in their name.

[IcePirates]

Kenneth, I applied something similar to my script, and I got some errors, but then I tweaked it a bit and it seemed to have worked! Thanks Georgiboy and Kenneth.

I may marked this topic as solved and start a new thread, let me know what your advice is after reading this reply.

(See attachment)
The script works well, but after speaking with some co-workers they also want a drop down box that drops down to the active line (like the command button does)...And the reason for this drop down is, it selects a transmittal template. So for example the three options inside the drop down would be "Filing", "Pipeline X-Rays", "Boxes", once a user goes to a line, then the user will select which transmital template they want (ie: x-ray, filing, box), then click "Create Transmittal"...And depending on which template the user selects each transmittal is slightly different.

So is this how I approached this, but I encountered a couple of errors..Ive added the combo-box; now above the command button statement, I would add:


Private Sub ComboBox1_Change(ByVal Target As Range)
'If Target.Row = Me.ComboBox1.TopLeftCell.Row Then Exit Sub
Me.ComboBox1.Top = Range("H" & Target.Row).Top
Me.ComboBox1.Left = Range("H" & Target.Row).Left
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.CommandButton1.Top = Range("G" & Target.Row).Top
Me.CommandButton1.Left = Range("G" & Target.Row).Left

End Sub


Then, once that is accomplished, went down to the 'Transfer Data' part of the script, and divided it up into three parts that only are called when a selection in the combo box is clicked...the way I did it was by using elseif but I deleted my script, because it simply just didn't work, too many errors. I was trying to use the Range("").Value = entername.Value
but I couldn't apply that to my situation here.


Anyway - my attachment has the combo box, and if you guys want to see my original script I can quickly throw it back together for you and post the attachment...But let me know your thoughts...

Thanks!

[georgiboy]

This should shed some light...

[IcePirates]

Fantastic,

Works perfect...I see how you re-arranged my commands for my buttons, and added it into the script...My one last question would be setting the range for the drop down, I see in column J you have set the range, but does it have to be there? For example, like if I didn't want to have it on the sheet at all...Is there another way for me to alter the combo box range, without having it on the sheet visible to everyone?

Would I just have to add in a
Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "Transmittal1"
ComboBox1.AddItem "Transmittal2"
ComboBox1.AddItem "Transmittal3"
ComboBox1.Text = ComboBox1.List(0)
End Sub

do the 'data' sheet? Should be something simple like that huh?
thanks!

[georgiboy]

Yep that works fine for me. Remove the range for the combobox using properties, go into design mode and right click on it and select properties, you will find it there.

[IcePirates]

Hey Georgiboy,

Did that code work for you? Could you double check for me, I tried adding it into my script and it really didn't do much.

Im going to play with it some more, and Ill report back - but I just wanted to make sure it worked for you, because it didnt work for me, its not that Im getting errors or anything, its just not changing th values in the combo-box to say
"Transmittal 1"
"Transmittal 2"
"Transmittal 3"

[georgiboy]

A workbook module should do it...

Goes in the workbook code window

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

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
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)

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,

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

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

Instead of If Sheets("Data".ComboBox1.Value = "Transmittal 1" Then

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.

'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 & ":D" & 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

[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:
.TypeText Excel.Range("P1").Value
to offset to the preceeding row in column P from the current row:
.TypeText Excel.Range("P" & cRow).Offset(-1,0).Value

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


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

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.

Sub sadd()
MsgBox Selection.Address, , "Selection.Address"
MsgBox Selection.Row, , "Selection.Row"
MsgBox ActiveCell.Address, , "ActiveCell.Address"
MsgBox ActiveCell.Row, , "ActiveCell.Row"
End Sub

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.