Copy Array from Word VBA to Excel

[MikeZz]

I have a macro that scans through a customer document which is 99.99% air and
puts the relavent info into a 2-D array.

From my Word Macro, how do I open up a new excel file, paste the data from
the array into the file, do a little formatting in excel, and then stop the
macro?

If someone can point me to actual examples of code, that would be great!

I'm using Office 2003.

Thanks,

 

[Cindy Meister]

Hi Mike

Well, there's no way to "paste" an array in memory into anything...

1. Stream it to a file (see the general VB topics open, write, print, etc.
to create a delimited file, or use MSXML to generate XML), then open the file
in Excel

2. Automate Excel, call a macro using the Application.Run method and pass
the array as an argument

3. Automate Excel and write the contents of the array to a worksheet (just
like the Excel macro would, but all the code is in Word)

 

[stebain]

Hi Mike

Well, there's no way to "paste" an array in memory into anything...

There isn't a clipboard copy/paste?

[Unless he means an array of his own creation]

 

[MikeZz]

Hi Cindy,
I take it these are 3 options.
My dilema is that I want to pass my code on to other people and have it be
as simple as possible. If I ask them to copy all these macros in both Word
and Excel into their personal workbook, it will never work.

Isn't there a way to control Excel from my word VBA and tell it to use a
Paste.range command?

Or, what would work best, is to translate my Word macros into an excel file
and have Excel VBA perform all the operations on the Word application.

Can you provide some examples on how to control a word document from Excel?
I'm thinking something along these lines:
I have the user open both the excel file with the VBA and the "subject" Word
File.
Have VBA find the open Word file and use my code to search through it and
create the array of info.
Then use Excel VBA to dump the data into the excel file.

Scrolling through my VBA, I have 3 basic Word commands I would have to do
from Excel:
'WORD COMMAND #1: Finds Keyword Text in the Word Document:
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Product/DRD FAM"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute
'WORD COMMAND #2: Tab over "X" amount of times to get the data I need
'Note that when you use this command, Word automatically selects all the
data in the cell I tabbed to which is what I need to do.
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell

'WORD COMMAND #3: I use this code a number of times as well
Dim myRange As Word.Range
Set myRange = ActiveDocument.Range
count = 0
With myRange.Find
..Text = search$
..MatchWholeWord = True
While .Execute
count = count + 1
myRange.Collapse Direction:=wdCollapseEnd
of file
Wend
End With

Or, if I dump my array to a comma dilaminated text file, can I have excel
open the text file from within the Word VBA?

Thanks,

 

[Cindy Meister]

Hi Mike

On the word.mvps.org website you'll find a couple of articles dealing with
automating Word from Excel, and vice-versa. Start with those and see how far
they take you... Then you can ask questions with a little less scope, that
are easier to answer without writing an entire book

On some of your points:

1. It may be best to have the user select the Word document from within your
application, so that you can control opening it, and thus have a "hold" on
what was opened. Things can get very tricky if many documents are open, and
your code needs to work with only a specific one. Letting the user pick a
file, then picking up that file path and opening the file through code lets
you generate a document object (instead of "activeDocument") to manipulate.
If everyone will be using Office XP or later, then the FileDialog object
would be ideal for this. If you have to accomodate earlier versions, as well,
then you should ask in the excel.programming newsgroup what interface they
recommend for letting the user pick a file path.

2. The routines you show can certainly be run from Excel VBA, using the
automation techniques described in the article. They aren't the "prettiest"
Word VBA, but if they work for you, that's fine You'll only need to watch
out for a couple of things, if the code is running from within Excel:
- Don't use "ActiveDocument". Instead, when opening the Word document (as
discussed above), assign it to a document object variable and use that in
place of ActiveDocument. Roughly:
Dim oDoc as Word.Document
Dim rng as Word.Range
Set oDoc = wdApp.Documents.Open("Filepath.doc")
Set rng = oDoc.Range

- Where you have Selection, consider using Range, instead. If you decide to
stick with Selection you MUST prefix it with the Word application object.
Otherwise, Excel will try to use its Selection object, which won't work
Roughly: wdApp.Selection

open the text file from within the Word VBA?>>

Yes, that would also be possible. Basically, the reverse of the above
discussion, where you automate Excel through Word (also on the word.mvps.org
site).

-- Cindy

 

[RB Smissaert]

This is very simple with automation.
In Word have reference to Excel and then have a routine as this simple
example:


Sub Test()

Dim i As Long
Dim c As Long
Dim arr(1 To 5, 1 To 3) As Long

Dim oXL As Excel.Application
Dim oWB As Workbook
Dim oSH As Worksheet

'fill the array
For i = 1 To 5
For c = 1 To 3
arr(i, c) = i + c
Next
Next

'start Excel
Set oXL = New Excel.Application
Set oWB = oXL.Workbooks.Open(FileName:="C:\test.xls")
Set oSH = oXL.Worksheets(1)

'put the array in the sheet
oSH.Range(Cells(1), Cells(5, 3)) = arr

oWB.Save
oWB.Close

Set oSH = Nothing
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub


RBS