Pasting to Word Document

[twckfa16]

Hello -

I ran into an issue with the below - basically the goal is to copy whats on the sheet
and paste into a word document (need to keep the same formatting and to paste NOT as a table)
and fix the spacing before and after to be 0.
What its doing now is pasting in the Word document as a table and the spacing options
are not working for me. I tried pasting as Plain text but lost the
boldness I had already. Any help would be appreciated.

Dim strFile As String
Dim word_app As Word.Application
Set word_app = New Word.Application

With word_app
 .Visible = True
 .Activate
 .Documents.Add


'Range("A1", Range("A1").SpecialCells(xlCellTypeLastCell)).Copy
Worksheets("Sheet3").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
    Selection.Rows.AutoFit
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Rows.AutoFit
    Selection.Columns.AutoFit
Columns("A:A").ColumnWidth = 100
Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Copy

With .Selection
  .Paragraphs.Alignment = wdAlignParagraphLeft
  .Paragraphs.SpaceAfter = 0
  .Paragraphs.SpaceBefore = 0
  .Font.Size = 10
  .Font.Name = "Calibri"
End With

.Selection.Paste



SaveAsName = Environ("test") & "C:temp\file" & Format(now, "yyyy-mm-dd") & ".docx"
.ActiveDocument.SaveAs2 SaveAsName
.ActiveDocument.Close
.Quit
End With

strFile = SaveAsName

Thank you !

[6StringJazzer]

need to keep the same formatting and to paste NOT as a table

Can you explain what you mean by this? Excel ranges are pasted into Word as tables. The only other options are:

Paste as plain text
Paste as an Excel object
Paste as a picture

Can you provide a Word file that shows what result you want?

[twckfa16]

Hello - thank you for answering!

I will attach a couple of files....the desired result photo may have to be zoomed in on...basically what what is in the photo minus the grid. Bear in mind, the photo is how
it looks in my phone. I need the lines that are bold to stay bold etc.

Thank you -

Mark

ps - may have to attach files again = hope this works

[6StringJazzer]

The Word file you attached does have a table. The table has no borders. Is that what you want? This removes the borders:

   .Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
   .Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
   .Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
   .Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
   .Selection.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
   .Selection.Borders(wdBorderVertical).LineStyle = wdLineStyleNone

The other option is to paste then convert the table to text.

.Selection.Rows.ConvertToText Separator:=wdSeparateByTabs

[twckfa16]

Hi 6 string - I am trying what you suggested but not sure if I am making a mistake with it. Here is the code I have:

Dim strFile As String
Dim wdApp As Word.Application
Set wdApp = New Word.Application

With wdApp
 .Visible = True
 .Activate
 .Documents.Add


'Range("A1", Range("A1").SpecialCells(xlCellTypeLastCell)).Copy
Worksheets("Exercises for week").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
    Selection.Rows.AutoFit
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Rows.AutoFit
    Selection.Columns.AutoFit
Columns("A:A").ColumnWidth = 100
Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
.Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
.Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
.Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
.Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
.Selection.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
.Selection.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
.Selection.Paragraphs.Alignment = wdAlignParagraphLeft
.Selection.Paragraphs.SpaceBefore = 0
.Selection.Paragraphs.SpaceAfter = 0

Range(Selection, Selection.End(xlDown)).Copy

'.Selection.Paste
'.Selection.Rows.ConvertToText (Seperator = wdSeperateByTabs


.Selection.Paste

Commented out was your second suggestion - again maybe I'm doing it incorrectly.

I attached what was generated from the code above and a sample word document of the desired result.

Thank you for your help!

[6StringJazzer]

Maybe give us the Excel file so we can run the code.

[twckfa16]

That makes sense - I will get the file together (will taylor to make
It simple )

Thank you !

[6StringJazzer]

You have to remove the borders after the paste.

This worked for me. It creates a table, but there are no borders. You seem to care just about not showing the gridlines.

Option Explicit

