OpenDataSource using Excel workbook

[NJS]

Good day all!
I need to use OpenDataSource to automatically re-connect/refresh a doc.
I always get prompted for the 'sheet' even though there is only one
worksheet in the workbook
Is there any way to suppress this prompt, and default to sheet1$ (or
whatever)??
thank you

 

[Doug Robbins - Word MVP]

The following is a recorded macro of the action of attaching a sheet from an
Excel file as a data source:

ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\Doug Robbins\My Documents\My Data
Sources\testsource.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\Doug Robbins\My Documents\My Data
Sources\testsource.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path=" _
, SQLStatement:="SELECT * FROM `Sheet2$`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess

Also checkout the OpenDataSource item in the Visual Basic Help file.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[NJS]

Excellent!, thanks Doug

The following is a recorded macro of the action of attaching a sheet from an
Excel file as a data source:

ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\Doug Robbins\My Documents\My Data
Sources\testsource.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\Doug Robbins\My Documents\My Data
Sources\testsource.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path=" _
, SQLStatement:="SELECT * FROM `Sheet2$`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess

Also checkout the OpenDataSource item in the Visual Basic Help file.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP