Parsing Word document into Excel

[Chill1]

I'll start this off by saying that I've worked with C++, C, Java, and
number of UNIX scripting languages but have little or no knowledge o
VBA. Working with VBA is somewhat essential to the project I'm workin
on at the moment so I came here for help. Just thought I'd present thi
so that any reply includes everything, down to the little details tha
may seem second nature to someone who has experience with VBA. Thanks
and now, onto the question

The company I'm working for needs a program that takes output fro
their Provantis storage system (which gives the most ass-backwards fil
formatting I've ever seen) and put it into an Excel spreadsheet in
usable format. I need help with opening and parsing the Word file
Anything relevant is welcome, but particularly information on settin
delimiters VBA will read between, any functions that get lines o
words, and how to then put any of this into a spreadsheet.

I know it's a lot (or maybe it's very simple and I just dont kno
jack), and my thanks to anyone that take the time to repl

 

[Tom Ogilvy]

Have you tried selecting the data in Word, copying it to the clipboard, then
going to Excel and select cell A1 on a new sheet and do Edit =>Paste. Then
select column 1 and do Data => Text to Columns and walk through the wizard?

You can turn on the macro recorder in Word while you do this manually to get
the word syntax.

Then repeat again with the macro recorder turned on in Excel.

 

[R Avery]

Or, you could use regular expressions to parse the horrible Word format.
You can find information on VBA regular expressions here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting051099.asp


Here is some sample code to get you started:



Private Const FILE_PATH As String = "C:\MyDoc.doc"

Public Sub ImportWordText()
Dim appWord As New Word.Application
Dim strAllText As String
Dim i As Long


With appWord.Documents.Open(FILE_PATH)
strAllText = .Range.Text

Dim re As New VBScript_RegExp_55.RegExp

re.Pattern = "Insert Pattern Here"

With re.Execute(strAllText)
For i = 0 To .Count - 1
With .Item(idxData).SubMatches
For j = 0 To .Count - 1
Debug.Print .Item(j)
Next j
End With
Next idxData
End With

.Close
End With

CleanUp:
appWord.Quit
Set appWord = Nothing
End Sub

 

[Chill1]

I thought about using the Macro recorder like that, but the Word file'
format is rather unpredictable so I figured whatever macro I recorde
would probably screw up more often than not.


Regular expressions... that almost brings a tear to my eye. That wil
make some of this much easier. Thanks for pushing me in the righ
direction.

Now, can anyone answer my question about how to run an exe (wit
commandline arguments) from VBA

 

[Tom Ogilvy]

Use the shell command.

 

[Tom Ogilvy]

How much variability will there be in terms of the code to copy data from
word?

 

[Chill1]

Not sure I understand the question.

I'm writing it all myself and can do it whatever way gets it done