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

[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.
[VBA]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[/VBA]

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

[vba]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[/vba]
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.
[vba]Sub t()
Dim s As String
s = "2400, 300 Smith St. SW, Sulphur, La, 70663"
s = Replace(s, ",", vbCrLf)
MsgBox s
End Sub[/vba]

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:
[vba]

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
[/vba]

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

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