#1
|
|||
|
|||
Problems formatting a Word 2010 table using VBA
Hi again guys.
(Win7 Word 2010 VBA) I’m creating a Word document using VBA, and I’m placing a table in the document. That works, but there’s too much space around the entries (i.e. the rows are too high). To work out how to code the VBA to correct this I recorded a macro whilst effecting actions which gave me the desired result. Because I can't select the table texts whilst recording the macro I selected the table and then started recording the actions. I pointed the cursor to Home/Line and Paragraph Spacing, clicked on “1.0", then “Remove Space After Paragraph”. The table looks perfect. The following macro was recorded Code:
Sub Macro1() ' ' Macro1 Macro ' ' Selection.ParagraphFormat.LineSpacing = LinesToPoints(32888) End Sub My code follows (N.B. "garsDiner" is a public global string array defined elsewhere) Code:
Public Sub InsertPermDinerArray() Dim lngCurCol As Long Dim lngCurRow As Long Dim lngDinerPtr As Long Dim lngMaxDiner As Long Dim lngTotCols As Long Dim lngTotRows As Long Dim rng As Range Dim tblDiner As Table '* '** Determine table size (two columns, '** so number of entries halved then '** rounded up). '* lngMaxDiner = UBound(garsDiner) + 1 lngTotRows = (lngMaxDiner + 0.5) / 2 lngTotCols = 2 '* '** Prepare to add at the end of the document. '* Set rng = ActiveDocument.Content rng.Collapse Direction:=wdCollapseEnd '* '** Insert the table. '* With ActiveDocument.Tables Set tblDiner = .Add(Range:=rng, _ numrows:=lngTotRows, _ numcolumns:=lngTotCols, _ AutoFitBehavior:=wdAutoFitContent) End With '* '** Now populate the table. '* lngCurCol = 1 lngCurRow = 1 With tblDiner For lngDinerPtr = LBound(garsDiner) To UBound(garsDiner) .Cell(lngCurRow, lngCurCol).Range.InsertAfter garsDiner(lngDinerPtr) lngCurRow = lngCurRow + 1 If lngCurRow > lngTotRows Then lngCurRow = 1 lngCurCol = lngCurCol + 1 End If Next lngDinerPtr End With tblDiner.Select ' Macro1 Macro recorded code. Selection.ParagraphFormat.LineSpacing = LinesToPoints(32888) Run-time error '5149': The measurement must be between 0.7 pt and 1584 pt. Does anyone know of a way to help me achieve my goal? All assistance gratefully received.... |
#2
|
|||
|
|||
It's easy, you're trying to set line spacing at 394656 points, and the maximum is 1584 points.
Quote:
|
#3
|
|||
|
|||
OK, I realise that - the issue is that I'm doing what works when the macro recorder is used, but it doesn't work when transferred to vba. I'm trying to find VBA to do what the macro recorder does when it is used to record my actions.
|
#4
|
|||
|
|||
macro recorder is not a good option to complex coding. It's better to code directly what you want word to do.
Last edited by eduzs; 05-12-2019 at 05:21 PM. |
#5
|
|||
|
|||
Thats the problem - without the recorder I'm not well enough versed in Word VBA to know what the "native" commands are.
If it were Excel I could probably make a better attempt, but it ain't, so I can't! Any ideas? |
#6
|
|||
|
|||
Can you tell us exactly what you want Word to do?
|
#7
|
|||
|
|||
Quote:
An idea: set the size to 15 points, instead. The macro recorder has always been a crutch. Sometimes it works and it can give you ideas. |
#8
|
|||
|
|||
See attached images.
"Pre.jpg" (1st image) shows the table as created by VBA. "Post.jpg" (2nd image) shows the result after using the Word ribbon's "Home/Paragraph/Paragraph Line and Spacing" to select "1.0" and "Remove Space After Paragraph". Note that as "Show Formatting" is ON, there are no carriage returns or line feeds at the end of the cell's text in either image. I want to use VBA to achieve programmatically what I get manually from the ribbon, i.e. the cell Height of each row in the table is reduced to fit the text, whilst the cells Width is unchanged. |
#9
|
|||
|
|||
You can try something like that:
Code:
activedocument.Tables(1).Range.ParagraphFormat.Spaceafter=0 activedocument.Tables(1).Range.ParagraphFormat.SpaceBefore=0 activedocument.Tables(1).Rows.HeightRule=wdRowHeightAuto |
#10
|
||||
|
||||
Quote:
Quote:
As for the immediate problem, edit your With tblDiner ... End with block by adding: Code:
With tblDiner ' ... .Range.ParagraphFormat.SpaceAfter = 0 .Rows.HeightRule = wdRowHeightAuto End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Thanks macropod - I know I need to investigate the Word object further - I rather stupidly assumed there would be more parallels with Excel in terms of methods of achieving a goal: I should have remembered it's a Microsoft offering!
Your suggested code works a treat - thanks for that. And thanks to all who replied (or considered replying) - it's nice to feel that I'm part of a community (or is it just that I've been in I.T. for too long?)... |
#12
|
||||
|
||||
The differences are because of the nature of each program rather than being something arbitrary. With Word, for example, cells in the same column of a table can have different widths - something that's impossible in Excel.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
Yes - I guess it's also something to do with terminology - I tend to think of a "cell" in Excel terms, and I'm still unfamiliar with some of the MSWord concepts like WholeStory, and I get wary of thinking of them in too "English" a way for fear of misleading myself.
I guess I'll just have to keep on learning, even though I retired 8 years ago. Still, they say it keeps you young!.... |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Does sending a W10 Word 2016 template to someone with a W7 Word 2010 system cause problems? | dianahbr | Word | 4 | 03-06-2018 02:27 PM |
Word 2011 for Mac - problems with table of contents | tepose | Word | 1 | 09-28-2014 03:36 PM |
Many Corruption Problems Word 2010 | sleake | Word | 2 | 07-25-2013 07:20 PM |
Copy & paste problems Word 2010 | pjm333 | Word | 2 | 02-13-2013 04:52 AM |
Spacing problems with word 2010 | Microsoftenquirer1000 | Word | 5 | 06-25-2012 12:01 PM |