Public Sub ToWord()


   Dim strFile As String
   Dim wdApp As Word.Application
   Set wdApp = New Word.Application
   
   With wdApp
      .Visible = True
      .Activate
      .Documents.Add
      
      
      'Range("A1", Range("A1").SpecialCells(xlCellTypeLastCell)).Copy
      Worksheets("Exercises for week").Activate
      Range("A1").Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Rows.AutoFit
      With Selection
         .HorizontalAlignment = xlGeneral
         .VerticalAlignment = xlTop
         .Orientation = 0
         .AddIndent = False
         .IndentLevel = 0
         .ShrinkToFit = False
         .ReadingOrder = xlContext
         .MergeCells = False
      End With
      Selection.Rows.AutoFit
      Selection.Columns.AutoFit
      Columns("A:A").ColumnWidth = 100
      Range("A1").Select
      Range(Selection, Selection.End(xlDown)).Select
      With Selection
         .HorizontalAlignment = xlGeneral
         .VerticalAlignment = xlTop
         .WrapText = True
         .Orientation = 0
         .AddIndent = False
         .IndentLevel = 0
         .ShrinkToFit = False
         .ReadingOrder = xlContext
         .MergeCells = False
      End With
      
      Range("A1").Select
      Range(Selection, Selection.End(xlDown)).Select
      
      Range(Selection, Selection.End(xlDown)).Copy
      
      '.Selection.Paste
      '.Selection.Rows.ConvertToText (Seperator = wdSeperateByTabs
      
      
      .Selection.Paste
      
      .Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
      .Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
      .Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
      .Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
      .Selection.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
      .Selection.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
      .Selection.Paragraphs.Alignment = wdAlignParagraphLeft
      .Selection.Paragraphs.SpaceBefore = 0
      .Selection.Paragraphs.SpaceAfter = 0
   
   End With

   Set WordApp = Nothing

End Sub

[twckfa16]

Thank you ! Wil try out later today and let you know
how I made out.

[twckfa16]

Hi 6String -

Apologies for the delay - I copied your code verbatim and ran it. This is what I got. I'm not sure
what is going on. I'll attach all 3 files - also took a pic of what it looked like
when opening from my phone. I'll keep at it

Thanks!

[6StringJazzer]

Here's my test version.

[twckfa16]

I ran your twckfa16 file and sent it to my phone - and I did a Word doc by just typing manually.
I think it just might be the way Excel is copied into Word. Attached are snapshots
of both

Thanks!

[6StringJazzer]

I ran your twckfa16 file and sent it to my phone

What device are you running the code on? What does the resulting Word file look like on the same device where you run the code? How are you sending it to your phone, and how are you viewing it on your phone?

[AliGW]

I don't believe that you are still using Excel 2007 if you are using it on your phone - please update your forum profile to whatever you are currently using. Thanks.

[twckfa16]

Hi all - sorry this one got away from me for a few days

AliGW - I updated my profile to Excel 2016

6string - its OK the way it is - just trying out how to get rid of the grid - if it isnt possible its OK

What device are you running the code on? Running it on my laptop which has Excel 2016
What does the resulting Word file look like on the same device where you run the code?

It looks like the file you sent me (may be attached here - ef_1

How are you sending it to your phone - sending to my phone using this code

           Dim qt As String
           Dim email_app As Outlook.Application
           Dim mailitem As Outlook.mailitem
           Dim src As String
           Set email_app = New Outlook.Application
           Set mailitem = email_app.CreateItem(olMailItem)
           mailitem.To = "myemail.com"
           mailitem.Subject = "Test email"
           mailitem.Attachments.Add strFile
           mailitem.Send

How are you viewing it on your phone? - I am viewing the file in the Files folder on my phone


Thanks !

Mark

[6StringJazzer]

The grid is there in Word because it's a table, but no visible gridlines. You won't see the gridlines if you are viewing it in Word, or if you print it.

So you are emailing it to your phone as an attachment. (strFile does not seem to be defined.)

I don't know what "in the Files folder on my phone" means. I was wondering what app you are using. If you are not using Word, and instead some other viewer, it may show the gridlines that Word does not show.

It should be possible to add code to convert the table to text without losing formatting. I'll take a look when time permits.

[6StringJazzer]

This will convert a table to text while preserving the formatting

   Selection.Rows.ConvertToText Separator:=wdSeparateByTabs, NestedTables:= True