The help file suggested:
.OpenDataSource Name:=xl_file & f.Name, ReadOnly:=True, _
Connection:= Left(f.Name, Len(f.Name) - 4)
Where Left(f.Name, Len(f.Name) - 4) is the name of the sheet
What the help file probably meansby "name of the sheet" is the same thing
as I mean, i.e. "the name of the worksheet", which is different from "the
name of the file containing the workbook", i.e. the name of the .xls file.
The trouble is that if you do not know the name of the sheet, you will
probably have to use DDE to open the workbook, e.g. using
..OpenDataSource _
Name:=xl_file & f.Name, _
Connection:=[Entire Spreadsheet], _
Subtype:=8
(8 is wdMergeSubtypeWord2000)
which should always open sheet 1. But only if you have Excel on the system,
and so on.
--
Peter Jamieson
http://tips.pjmsn.me.uk
"BAC" wrote in message
...
Sorry, no..
The help file suggested:
.OpenDataSource Name:=xl_file & f.Name, ReadOnly:=True, _
Connection:= Left(f.Name, Len(f.Name) - 4)
Where Left(f.Name, Len(f.Name) - 4) is the name of the sheet
which doesn't work either even when we use "'Left(f.Name, Len(f.Name) - 4)
&
"'$"
"Peter Jamieson" wrote:
Try
.OpenDataSource Name:=xl_file & f.Name, SQLStatement:="SELECT * From
[Sheetname]"
where Sheetname is the name of the sheet, e.g. for the second sheet in a
default workbook with 3 sheets, sheetname would be Sheet2$
--
Peter Jamieson
http://tips.pjmsn.me.uk
"BAC" wrote in message
...
XP pro, Office 2003 pro
I am trying to automate a mailing from multiple Excel workbooks. Even
though
I have only 1 worksheet in each book, my routine still pauses waiting
for
me
to select the worksheet before completing the merge,
How do i tell Word to continue, using the only worksheet there, or what
format would I use in VBA to add the sheet name to the file name:
= is where routine pauses with box to select Table from File
Sub Merge_Letters()
Dim mw As Word.Application
Dim curr_doc As Word.Document
Dim fs, fd, ff As Object
Dim ex_Date As Date
Const wd_file = "C:\WorkingFiles\BusinessLeasing\Client Manager Pre
Approval
Letter.doc"
Const xl_file = "C:\WorkingFiles\BusinessLeasing\Execs\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set fd = fs.GetFolder(xl_file)
Set ff = fd.Files
Set mw = CreateObject("word.application")
mw.Visible = True
'Start by getting new Expiration date:
GetDate:
ex_Date = InputBox("Enter offer expiration date (e.g. 12/31/07):")
If Not IsDate(ex_Date) Then
MsgBox ex_Date & " is not a date. Please try again!", vbOKCancel
If vbCancel Then Exit Sub
GoTo GetDate
End If
With mw
.Documents.Open FileName:=wd_file
For Each f In ff
If Right(f.Name, 4) = ".xls" Then
wd_name = Left(f.Name, Len(f.Name) - 4) & ".doc"
With ActiveDocument.MailMerge
= .OpenDataSource Name:=xl_file & f.Name, ReadOnly:=True
= .Execute
End With 'Activedocument
.ActiveDocument.SaveAs xl_file & wd_name
.ActiveDocument.Close
End If
Next f
End With 'mw
mw.Application.Quit
Application.Quit
End Sub