View Single Post
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
BAC BAC is offline
external usenet poster
 
Posts: 10
Default Selecting which sheet to use from Excel VBA

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