Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-12-2019, 08:34 AM
sts023 sts023 is offline Problems formatting a Word 2010 table using VBA Windows 7 64bit Problems formatting a Word 2010 table using VBA Office 2010
Hopeless Idiot
Problems formatting a Word 2010 table using VBA
 
Join Date: Apr 2019
Location: God's Own County
Posts: 26
sts023 is on a distinguished road
Default 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
When I inserted the line into my code, I get an error.
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)
The last line generates the following error:

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....
Reply With Quote
  #2  
Old 05-12-2019, 09:36 AM
eduzs eduzs is offline Problems formatting a Word 2010 table using VBA Windows 10 Problems formatting a Word 2010 table using VBA Office 2010 32bit
Expert
 
Join Date: May 2017
Posts: 260
eduzs is on a distinguished road
Default

It's easy, you're trying to set line spacing at 394656 points, and the maximum is 1584 points.


Quote:
Selection.ParagraphFormat.LineSpacing = 1584
Reply With Quote
  #3  
Old 05-12-2019, 10:36 AM
sts023 sts023 is offline Problems formatting a Word 2010 table using VBA Windows 7 64bit Problems formatting a Word 2010 table using VBA Office 2010
Hopeless Idiot
Problems formatting a Word 2010 table using VBA
 
Join Date: Apr 2019
Location: God's Own County
Posts: 26
sts023 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 05-12-2019, 11:05 AM
eduzs eduzs is offline Problems formatting a Word 2010 table using VBA Windows 10 Problems formatting a Word 2010 table using VBA Office 2010 32bit
Expert
 
Join Date: May 2017
Posts: 260
eduzs is on a distinguished road
Default

macro recorder is not a good option to complex coding. It's better to code directly what you want word to do.
Quote:
Originally Posted by sts023 View Post
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.

Last edited by eduzs; 05-12-2019 at 05:21 PM.
Reply With Quote
  #5  
Old 05-12-2019, 11:21 AM
sts023 sts023 is offline Problems formatting a Word 2010 table using VBA Windows 7 64bit Problems formatting a Word 2010 table using VBA Office 2010
Hopeless Idiot
Problems formatting a Word 2010 table using VBA
 
Join Date: Apr 2019
Location: God's Own County
Posts: 26
sts023 is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 05-12-2019, 11:31 AM
eduzs eduzs is offline Problems formatting a Word 2010 table using VBA Windows 10 Problems formatting a Word 2010 table using VBA Office 2010 32bit
Expert
 
Join Date: May 2017
Posts: 260
eduzs is on a distinguished road
Default

Can you tell us exactly what you want Word to do?
Reply With Quote
  #7  
Old 05-12-2019, 12:37 PM
Charles Kenyon Charles Kenyon is offline Problems formatting a Word 2010 table using VBA Windows 10 Problems formatting a Word 2010 table using VBA Office 2016
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,081
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Quote:
Originally Posted by sts023 View Post
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?

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.
Reply With Quote
  #8  
Old 05-12-2019, 12:42 PM
sts023 sts023 is offline Problems formatting a Word 2010 table using VBA Windows 7 64bit Problems formatting a Word 2010 table using VBA Office 2010
Hopeless Idiot
Problems formatting a Word 2010 table using VBA
 
Join Date: Apr 2019
Location: God's Own County
Posts: 26
sts023 is on a distinguished road
Default

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.
Attached Images
File Type: jpg Pre.jpg (19.6 KB, 19 views)
File Type: jpg Post.jpg (22.5 KB, 19 views)
Reply With Quote
  #9  
Old 05-12-2019, 03:53 PM
eduzs eduzs is offline Problems formatting a Word 2010 table using VBA Windows 10 Problems formatting a Word 2010 table using VBA Office 2010 32bit
Expert
 
Join Date: May 2017
Posts: 260
eduzs is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 05-12-2019, 05:10 PM
macropod's Avatar
macropod macropod is offline Problems formatting a Word 2010 table using VBA Windows 7 64bit Problems formatting a Word 2010 table using VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by sts023 View Post
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.
Whatever the macro recorder records is VBA - and it's generally pretty reliable. Conversely, it's as dumb as a box of rocks and would be incapable of generating code as complex as that in your first post. There are also many actions it won't record.
Quote:
Originally Posted by sts023 View Post
Thats the problem - without the recorder I'm not well enough versed in Word VBA to know what the "native" commands are.
Time to get into the Word Object model and start learning.

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
Better than using '.ParagraphFormat.SpaceAfter = 0' would be to apply an appropriate paragraph Style to the table.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 05-13-2019, 03:52 AM
sts023 sts023 is offline Problems formatting a Word 2010 table using VBA Windows 7 64bit Problems formatting a Word 2010 table using VBA Office 2010
Hopeless Idiot
Problems formatting a Word 2010 table using VBA
 
Join Date: Apr 2019
Location: God's Own County
Posts: 26
sts023 is on a distinguished road
Default

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?)...
Reply With Quote
  #12  
Old 05-13-2019, 05:37 AM
macropod's Avatar
macropod macropod is offline Problems formatting a Word 2010 table using VBA Windows 7 64bit Problems formatting a Word 2010 table using VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by sts023 View Post
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!
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]
Reply With Quote
  #13  
Old 05-13-2019, 05:47 AM
sts023 sts023 is offline Problems formatting a Word 2010 table using VBA Windows 7 64bit Problems formatting a Word 2010 table using VBA Office 2010
Hopeless Idiot
Problems formatting a Word 2010 table using VBA
 
Join Date: Apr 2019
Location: God's Own County
Posts: 26
sts023 is on a distinguished road
Red face

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!....
Reply With Quote
Reply

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
Problems formatting a Word 2010 table using VBA 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
Problems formatting a Word 2010 table using VBA Spacing problems with word 2010 Microsoftenquirer1000 Word 5 06-25-2012 12:01 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:14 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft