Reply
 
Thread Tools Display Modes
  #1   Report Post  
Denise
 
Posts: n/a
Default Trying to suppress Select Table box in Excel-Word mailmerge

Hello

Using Office 2002 SP-2, Windows 2000 V5.0 SP-4.

I have been handed a mailmerge problem, involving merging data from Excel
into a Word document. It's one of those 'it used to work but now it
doesn't' scenarios.

An Excel spreadsheet has some code, which calls a Word mailmerge document,
which subsequently calls another Excel spreadsheet with the merge data in
it. Apparently the process stopped working at some point and, when you run
the code in Excel, it opens the Word doc as an ordinary document, with no
reference to it being a mailmerge doc. If you open the Word doc on its
own, say through Explorer, it understands that it is a mailmerge doc.

I added to the existing code in the Excel file to try and get it to pick up
on the fact that the Word doc was a mailmerge file. The code is as
follows:-

With .Documents("BACDBSnew.doc").MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:="C:\temp\mailmerge.xls", _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Entire Spreadsheet", _
SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With

This certainly gets the whole mailmerge thing going, the problem is that I
get the Select Table dialog appearing in the Word doc, where I have to
select the sheet and untick the 'First row of data contains column
headings' box. Is there any way to automate these responses via the code,
or at least to suppress this dialog?

Sorry, it's a long story for a short question! Hopefully, though, someone
will have some ideas. I'd appreciate any help.

Thanks

Denise Crawley
  #2   Report Post  
Peter Jamieson
 
Posts: n/a
Default

My guess is that the problem may result from a security-realted change in
Word/Office SP-2 where OpenDataSource connections made programmatically fail
unless a change has been made to the Windows registry - see the following
Knowledgebase article for details:

http://support.microsoft.com/default...b;en-us;825765

Peter Jamieson

"Denise" wrote in message
...
Hello

Using Office 2002 SP-2, Windows 2000 V5.0 SP-4.

I have been handed a mailmerge problem, involving merging data from Excel
into a Word document. It's one of those 'it used to work but now it
doesn't' scenarios.

An Excel spreadsheet has some code, which calls a Word mailmerge document,
which subsequently calls another Excel spreadsheet with the merge data in
it. Apparently the process stopped working at some point and, when you
run
the code in Excel, it opens the Word doc as an ordinary document, with no
reference to it being a mailmerge doc. If you open the Word doc on its
own, say through Explorer, it understands that it is a mailmerge doc.

I added to the existing code in the Excel file to try and get it to pick
up
on the fact that the Word doc was a mailmerge file. The code is as
follows:-

With .Documents("BACDBSnew.doc").MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:="C:\temp\mailmerge.xls", _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Entire Spreadsheet", _
SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With

This certainly gets the whole mailmerge thing going, the problem is that I
get the Select Table dialog appearing in the Word doc, where I have to
select the sheet and untick the 'First row of data contains column
headings' box. Is there any way to automate these responses via the code,
or at least to suppress this dialog?

Sorry, it's a long story for a short question! Hopefully, though, someone
will have some ideas. I'd appreciate any help.

Thanks

Denise Crawley



  #3   Report Post  
Denise
 
Posts: n/a
Default

Hi Peter, thanks for your reply.

It's not actually that dialog that's the problem. It's the one headed
Select Table, which has the name of the sheet (Sheet1$), description,
modified, etc.. Would this box also require a registry change to get rid
of it?

Thanks

Denise

On Mon, 20 Jun 2005 22:06:01 +0100, Peter Jamieson wrote:

My guess is that the problem may result from a security-realted change in
Word/Office SP-2 where OpenDataSource connections made programmatically fail
unless a change has been made to the Windows registry - see the following
Knowledgebase article for details:

http://support.microsoft.com/default...b;en-us;825765

Peter Jamieson

"Denise" wrote in message
...
Hello

Using Office 2002 SP-2, Windows 2000 V5.0 SP-4.

I have been handed a mailmerge problem, involving merging data from Excel
into a Word document. It's one of those 'it used to work but now it
doesn't' scenarios.

An Excel spreadsheet has some code, which calls a Word mailmerge document,
which subsequently calls another Excel spreadsheet with the merge data in
it. Apparently the process stopped working at some point and, when you
run
the code in Excel, it opens the Word doc as an ordinary document, with no
reference to it being a mailmerge doc. If you open the Word doc on its
own, say through Explorer, it understands that it is a mailmerge doc.

