Re: Trying to suppress Select Table box in Excel-Word mailmerge
- From: Denise <me@xxxxxx>
- Date: Tue, 21 Jun 2005 13:08:39 +0100
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" <me@xxxxxx> wrote in message
> news:34iwsablixng.1x0dc8l4fc5tf$.dlg@xxxxxxxxxxxxx
>> 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.aspx?scid=kb;en-us;825765
>>>
>>> Peter Jamieson
>>>
>>> "Denise" <me@xxxxxx> wrote in message
>>> news:yvggomlela84$.1jglfbctq0vd1.dlg@xxxxxxxxxxxxx
>>>> 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
.
- References:
- Trying to suppress Select Table box in Excel-Word mailmerge
- From: Denise
- Re: Trying to suppress Select Table box in Excel-Word mailmerge
- From: Peter Jamieson
- Re: Trying to suppress Select Table box in Excel-Word mailmerge
- From: Denise
- Re: Trying to suppress Select Table box in Excel-Word mailmerge
- From: Peter Jamieson
- Trying to suppress Select Table box in Excel-Word mailmerge
- Prev by Date: Merge with correlated data on both sides
- Next by Date: Re: Merge with correlated data on both sides
- Previous by thread: Re: Trying to suppress Select Table box in Excel-Word mailmerge
- Next by thread: Mail merge via dde connection
- Index(es):
Relevant Pages
|
