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


    Sub Excel_to_Word_JackintheUK()

    ‘ Amend range to suite you requirements


    Set Word6 = CreateObject("Word.Basic")
    With Word6
    If UCase(Left(Application.OperatingSystem, 3)) <> "MAC" Then
    .AppMaximize 1
    AppActivate "Microsoft Word"
    End If
    .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
    Application.CutCopyMode = False
    .SaveAs Filename:=ThisWorkbook.Path & "\OzGrid.doc"
    End With


    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!