Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
rberry rberry is offline
external usenet poster
 
Posts: 2
Default Select Table Dialogue Prompt

I have programmatically set up a VBA routine from within Access 2003 to load
a Word 2003 mailmerge document (OLE DB) the datasource is a Excel 2003
spreadsheet.

This is all done in the background so the user doesn't see the gubbins of
Word doing its thing!

Problem, whilst it loads into the mailmerge document as expected it hangs up
because in the Select Table Dialogue Box it ask for the worksheet you want to
load as the Range of data selected.

Question is there a way to autiomate this so that is selcets a defined
worksheet "qryLetterMerge" and complete the rest of the automation process
which print the merged letters and then returns the user to access.

Below I show the VBA code I use to launch this process:

' Create Word Instance trapping routine (Early Binding).
Set objWord = GetObject(, "Word.Application")
If Err.Number 0 Then
Set objWord = CreateObject("Word.Application")
End If

'Launch Office Automation and run mailmerge letters.
' Make Word visible.
objWord.Visible = False
DoEvents
objWord.Activate

' Set the mail merge data source as the WEF (Front End) database.
objWord.Documents.Open FileName:=StrDoc, ConfirmConversions:=False,
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""

objWord.ActiveDocument.MailMerge.OpenDataSource
Name:="E:\Development Databases\WEF\qryLetterMerge.xls",
Connection:="qryLetterMerge"

' Manipulating the Word Mailmerge object
With objWord.ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With

' Execute the mail merge.
For intCounter = 1 To RptCopies
.Execute
Next intCounter
End With

' Close Active Word Document
objWord.ActiveDocument.Close wdDoNotSaveChanges

' Close word object instance
objWord.Quit
Set objWord = Nothing


  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Select Table Dialogue Prompt

In this case you need to specify a query. If the sheet name is Sheet1,
and assuming you want all the columns and rows, try

objWord.ActiveDocument.MailMerge.OpenDataSource
Name:="E:\Development Databases\WEF\qryLetterMerge.xls",
SQLStatement:="SELECT * FROM [Sheet1$]"

(notice the $ on the end of the sheet name)

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

rberry wrote:
I have programmatically set up a VBA routine from within Access 2003 to load
a Word 2003 mailmerge document (OLE DB) the datasource is a Excel 2003
spreadsheet.

This is all done in the background so the user doesn't see the gubbins of
Word doing its thing!

Problem, whilst it loads into the mailmerge document as expected it hangs up
because in the Select Table Dialogue Box it ask for the worksheet you want to
load as the Range of data selected.

Question is there a way to autiomate this so that is selcets a defined
worksheet "qryLetterMerge" and complete the rest of the automation process
which print the merged letters and then returns the user to access.

Below I show the VBA code I use to launch this process:

' Create Word Instance trapping routine (Early Binding).
Set objWord = GetObject(, "Word.Application")
If Err.Number 0 Then
Set objWord = CreateObject("Word.Application")
End If

'Launch Office Automation and run mailmerge letters.
' Make Word visible.
objWord.Visible = False
DoEvents
objWord.Activate

' Set the mail merge data source as the WEF (Front End) database.
objWord.Documents.Open FileName:=StrDoc, ConfirmConversions:=False,
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""

objWord.ActiveDocument.MailMerge.OpenDataSource
Name:="E:\Development Databases\WEF\qryLetterMerge.xls",
Connection:="qryLetterMerge"

' Manipulating the Word Mailmerge object
With objWord.ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With

' Execute the mail merge.
For intCounter = 1 To RptCopies
.Execute
Next intCounter
End With

' Close Active Word Document
objWord.ActiveDocument.Close wdDoNotSaveChanges

' Close word object instance
objWord.Quit
Set objWord = Nothing


  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
rberry rberry is offline
external usenet poster
 
Posts: 2
Default Select Table Dialogue Prompt

Peter
Top man, that worked as sweet as a nut. Many thanks.

Oh by the way what is the significance of the $?

Regards
Richard

"Peter Jamieson" wrote:

In this case you need to specify a query. If the sheet name is Sheet1,
and assuming you want all the columns and rows, try

objWord.ActiveDocument.MailMerge.OpenDataSource
Name:="E:\Development Databases\WEF\qryLetterMerge.xls",
SQLStatement:="SELECT * FROM [Sheet1$]"

(notice the $ on the end of the sheet name)

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

rberry wrote:
I have programmatically set up a VBA routine from within Access 2003 to load
a Word 2003 mailmerge document (OLE DB) the datasource is a Excel 2003
spreadsheet.

This is all done in the background so the user doesn't see the gubbins of
Word doing its thing!

Problem, whilst it loads into the mailmerge document as expected it hangs up
because in the Select Table Dialogue Box it ask for the worksheet you want to
load as the Range of data selected.

Question is there a way to autiomate this so that is selcets a defined
worksheet "qryLetterMerge" and complete the rest of the automation process
which print the merged letters and then returns the user to access.

Below I show the VBA code I use to launch this process:

' Create Word Instance trapping routine (Early Binding).
Set objWord = GetObject(, "Word.Application")
If Err.Number 0 Then
Set objWord = CreateObject("Word.Application")
End If

'Launch Office Automation and run mailmerge letters.
' Make Word visible.
objWord.Visible = False
DoEvents
objWord.Activate

' Set the mail merge data source as the WEF (Front End) database.
objWord.Documents.Open FileName:=StrDoc, ConfirmConversions:=False,
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""

objWord.ActiveDocument.MailMerge.OpenDataSource
Name:="E:\Development Databases\WEF\qryLetterMerge.xls",
Connection:="qryLetterMerge"

' Manipulating the Word Mailmerge object
With objWord.ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With

' Execute the mail merge.
For intCounter = 1 To RptCopies
.Execute
Next intCounter
End With

' Close Active Word Document
objWord.ActiveDocument.Close wdDoNotSaveChanges

' Close word object instance
objWord.Quit
Set objWord = Nothing



  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Select Table Dialogue Prompt

Oh by the way what is the significance of the $?

It's just that you can't use the sheet name as you see it in the sheet.
As far as the OLE DB provider is concerned, the tablename it sees is the
sheetname + "$". If you were trying to connect to a named range, you
don't need to add the $. I am not sure there is any more significance
than that.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

rberry wrote:
Peter
Top man, that worked as sweet as a nut. Many thanks.

Oh by the way what is the significance of the $?

Regards
Richard

"Peter Jamieson" wrote:

In this case you need to specify a query. If the sheet name is Sheet1,
and assuming you want all the columns and rows, try

objWord.ActiveDocument.MailMerge.OpenDataSource
Name:="E:\Development Databases\WEF\qryLetterMerge.xls",
SQLStatement:="SELECT * FROM [Sheet1$]"

(notice the $ on the end of the sheet name)

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

rberry wrote:
I have programmatically set up a VBA routine from within Access 2003 to load
a Word 2003 mailmerge document (OLE DB) the datasource is a Excel 2003
spreadsheet.

This is all done in the background so the user doesn't see the gubbins of
Word doing its thing!

Problem, whilst it loads into the mailmerge document as expected it hangs up
because in the Select Table Dialogue Box it ask for the worksheet you want to
load as the Range of data selected.

Question is there a way to autiomate this so that is selcets a defined
worksheet "qryLetterMerge" and complete the rest of the automation process
which print the merged letters and then returns the user to access.

Below I show the VBA code I use to launch this process:

' Create Word Instance trapping routine (Early Binding).
Set objWord = GetObject(, "Word.Application")
If Err.Number 0 Then
Set objWord = CreateObject("Word.Application")
End If

'Launch Office Automation and run mailmerge letters.
' Make Word visible.
objWord.Visible = False
DoEvents
objWord.Activate

' Set the mail merge data source as the WEF (Front End) database.
objWord.Documents.Open FileName:=StrDoc, ConfirmConversions:=False,
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""

objWord.ActiveDocument.MailMerge.OpenDataSource
Name:="E:\Development Databases\WEF\qryLetterMerge.xls",
Connection:="qryLetterMerge"

' Manipulating the Word Mailmerge object
With objWord.ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With

' Execute the mail merge.
For intCounter = 1 To RptCopies
.Execute
Next intCounter
End With

' Close Active Word Document
objWord.ActiveDocument.Close wdDoNotSaveChanges

' Close word object instance
objWord.Quit
Set objWord = Nothing


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
Prompt for document properties Save opion fails to prompt R.Evans Microsoft Word Help 6 August 18th 06 12:17 AM
select table HenkR Mailmerge 1 June 7th 06 11:26 AM
"Select Table" dialog with no table selectable [email protected] Mailmerge 3 August 23rd 05 02:57 PM
Suppress prompt to update table of contents LurfysMa New Users 2 July 31st 05 07:44 AM
Word when deleting table sections prompt starting appearing stevez6996 Tables 2 January 4th 05 08:39 PM


All times are GMT +1. The time now is 01:08 PM.

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"