Code in Excel macro to get all open Word docs?
 

What would I use in Excel 2000 VBA to get all the currently open Word docs?
Or, even better, break out of the code to allow the user to choose his doc
and select a specific position in the doc?

I have a Word macro that takes a selection copied from Excel and pastes it
into my Word report with certain formatting. Some of my coworkers could
make use of this macro as well, but it would be easier to put the macro in
an Excel workbook I already distribute with macros than to get them to add
it into Word. To make it work , though, they would have to be able to get
it into the right Word doc from Excel - much different than having the doc
open and pulling it off the clipboard.

Any help is appreciated.
Ed

Code in Excel macro to get all open Word docs?
 

Ed,

This is a cut down version of a routine that should give you an idea. It
looks a bit odd with the redim structure because I've cut some other logic
out.

Public Sub EnumerateWordDocs(oWord As Object, vDocs As Variant)
'---------------------------------------------------------------------------------------
' Procedure : EnumerateEFAWordDocs
' DateTime : 1/13/2006 16:40
' Author : Robin Hammond
' Purpose : enumerates any files that are currently open returning
the
' results in the vDocs array
'---------------------------------------------------------------------------------------
'
Dim vDoc As Variant
Dim lCounter As Long
Dim lVarCounter As Long

20 lCounter = 0

30 For Each vDoc In oWord.Documents

40 With vDoc

70 If lCounter 0 Then

80 ReDim Preserve vDocs(0 To lCounter)

90 Else

100 ReDim vDocs(0 To 0)

110 End If

120 vDocs(lCounter) = vDoc.FullName
130 lCounter = lCounter + 1

170 End With

180 Next vDoc

End Sub

This is in some form code to give you an idea of how to call it

Private Sub UserForm_Initialize()
Dim vDocs As Variant
Dim lCounter As Long
Dim oWord as Object

10 On Error Resume Next
20 Set oWord = GetObject(, "Word.Application")
30 On Error GoTo UserForm_Initialize_Error

40 If Not oWord Is Nothing Then

50 EnumerateWordDocs oWord, vDocs

60 If Not IsEmpty(vDocs) Then

70 For lCounter = 0 To UBound(vDocs)

80 lstDocs.AddItem vDocs(lCounter)

90 Next lCounter

110 End If

120 End If


Robin Hammond
www.enhanceddatasystems.com

"Ed" wrote in message
...
What would I use in Excel 2000 VBA to get all the currently open Word
docs?
Or, even better, break out of the code to allow the user to choose his doc
and select a specific position in the doc?

I have a Word macro that takes a selection copied from Excel and pastes it
into my Word report with certain formatting. Some of my coworkers could
make use of this macro as well, but it would be easier to put the macro in
an Excel workbook I already distribute with macros than to get them to add
it into Word. To make it work , though, they would have to be able to get
it into the right Word doc from Excel - much different than having the doc
open and pulling it off the clipboard.

Any help is appreciated.
Ed

Code in Excel macro to get all open Word docs?
 

Thank you very much, Robin! This seems to be working very well.

There is one slight hitch, though. You have a variable "lstDocs" in line 80
of the Initialize sub, but it is not Dim'ed. Is this to be a Control (maybe
an Option Button for each vDocs?)?

Ed

"Robin Hammond" wrote in message
...
Ed,

This is a cut down version of a routine that should give you an idea. It
looks a bit odd with the redim structure because I've cut some other logic
out.

Public Sub EnumerateWordDocs(oWord As Object, vDocs As Variant)

'---------------------------------------------------------------------------
------------
' Procedure : EnumerateEFAWordDocs
' DateTime : 1/13/2006 16:40
' Author : Robin Hammond
' Purpose : enumerates any files that are currently open returning
the
' results in the vDocs array

'---------------------------------------------------------------------------
------------
'
Dim vDoc As Variant
Dim lCounter As Long
Dim lVarCounter As Long

20 lCounter = 0

30 For Each vDoc In oWord.Documents

40 With vDoc

70 If lCounter 0 Then

80 ReDim Preserve vDocs(0 To lCounter)

90 Else

100 ReDim vDocs(0 To 0)

110 End If

120 vDocs(lCounter) = vDoc.FullName
130 lCounter = lCounter + 1

170 End With

180 Next vDoc

End Sub

This is in some form code to give you an idea of how to call it

Private Sub UserForm_Initialize()
Dim vDocs As Variant
Dim lCounter As Long
Dim oWord as Object

10 On Error Resume Next
20 Set oWord = GetObject(, "Word.Application")
30 On Error GoTo UserForm_Initialize_Error

40 If Not oWord Is Nothing Then

50 EnumerateWordDocs oWord, vDocs

60 If Not IsEmpty(vDocs) Then

70 For lCounter = 0 To UBound(vDocs)

80 lstDocs.AddItem vDocs(lCounter)

90 Next lCounter

110 End If

120 End If


Robin Hammond
www.enhanceddatasystems.com

"Ed" wrote in message
...
What would I use in Excel 2000 VBA to get all the currently open Word
docs?
Or, even better, break out of the code to allow the user to choose his
doc
and select a specific position in the doc?

I have a Word macro that takes a selection copied from Excel and pastes
it
into my Word report with certain formatting. Some of my coworkers could
make use of this macro as well, but it would be easier to put the macro
in
an Excel workbook I already distribute with macros than to get them to
add
it into Word. To make it work , though, they would have to be able to
get
it into the right Word doc from Excel - much different than having the
doc
open and pulling it off the clipboard.

Any help is appreciated.
Ed

Code in Excel macro to get all open Word docs?
 

Good.

lstDocs was a listbox on my form in the example I gave.

Robin Hammond
www.enhanceddatasystems.com

