asked on

VBA Word Array, to Excel file

VBA Word Array, to Excel file
Hi guys lets say I have an array in word VBA of type:

MyArr ( Row, Column )

(Where Row and Column are Excel’s Sheet Row and Column)

I just need to export it to Excel (or excel file format) from a word VBA macro.

Good Points are given for this question....

Take care

GrahamSkan

You will need something like this.

Sub ArrayExcel()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim r As Integer
    Dim c As Integer
    
    Set xlApp = New Excel.Application
    Set xlWbk = xlApp.Workbooks.Add
    xlApp.Visible = True
    Set xlWks = xlWbk.Sheets(1)
    For Each ffld In ActiveDocument.FormFields
        xlWks.Cells(r, c).Value = MyArr(r, c)
        c = c + 1
    Next ffld
    'xlWbk.SaveAs ....
    'xlWbk.Close
    'xlApp.Quit
End Sub

Open in new window

GrahamSkan

Oops. That was the unfinished version that I copied by mistake

Sub ArrayExcel2()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim r As Integer
    Dim c As Integer
    
    Set xlApp = New Excel.Application
    Set xlWbk = xlApp.Workbooks.Add
    xlApp.Visible = True
    Set xlWks = xlWbk.Sheets(1)
    For r = 0 To UBound(MyArr, 1)
        For c = 0 To UBound(MyArr, 2)
            xlWks.Cells(r + 1, c + 1).Value = MyArr(r, c)
        Next c
    Next r
    
    'xlWbk.SaveAs ....
    'xlWbk.Close
    'xlApp.Quit
End Sub

Open in new window

New_Alex

ASKER

Hey,

Like said, I would like to have that run in Microsoft Word VBA environment. Because the array comes from word document.

Any way to make the above code work in MS word VBA?

Thanks Friends

Love !

New_Alex

ASKER

Hi Guys, at least if I manage to transfer the variable to Excel VBA and then run an Excel VBA Macro?

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

New_Alex

ASKER

Even if it answers the question, it seems that there are not any better answers than this. The solution needs excel to run and it does not export directly to file. It also needs extra reference settings.

I prefer to export in .xml format !