VBA: Reading from Excel & Writing to Word

  • Can anyone give me a simple example of how to read text/data from an excel file in VBA? I only need data from a few selected cells.

    Also, what is the newline/carriage return command when writing text to Word in VBA? I know is C++ it's \n or something. Thanks in advance.

  • Here is a macro that contains enough examples that I think you can get what you need. (It also creates a new Word file.)

    Sub MakeWordFile()
    ' Creates Word document of Auction Items using Automation
    Dim WordApp As Object

    ' Start Word and create an object
    Set WordApp = CreateObject("Word.Application")
    With WordApp
    End With

    ' Determine the file name
    SaveAsName = ThisWorkbook.Path & "" & "Auction Catalog.doc"

    ' Sort Worksheet into proper catalog order
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
    Key2:=Range("B2"), Order2:=xlAscending, _
    Key1:=Range("C2"), Order3:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    ' Information from worksheet
    Set Data = Sheets("Items").Range("A1")

    ' Cycle through all records in Items
    Records = Application.CountA(Sheets("Items").Range("Title"))
    ' Records = 7

    For i = 2 To Records
    ' Update status bar progress message
    Application.StatusBar = "Processing Record " & i & " of " & Records

    ' Assign current data to variables
    Item1 = Data.Offset(i - 1, 0).Value
    Item2 = Data.Offset(i - 1, 1).Value
    Title = Data.Offset(i - 1, 2).Value
    Descript = Data.Offset(i - 1, 3).Value

    ' Send commands to Word
    With WordApp
    With .Selection
    .Font.Size = 12
    .Font.Bold = True
    .ParagraphFormat.Alignment = 0
    .TypeText Text:=Item1 & Item2 & ". "
    .Font.Underline = True
    .TypeText Text:=Title
    .Font.Bold = False
    .Font.Underline = False
    .TypeText Text:=Descript
    End With
    End With
    Next i

    ' Save the Word file and close it
    With WordApp
    .ActiveDocument.SaveAs FileName:=SaveAsName
    ' Kill the object
    End With

    Set WordApp = Nothing

    ' Reset status bar
    Application.StatusBar = ""
    MsgBox "Auction Catalog.doc was created and saved in " & ThisWorkbook.Path
    End Sub

  • thomach, your codes helped me out a bit but I have another questions. If I'm in a MSword macro, how do I open an Excel file and copy a selection over to MSword? Or at least assign it to a variable in MSword macro?

  • I'm afraid I'v never worked from the MS Word VBA side. I would assume it's similar, but obviously don't know.

    But remember that once you have opened the Excel file and are working in it you need to use Excel VBA commands, just as you need to use the WORD-specific commands while working in the Word document.

    Hopefully someone else can give you a real answer.

    Is VBA HELP in WORD of any use?

  • Thanks for all your help thomach. Anybody worked with MS Word VBA that can give me some pointers? Any other sites that might provide me with an answer?

Participate now!

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