View Single Post
  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Selecting which sheet to use from Excel VBA

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