Copying from Excel to Word

  • I am copying data from Excel to Word using VBA and I have noticed that special characters (Subscripts, superscripts, Greek symbols, etc) lose their formatting when they are pasted into Word. Direct cut-and-paste though works fine. Is there some way to keep the formatting when using VBA?

  • HI


    Another script that will assist you there are many ways this will create an object [Word session] and copy over the range of Excels data into word, a useful method.


    Hope this helps you get started.


    Kindest possible regards


    Jack in the Uk


    Code:


    Sub Excel_to_Word_JackintheUK()

    Range("A1:A20").Select
    ‘ Amend range to suite you requirements


    Selection.Copy

    Set Word6 = CreateObject("Word.Basic")
    With Word6
    If UCase(Left(Application.OperatingSystem, 3)) <> "MAC" Then
    .AppRestore
    .AppMaximize 1
    Else
    AppActivate "Microsoft Word"
    End If
    .FileNewDefault
    '.InsertPara
    .Insert "Jack - Test from Excel"
    .startofline 15
    ' .Bold
    ' .CenterPara
    '.startofline 15
    Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _
    skipblanks:=False, Transpose:=False
    Application.CutCopyMode = False

    End With



    End Sub

  • i dont know what VBA is, but i copy from excel to text then from text to word, then i select 2 columns giving me equivalent to 2 pages of excel to 1 page in word.


    i also understand that my point may mean nothing :)


    but i just wanted to reply to something :bsmile:

  • Hi,


    Following procedure will
    - create a new word-document
    - copy & paste the data in cellrange E1:E20 with all formatting to the new document
    - Save the Word-document
    - Close Word
    - Tell the world about it :)


    Sub Copy_Keep_Formatting()
    Dim wApp As Object
    Dim wDoc As Object
    Dim rnValue As Range


    With ActiveSheet
    Set rnValue = .Range("E1:E20")
    End With


    Set wApp = CreateObject("Word.Application")
    Set wDoc = wApp.Documents.Add()

    With wDoc
    rnValue.Copy
    .Content.Paste
    Application.CutCopyMode = False
    .SaveAs Filename:=ThisWorkbook.Path & "\OzGrid.doc"
    .Close
    End With


    wApp.Quit


    Set wDoc = Nothing
    Set wApp = Nothing


    MsgBox "All data copied to the document", vbInformation
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!