none
Specify No Spacing when creating Word document from Excel RRS feed

  • Question

  • I have this code coped from the internet that creates a word document from the contents of the active worksheet. Works fine, but the results are "Normal Spaced". I want No Spacing. I tinkered with the linespacing and linespacingrule properties, but I must not be using them correctly. Any help:

        Set ws = ActiveSheet
        ws.UsedRange.Copy
        wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
        wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
        Application.CutCopyMode = False
        wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
        If Not ws.Name = Worksheets(Worksheets.Count).Name Then
          With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
              .InsertParagraphBefore
              .Collapse Direction:=wdCollapseEnd
              .InsertBreak Type:=wdPageBreak
          End With
        End If
        
        With wdApp.ActiveWindow
            If .View.SplitSpecial = wdPaneNone Then
                .ActivePane.View.Type = wdNormalView
            Else
                .View.Type = wdNormalView
            End If
        End With
        Set wdDoc = Nothing
        wdApp.Visible = True
        Set wdApp = Nothing

    Tod

    Thursday, February 20, 2020 6:26 PM

Answers

  • It's not apparent what you mean by 'No Spacing' vs 'Normal Spaced'. Presumably it has something to do with line spacing and/or paragraph before/after spacing. Please specify.

    Ideally, you'd create a template containing a paragraph Style with the appropriate characteristics, then specify that template when creating the new document and applying that Style to the pasted table.

    That said, the following code (which is simpler than what you're using) should get you out of your present bind:

    Set ws = ActiveSheet
    With wdApp
      .Visible = True
      Set wdDoc = .Documents.Add
      With wdDoc.Range
        .InsertAfter vbCr
        ws.UsedRange.Copy
        .Characters.Last.Paste
        With .Tables(.Tables.Count).Range.ParagraphFormat
          .LineSpacingRule = wdLineSpaceSingle
          .SpaceBefore = 0
          .SpaceAfter = 0
        End With
        If Not ws.Name = Worksheets(Worksheets.Count).Name Then .InsertAfter Chr(12)
      End With
    End With
    Set wdDoc = Nothing: Set wdApp = Nothing: Set ws = Nothing


    Cheers
    Paul Edstein
    [MS MVP - Word]


    • Edited by macropodMVP Friday, February 21, 2020 12:01 AM
    • Marked as answer by todtown Monday, February 24, 2020 1:00 PM
    Friday, February 21, 2020 12:01 AM

All replies

  • It's not apparent what you mean by 'No Spacing' vs 'Normal Spaced'. Presumably it has something to do with line spacing and/or paragraph before/after spacing. Please specify.

    Ideally, you'd create a template containing a paragraph Style with the appropriate characteristics, then specify that template when creating the new document and applying that Style to the pasted table.

    That said, the following code (which is simpler than what you're using) should get you out of your present bind:

    Set ws = ActiveSheet
    With wdApp
      .Visible = True
      Set wdDoc = .Documents.Add
      With wdDoc.Range
        .InsertAfter vbCr
        ws.UsedRange.Copy
        .Characters.Last.Paste
        With .Tables(.Tables.Count).Range.ParagraphFormat
          .LineSpacingRule = wdLineSpaceSingle
          .SpaceBefore = 0
          .SpaceAfter = 0
        End With
        If Not ws.Name = Worksheets(Worksheets.Count).Name Then .InsertAfter Chr(12)
      End With
    End With
    Set wdDoc = Nothing: Set wdApp = Nothing: Set ws = Nothing


    Cheers
    Paul Edstein
    [MS MVP - Word]


    • Edited by macropodMVP Friday, February 21, 2020 12:01 AM
    • Marked as answer by todtown Monday, February 24, 2020 1:00 PM
    Friday, February 21, 2020 12:01 AM
  • Thank you. You addressed the line spacing issue. Also thanx for cleaning up my code. 
    Monday, February 24, 2020 1:00 PM