CODEWITHSUNDEEP
vbaexcelms-word
gNerb
gNerb

Reputation: 887

MS Word VBA: Controlling excel workbook from word - Object required error

I'm creating a MACRO in MS Word that needs to be able to (basically) copy and paste the contents of a table in word into excel.

DISCLAIMER: This might seem like an over complication; however, the approach is required as it is a setup for more complicated processing.

Long story short, I loop through every table in the document, and then every cell in the table and place the text into a corresponding cell in an excel sheet.

I have these declarations for excel objects:

'Objects
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlRange As Excel.Range

At the bottom of my loops, I have the following code:

xlBook.Worksheets(x).Activate
Set xlRange = xlBook.ActiveSheet.Range(Chr(65 + x) & y)
xlRange.Text = tString

The last line is throwing an "object required" error. The variable tstring is defined as a string and is set earlier in the loop.

The full code:

Sub CopyTablesToExcel()
    'Constants
    Const COLUMN_INDEX = 1
    Const ROW_INDEX = 2

    'Ints
    Dim x As Integer, y As Integer, z As Integer 'Counters
    Dim numTables As Integer 'Number of tables in the word file
    Dim numSheets As Integer 'Number of sheets in the excel file
    Dim LastCell(1 To 2) As Integer   'Used to keep track of the last cell of a newly created excel table
    Dim map() As Integer 'Holds a map of the table columns

    'strings
    Dim xlBookName As String 'Name of the excel workbook
    Dim tString As String 'Temporary string

    'Objects
    Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlRange As Excel.Range

    'Initialize
    Set xlBook = xlApp.Workbooks.Add
    numSheets = xlBook.Worksheets.count
    numTables = ActiveDocument.Tables.count

    'Save the new book
    xlBookName = InputBox("Enter the ticker symbol:")
    xlBook.SaveAs FileName:=xlBookName

    'Show the file?
    xlApp.Visible = True

    'Make sure there are enough sheets
    If numSheets < numTables Then
        For x = 1 To (numTables - numSheets)
            xlBook.Worksheets.Add
            numSheets = numSheets + 1
        Next
    End If

    'Cycle through every table in the document and paste it to the worksheet
    For z = 1 To numTables 'Cycle through tables
        'Keep track of the last cell in the table
        LastCell(COLUMN_INDEX) = ActiveDocument.Tables(z).Columns.count
        LastCell(ROW_INDEX) = ActiveDocument.Tables(z).rows.count

        For x = ActiveDocument.Tables(z).rows(ActiveDocument.Tables(z).rows.count).Cells.count To 1 Step -1 'Cycle through columns
            'Used selections to support horizontally merged cells
            ActiveDocument.Tables(z).rows(ActiveDocument.Tables(z).rows.count).Cells(x).Select
            Selection.SelectColumn
            For y = Selection.Cells.count To 1 Step -1 'Cycle through cells
                tString = Selection.Cells(y).Range.Text

                'Move the content into excel
                xlBook.Worksheets(x).Activate
                Set xlRange = xlBook.ActiveSheet.Range(Chr(65 + x) & y)
                Debug.Print Chr(65 + x) & y 'Prints as expected
                xlRange.Text = tString
            Next
        Next
    Next
End Sub

I believe this is happening because the MACRO is failing to set the xlRange correctly. The output from debug.print is correct and is the format of "A#".

EDIT: 

If Not xlRange Is Nothing Then
    xlRange.Text = tString 'Still errors
End If

The above will evaluate to true but still throws the error at the marked line

Upvotes: 0

Views: 1161

Answers (1)

David Zemens
David Zemens

Reputation: 53623

I see two things:

.Text is a read only property. I would expect an an "Unable to set the text property of range object" error on this line:

xlRange.Text = tString

Change to:

xlRange.Value = tString

Also, your range assignment is probably wrong. I don't know why you are doing CHR(65) instead of simply "A", but the problem is this line:

Set xlRange = xlBook.ActiveSheet.Range(Chr(65 + x) & y)

Here you are ADDING x to 65, and then the Chr function returns whatever result that is, which could raise an error. A value of x that is greater than 25 will most likely raise an error becasue then Chr(65 + x) does not evaluate to a valid range address.

Since you clarify in the comments that you do intend to do this (e.g., "A" + 1 = "B", etc.), it would be better probably to do this, if for no other reason than it seems more legible and leverages the Excel object model in a less-ambiguous manner:

Set xlRange = xlBook.ActiveSheet.Range("A" & y).Offset(,x)

Upvotes: 2

Related Questions