"Ed" wrote in message
...
Thank you very much, Robin! This seems to be working very well.

There is one slight hitch, though. You have a variable "lstDocs" in line
80
of the Initialize sub, but it is not Dim'ed. Is this to be a Control
(maybe
an Option Button for each vDocs?)?

Ed

"Robin Hammond" wrote in message
...
Ed,

This is a cut down version of a routine that should give you an idea. It
looks a bit odd with the redim structure because I've cut some other
logic
out.

Public Sub EnumerateWordDocs(oWord As Object, vDocs As Variant)

'---------------------------------------------------------------------------
------------
' Procedure : EnumerateEFAWordDocs
' DateTime : 1/13/2006 16:40
' Author : Robin Hammond
' Purpose : enumerates any files that are currently open
returning
the
' results in the vDocs array

'---------------------------------------------------------------------------
------------
'
Dim vDoc As Variant
Dim lCounter As Long
Dim lVarCounter As Long

20 lCounter = 0

30 For Each vDoc In oWord.Documents

40 With vDoc

70 If lCounter 0 Then

80 ReDim Preserve vDocs(0 To lCounter)

90 Else

100 ReDim vDocs(0 To 0)

110 End If

120 vDocs(lCounter) = vDoc.FullName
130 lCounter = lCounter + 1

170 End With

180 Next vDoc

End Sub

This is in some form code to give you an idea of how to call it

Private Sub UserForm_Initialize()
Dim vDocs As Variant
Dim lCounter As Long
Dim oWord as Object

10 On Error Resume Next
20 Set oWord = GetObject(, "Word.Application")
30 On Error GoTo UserForm_Initialize_Error

40 If Not oWord Is Nothing Then

50 EnumerateWordDocs oWord, vDocs

60 If Not IsEmpty(vDocs) Then

70 For lCounter = 0 To UBound(vDocs)

80 lstDocs.AddItem vDocs(lCounter)

90 Next lCounter

110 End If

120 End If


Robin Hammond
www.enhanceddatasystems.com

"Ed" wrote in message
...
What would I use in Excel 2000 VBA to get all the currently open Word
docs?
Or, even better, break out of the code to allow the user to choose his
doc
and select a specific position in the doc?

I have a Word macro that takes a selection copied from Excel and pastes
it
into my Word report with certain formatting. Some of my coworkers
could
make use of this macro as well, but it would be easier to put the macro
in
an Excel workbook I already distribute with macros than to get them to
add
it into Word. To make it work , though, they would have to be able to
get
it into the right Word doc from Excel - much different than having the
doc
open and pulling it off the clipboard.

Any help is appreciated.
Ed

Code in Excel macro to get all open Word docs?
 

lstDocs was a listbox on my form in the example I gave.
D'oh!! The "lst" should have given it away. (Just because _I'm_ lax in
identifying my variables doesn't mean everyone else is!)

Got it, put it in, and it all works great! Thank you!!
Ed

"Robin Hammond" wrote in message
...
Good.

lstDocs was a listbox on my form in the example I gave.

Robin Hammond
www.enhanceddatasystems.com

"Ed" wrote in message
...
Thank you very much, Robin! This seems to be working very well.

There is one slight hitch, though. You have a variable "lstDocs" in
line
80
of the Initialize sub, but it is not Dim'ed. Is this to be a Control
(maybe
an Option Button for each vDocs?)?

Ed

"Robin Hammond" wrote in message
...
Ed,

This is a cut down version of a routine that should give you an idea.
It
looks a bit odd with the redim structure because I've cut some other
logic
out.

Public Sub EnumerateWordDocs(oWord As Object, vDocs As Variant)


'---------------------------------------------------------------------------
------------
' Procedure : EnumerateEFAWordDocs
' DateTime : 1/13/2006 16:40
' Author : Robin Hammond
' Purpose : enumerates any files that are currently open
returning
the
' results in the vDocs array


'---------------------------------------------------------------------------
------------
'
Dim vDoc As Variant
Dim lCounter As Long
Dim lVarCounter As Long

20 lCounter = 0

30 For Each vDoc In oWord.Documents

40 With vDoc

70 If lCounter 0 Then

80 ReDim Preserve vDocs(0 To lCounter)

90 Else

100 ReDim vDocs(0 To 0)

110 End If

120 vDocs(lCounter) = vDoc.FullName
130 lCounter = lCounter + 1

170 End With

180 Next vDoc

End Sub

This is in some form code to give you an idea of how to call it

Private Sub UserForm_Initialize()
Dim vDocs As Variant
Dim lCounter As Long
Dim oWord as Object

10 On Error Resume Next
20 Set oWord = GetObject(, "Word.Application")
30 On Error GoTo UserForm_Initialize_Error

40 If Not oWord Is Nothing Then

50 EnumerateWordDocs oWord, vDocs

60 If Not IsEmpty(vDocs) Then

70 For lCounter = 0 To UBound(vDocs)

80 lstDocs.AddItem vDocs(lCounter)

90 Next lCounter

110 End If

120 End If


Robin Hammond
www.enhanceddatasystems.com

"Ed" wrote in message
...
What would I use in Excel 2000 VBA to get all the currently open Word
docs?
Or, even better, break out of the code to allow the user to choose
his
doc
and select a specific position in the doc?

I have a Word macro that takes a selection copied from Excel and
pastes
it
into my Word report with certain formatting. Some of my coworkers
could
make use of this macro as well, but it would be easier to put the
macro
in
an Excel workbook I already distribute with macros than to get them
to
add
it into Word. To make it work , though, they would have to be able
to
get
it into the right Word doc from Excel - much different than having
the
doc
open and pulling it off the clipboard.

Any help is appreciated.
Ed