Opening word from within xls

[AB]

Hi,
I'm trying to launch .doc from within .xls using FollowHyperlink
method (as i need it to go via dde and simple open method doesn't
work).

The .xls contains invoicing data wile the .doc is MailMerge (to create
the invoice) based on that same .xls (i'm launching the .doc from) via
dde. As a result it stalls (says that can't access) because:

- the dde connection in the .doc MM is trying to connect to the .xls
that stores the invoicing data

- since i'm launching the .doc from the same .xls where the invoicing
data are stored - the code 'locks' the .xls (since the code is still
kind of running as it waits till the .doc opens) but the .doc can't
open since it can't reach the locked .xls file - a vicious cycle...

Any ideas how to have the .xls 'unlock' for the time while the .doc MM
is opening?
Any help would be greatly appreciated.

 

[Peter T]

Try something like this

Sub test()
Dim sLink As String
Dim oWd As Object ' As Word
Dim oDoc As Object ' As Document

sLink = Range("C4").Hyperlinks(1).Address ' path & .doc filename

On Error Resume Next
' attempt to find a running instance of Word
Set oWd = GetObject(, "word.application")
On Error GoTo 0

If oWd Is Nothing Then
' start a new instance of Word
Set oWd = CreateObject("word.application")
End If
oWd.Visible = True
Set oDoc = oWd.documents.Open(sLink)

End Sub


Maybe you could record a macro in Word to do the rest of your mailmerge,
then adapt it to include in the above.

If you set a reference to Word in Tools (on the main VBE menu bar) you can
change As Object to As Word & Document to get Word's VBA intellisense.

Regards,
Peter T

 

[AB]

Thanks Peter.
The code and the note about the Word Library is helpful!

In the meantime unfortunatelly this way the .doc opens as regular Word
file without linking/connecting to the MailMarge data in .xls - i.e.,
it has the fileds in the .doc but it's not a mail merge master file
anymore - i can't navigate between records nor merge to a new
document.

When i open the file manually (file>open) then Word asks is if want to
run the SQL behind the word (to which i say Yes) and everything is
fine - it's a proper MM master file BUT when i open the word file from
VBA (not using the followhyperlink) then the file upon openning
doesn't prompt for that SQL anymore and it's not the MM masterfile
anymore...
Therefore i tried the followhyperlink that let's me open the file the
way i want BUT it stalls due to locking down the .xls (since i launch
the followhyperlink from the .xls).

Any ideas how to overcome the above?

 

[Peter T]

Did you try recording a macro in Word as I suggested. I have done just that,
and adapted to include in the maco I posted previously (I had set up a
simple mailmerge in the doc before saving)

Sub test2()
Dim sLink As String
Dim oWd As Object ' As Word
Dim oDoc As Object ' As Document

sLink = Range("C4").Hyperlinks(1).Address ' path & .doc filename

'On Error Resume Next
Set oWd = GetObject(, "word.application")
On Error GoTo 0

If oWd Is Nothing Then

Set oWd = CreateObject("word.application")
End If
oWd.Visible = True
Set oDoc = oWd.documents.Open(sLink)


' Comment these constants if the reference to Word is set to this project
Const wdOpenFormatAuto As Long = 0
Const wdFieldAddressBlock As Long = 93
Const wdMergeSubTypeAccess As Long = 1

Const wdSendToNewDocument As Long = 0
Const wdDefaultFirstRecord As Long = 1
Const wdDefaultLastRecord As Long = -16

oDoc.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\Owner\My Documents\MergeData.xls", _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=C:\Documents and Settings\Owner\My
Documents\MergeData.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase Pa" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

'' watch out for line wrap above
'' "Provider=Microsoft.Jet .....Path="""";Jet OLEDBatabase Pa" _
'' should all be on one line


With oDoc.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

End Sub

 

[AB]

Thanks Peter!
Does the code work for you if you launch it from the same .xls file
where the MailMerge would pull data from? Doesn't it lock down
the .xls the same way as 'my code' does? (i'm not at my pc right now
so can't check it myself but was wondering as maybe it's actually PC
setup and not .xls vs .doc thing)
one difference in the code though - in your code it's:
ConfirmConversions:=False
but i need it to be
ConfirmConversions:=True
as i need it to come via dde as i need to retain formatting. Maybe i
got this one wrong, though...
??

 

[Peter T]

I didn't try to run the code from the xls mailmerge data file (in the last
macro, the file mergedata.xls was closed). I would not be surprised if it
failed in the data xls as, AFAIK, SQL should only be used with closed files.

It would not be difficult at all process the data, copy it to another
(temporary) workbook, save and close the file, then do the mailmerge stuff
with code in the original data file.

As for ConfirmConversions false vs true I have no idea. Like I said, I
merely recorded a simple macro in Word, then adapted for use in Excel. All
seemed to work OK with my limited test.

Not sure why but somehow I seemed to have answered a few questions lately
about Excel/Word mailmerge whilst knowing very little about it!

Regards,
Peter T

 

[AB]

Thanks Pater!
It just means that it takes two intelligent people to have a
discussion to come up with answers! ))))
Thanks a lot for your thought/suggestions. It seems I’ll need to go
the workaround route.

Thanks again!