I added to the existing code in the Excel file to try and get it to pick
up
on the fact that the Word doc was a mailmerge file. The code is as
follows:-

With .Documents("BACDBSnew.doc").MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:="C:\temp\mailmerge.xls", _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Entire Spreadsheet", _
SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With

This certainly gets the whole mailmerge thing going, the problem is that I
get the Select Table dialog appearing in the Word doc, where I have to
select the sheet and untick the 'First row of data contains column
headings' box. Is there any way to automate these responses via the code,
or at least to suppress this dialog?

Sorry, it's a long story for a short question! Hopefully, though, someone
will have some ideas. I'd appreciate any help.

Thanks

Denise Crawley

  #4   Report Post  
Peter Jamieson
 
Posts: n/a
Default

OK, I was trying to address the original problem, and it /might/ be worth
going back to the original version of the sheet and following the article I
mentioned. But maybe not. Otherwise, it may just be that you need to provide
different connection settings. I would suggest that if you want to connect
using DDE, you use

..OpenDataSource Name:="C:\temp\mailmerge.xls", _
Connection:="Entire Spreadsheet", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeWord2000

You shouldn't need any of the other parameters. Notice that the single
quotes around Sheet1$ are backwards quotes, not the more common vertical
ones. If the Workbook's sheets have been renamed you will probably need to
substitue the actual name used for the sheet. (In fact, that is a possible
reason why it has suddenly gone wrong).

DDE opens a copy of Excel and your user may be able to tell you whether that
is what used to happen. If you just want to use the default method, you may
find that

..OpenDataSource Name:="C:\temp\mailmerge.xls"

is actually enough but if not, try

..OpenDataSource Name:="C:\temp\mailmerge.xls", _
Connection:="", _
SQLStatement:="SELECT * FROM `Sheet1$`"

or

..OpenDataSource Name:="C:\temp\mailmerge.xls", _
Connection:="", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeAccess

(Yes, Access, even though it's an Excel sheet)

If that doesn't help it's difficult to know what to do next - I think I
would try similar code with a newly created sheet. if that works, you may
need to reconstruct the spreadsheet. If it does not, no idea what's wrong!

Peter Jamieson

"Denise" wrote in message
...
Hi Peter, thanks for your reply.

It's not actually that dialog that's the problem. It's the one headed
Select Table, which has the name of the sheet (Sheet1$), description,
modified, etc.. Would this box also require a registry change to get rid
of it?

Thanks

Denise

On Mon, 20 Jun 2005 22:06:01 +0100, Peter Jamieson wrote:

My guess is that the problem may result from a security-realted change in
Word/Office SP-2 where OpenDataSource connections made programmatically
fail
unless a change has been made to the Windows registry - see the following
Knowledgebase article for details:

http://support.microsoft.com/default...b;en-us;825765

Peter Jamieson

"Denise" wrote in message
...
Hello

Using Office 2002 SP-2, Windows 2000 V5.0 SP-4.

I have been handed a mailmerge problem, involving merging data from
Excel
into a Word document. It's one of those 'it used to work but now it
doesn't' scenarios.

An Excel spreadsheet has some code, which calls a Word mailmerge
document,
which subsequently calls another Excel spreadsheet with the merge data
in
it. Apparently the process stopped working at some point and, when you
run
the code in Excel, it opens the Word doc as an ordinary document, with
no
reference to it being a mailmerge doc. If you open the Word doc on its
own, say through Explorer, it understands that it is a mailmerge doc.

I added to the existing code in the Excel file to try and get it to pick
up
on the fact that the Word doc was a mailmerge file. The code is as
follows:-

With .Documents("BACDBSnew.doc").MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:="C:\temp\mailmerge.xls", _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Entire Spreadsheet", _
SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With

This certainly gets the whole mailmerge thing going, the problem is that
I
get the Select Table dialog appearing in the Word doc, where I have to
select the sheet and untick the 'First row of data contains column
headings' box. Is there any way to automate these responses via the
code,
or at least to suppress this dialog?

Sorry, it's a long story for a short question! Hopefully, though,
someone
will have some ideas. I'd appreciate any help.

Thanks

Denise Crawley



  #5   Report Post  
Denise
 
Posts: n/a
Default

Peter, you're a genius. I hadn't noticed that the single quotes were
backward ones. I changed them and it works perfectly! Shame on me for not
realising.

Thanks very much for your help. I should have some very happy users now.

Denise

On Tue, 21 Jun 2005 11:50:37 +0100, Peter Jamieson wrote:

OK, I was trying to address the original problem, and it /might/ be worth
going back to the original version of the sheet and following the article I
mentioned. But maybe not. Otherwise, it may just be that you need to provide
different connection settings. I would suggest that if you want to connect
using DDE, you use

.OpenDataSource Name:="C:\temp\mailmerge.xls", _
Connection:="Entire Spreadsheet", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeWord2000

You shouldn't need any of the other parameters. Notice that the single
quotes around Sheet1$ are backwards quotes, not the more common vertical
ones. If the Workbook's sheets have been renamed you will probably need to
substitue the actual name used for the sheet. (In fact, that is a possible
reason why it has suddenly gone wrong).

DDE opens a copy of Excel and your user may be able to tell you whether that
is what used to happen. If you just want to use the default method, you may
find that

.OpenDataSource Name:="C:\temp\mailmerge.xls"

is actually enough but if not, try

.OpenDataSource Name:="C:\temp\mailmerge.xls", _
Connection:="", _
SQLStatement:="SELECT * FROM `Sheet1$`"

or

.OpenDataSource Name:="C:\temp\mailmerge.xls", _
Connection:="", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeAccess

(Yes, Access, even though it's an Excel sheet)

If that doesn't help it's difficult to know what to do next - I think I
would try similar code with a newly created sheet. if that works, you may
need to reconstruct the spreadsheet. If it does not, no idea what's wrong!

Peter Jamieson

"Denise" wrote in message
...
Hi Peter, thanks for your reply.

It's not actually that dialog that's the problem. It's the one headed
Select Table, which has the name of the sheet (Sheet1$), description,
modified, etc.. Would this box also require a registry change to get rid
of it?

Thanks

Denise

On Mon, 20 Jun 2005 22:06:01 +0100, Peter Jamieson wrote:

My guess is that the problem may result from a security-realted change in
Word/Office SP-2 where OpenDataSource connections made programmatically
fail
unless a change has been made to the Windows registry - see the following
Knowledgebase article for details:

http://support.microsoft.com/default...b;en-us;825765

Peter Jamieson

"Denise" wrote in message
...
Hello

Using Office 2002 SP-2, Windows 2000 V5.0 SP-4.

I have been handed a mailmerge problem, involving merging data from
Excel
into a Word document. It's one of those 'it used to work but now it
doesn't' scenarios.

An Excel spreadsheet has some code, which calls a Word mailmerge
document,
which subsequently calls another Excel spreadsheet with the merge data
in
it. Apparently the process stopped working at some point and, when you
run
the code in Excel, it opens the Word doc as an ordinary document, with
no
reference to it being a mailmerge doc. If you open the Word doc on its
own, say through Explorer, it understands that it is a mailmerge doc.

I added to the existing code in the Excel file to try and get it to pick
up
on the fact that the Word doc was a mailmerge file. The code is as
follows:-

With .Documents("BACDBSnew.doc").MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:="C:\temp\mailmerge.xls", _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Entire Spreadsheet", _
SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With

This certainly gets the whole mailmerge thing going, the problem is that
I
get the Select Table dialog appearing in the Word doc, where I have to
select the sheet and untick the 'First row of data contains column
headings' box. Is there any way to automate these responses via the
code,
or at least to suppress this dialog?

Sorry, it's a long story for a short question! Hopefully, though,
someone
will have some ideas. I'd appreciate any help.

Thanks

Denise Crawley

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macros - Keyboard Commands Janet Microsoft Word Help 6 April 11th 05 05:28 AM
Locking Two Words Together to Make a Proper Compound Noun in Word WorkingWoman Microsoft Word Help 2 April 7th 05 02:33 PM
In Word, how can I see all files (*.*) in "save as"? citizen53 New Users 8 April 4th 05 04:56 PM
Wordperfect Office 2000 conversion to Word 2003 MikeE New Users 1 March 21st 05 01:04 AM
How do I insert Excel data as a Word table and stay within margins Fritz Tables 1 February 6th 05 09:39 AM


All times are GMT +1. The time now is 10:16 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"