PDA

View Full Version : Controlling MS Word from Excel VBA



JPL007
11-01-2012, 11:14 AM
Hi all
thank you for reading this.

I am trying to write a macro to control a Word document (wrdDoc) from Excel VBA. However the syntax is quite different between them.

The first line is working, I cannot get the right syntax for the rest.
Any help please ?
cheers
Julien

Sub createTableUnderlying(nbUnderlyings, leUnd)
wrdDoc.bookmarks("FundTicker").Select

wrdDoc.Selection.SplitTable NumRows:=nbUnderlyings, NumColumns:=4, MergeBeforeSplit:=False


'Need to select table
wrdDoc.bookmarks("FundTicker").Select
wrdDoc.Selection.MoveDown Unit:=wdLine, Count:=nbUnderlyings, Extend:=wdExtend
wrdDoc.Selection.MoveRight Unit:=wdCharacter, Count:=3, Extend:=wdExtend
wrdDoc.Selection.SelectCell
wrdDoc.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
wrdDoc.Selection.Cells.VerticalAlignment = wdCellAlignVerticalCenter
With Selection.Cells
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderHorizontal)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderVertical)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
End With
' With Options
' .DefaultBorderLineStyle = wdLineStyleSingle
' .DefaultBorderLineWidth = wdLineWidth050pt
' .DefaultBorderColor = wdColorAutomatic
' End With

End Sub

Kenneth Hobs
11-01-2012, 01:45 PM
Selection is in MSWord and Excel.

The first line is the Sub. Why that should be a problem, I don't know. I would guess that you defined the MSWord document object as a Public variable earlier.

You can record a macro in MSWord but to use it in Excel, you need to reference the MSWord object and to use that object.

e.g.
'FindReplace Text
' http://www.excelforum.com/excel-programming/682014-replace-word-in-ms-word-with-varable-from-ms-excel.html
' http://www.vbaexpress.com/forum/showthread.php?t=38958
' http://www.vbaexpress.com/forum/showthread.php?p=250215
' http://www.vbaexpress.com/forum/showthread.php?t=42833
' http://support.microsoft.com/kb/240157
' http://word.tips.net/T001833_Generating_a_Count_of_Word_Occurrences.html

' http://www.excelforum.com/excel-programming/794297-struggling-with-a-find-replace-macro-to-word.html

'Bookmarks
' http://vbaexpress.com/forum/showthread.php?p=185718
'Colin_L, http://www.mrexcel.com/forum/showthread.php?t=358054
' http://www.vbaexpress.com/forum/showthread.php?p=253277

snb
11-01-2012, 02:39 PM
sub snb()
with getobject("G:\OF\example.doc")
For Each bd In .Tables(1).Borders
With bd
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth075pt
.Color = -553582593
End With
Next
end with
end sub


Avoid 'selection' in VBA
Avoid 'bookmarks' in VBA: you can refer to any point in a document, using paragraphs, ranges, lines, cells, characters, etc.
If you want information transfer you can use documentvariables instead of 'bookmarks'.

JPL007
11-02-2012, 01:29 AM
Thank you both for your reply.

could you please tell me how to split a cell in word:
The below is not working. (Ken wrdDoc is indeed declared as a public object)


wrdDoc.Cells.Split NumRows:=nbUnderlyings, NumColumns:=4, MergeBeforeSplit:=False


Kind regards

snb
11-02-2012, 02:56 AM
What are you trying to accomplish in Word ?

PS. If you use the With...End with method you don't have to worry about declarations.

JPL007
11-02-2012, 03:45 AM
Hi Snb

From Excel I am creating a word document.
This document contains 3 tables.
I am trying to access a cell in one specific table, and split this cell with 4 columns, and a variable number of rows.

Kind regards

snb
11-02-2012, 03:57 AM
If I understand you correctly: you want to create a 'nested table' in 1 cell of one of the tables in the Word document ?

JPL007
11-02-2012, 04:05 AM
Hi Snb

Yes you understood correctly.

JPL007
11-02-2012, 04:06 AM
The nested table needs to fit the border of the cell.
Splitting the cell is the way I found to do this, so that it still looks good.

snb
11-02-2012, 03:03 PM
This is what produced the desired result.

Sub snb()
With getobject("G:\OF\example.doc")
For Each bd In .Tables(1).Borders
With bd
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth075pt
.Color = -553582593
End With
Next
.tables(1).cell(5, 1).Split 6, 4
End With
End Sub

JPL007
11-05-2012, 03:45 AM
Thank you Snb, the split function works wonder.

Now how do I do border to a certain set of cells?

I am trying

With Range(wrdDoc.tables(1).Cells(9, 2), wrdDoc.tables(1).Cells(8 + nbUnderlyings, 2))


to adapt your code, but it does not seem to work.

thanks for your help

snb
11-05-2012, 04:00 AM
sub M_snb()


with Getobject("G:\OF\example.doc")

.application.Range(.tables(1).Cells(9, 2), .tables(1).Cells(8 + nbUnderlyings, 2)).borders(1).linestyle= .....
end with
end sub









- check in the VBEditor's helpfiles what With ... End with means
- you can avoid object variables
- check the difference between Application.Range and .paragraphs(1).range in Word.

JPL007
11-05-2012, 04:11 AM
I tried the below but it does not seem to work.
could you please let me know what I did wrong?

With wrdDoc
.Application.Range(.tables(1).Cells(9, 2), .tables(1).Cells(8 + nbUnderlyings, 2)).Borders(1).LineStyle = wdLineStyleSingle
end with

snb
11-05-2012, 04:20 AM
You didn't use the code I posted.

I can't 'see' what wrddoc is, nor the value of 'nbunderlyings', nor whether the document contains any table, not the size of that table (if it exists).
This is too much of a riddle....

Please post a sample document.

JPL007
11-05-2012, 12:00 PM
Hi Snb

sorry for the riddle, but thank you for your help.

Please find the concerned document.

nbUnderlyings is a number strictly bigger than 1.
WrdDoc is the name of the active word. and is defined as :
WDoc = thePath & myDoc & ".doc"
Set wrdDoc = WordApp.Documents.Open(WDoc)