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?
[vba]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[/vba]
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
Reply With Quote
