• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

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
0
New_Alex
Asked:
New_Alex
  • 3
  • 3
1 Solution
 
GrahamSkanRetiredCommented:
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

0
 
GrahamSkanRetiredCommented:
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

0
 
New_AlexAuthor Commented:
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 !
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
New_AlexAuthor Commented:
Hi Guys, at least if I manage to transfer the variable to Excel VBA and then run an Excel VBA Macro?
0
 
GrahamSkanRetiredCommented:
The code is written in Word VBA for running there.
I did forget to mention that you will need a set a reference (Tools/References in the Word VBA editor) to the Microsoft Excel Object Library.

It would run in Excel, but this very slightly tweaked version uses current Workbook instead of creating a new one.
'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 = Application New Excel.Application
    Set xlWbk = ActiveWorkbook
    '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

Open in new window

0
 
New_AlexAuthor Commented:
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  !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now