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