Consulting

Results 1 to 3 of 3

Thread: Transfering Data To Word

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Transfering Data To Word

    Hello,

    Im hoping someone can help me out ...My coding is below and it partially works; when you run the macro in Excel, the script opens a Word document but does not transfer what is in the Excel sheet to the word document...

    The error I get an error that says, 'Sub-Script' out of range..It doesn't look to me like any of it is out of range...but maybe Im missing something...Does anyone see anything?

    Option Explicit
    
    Public Sub TransferData()
        ' This macro transfers the data range "A1:E11" to a table in Word
        ' Constants:
        ' docFullName = The full name of an already existing Word document
        ' Variables:
        ' doc = The Word document (assumed to be empty)
        ' i = A counter (for rows)
        ' j = A counter (for columns)
        ' tbl = A Word table
        ' wdRng = A Word range (the first paragraph of doc)
        ' wks = The worksheet "data" that contains the data range
        ' Const docFullName = "C:\OLE Automation\Word.doc" '
        Dim doc As Object
        Dim i As Long
        Dim j As Long
        Dim tbl As Object
        Dim wdApp As Object 'Only if you require a new document each time
        Dim wdRng As Object
        Dim wks As Worksheet
        ' Assing Word objects 'Only if you require a new document each time
        Set wdApp = CreateObject("Word.Application")
        wdApp.Visible = True
        Set doc = wdApp.Documents.Add
        ' Assign variables and objects
        ' Set doc = GetObject(docFullName) 'Only if you want a specific document
        Set wdRng = doc.Paragraphs(1).Range
        Set tbl = doc.Tables.Add(wdRng, 11, 5)
        Set wks = ThisWorkbook.Worksheets("data")
        ' Transfer the data
        With tbl
            For i = 1 To 11
                For j = 1 To 5
                    .Cell(i, j) = wks.Cells(i, j)
                Next j
            Next i
        End With
        ' Save and close doc 'Only if you want a specific document
        ' Call doc.Save
        ' Call doc.Close(False)
        ' Clean
        Set doc = Nothing
        Set wks = Nothing
    End Sub
    And I have data in cells A1:E11 - So I was thinking that could be the issue but I double checked that and the data is there
    Last edited by Aussiebear; Yesterday at 04:33 PM.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,954
    Location
    First off, welcome to the forum!

    Most likely you don't have a worksheet named "Data" in the workbook with the code.

  3. #3
    haha - ahh what a silly mistake!

    Thanks for the help!~

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •