View Single Post
Old April 11th 19, 12:43 PM
Nayna Nayna is offline
First recorded activity by WordBanter: Apr 2019
Posts: 3

Originally Posted by Peter Jamieson View Post
OK, if my sheet name is "mysheetname", the SQLStatement string should be

"SELECT * FROM [mysheetname$]"

Are the path names of these files quite long? If so, can you try mapping a
drive letter to the path and using that instead?

The dialog box you are seeing is probably the ODBC "Select Table" dialog,
which is probably displaying because Word 2003 tries OLE DB first, then
ODBC, then DDE. But unfortunately it doesn't always put the right Workbook
name in the Workbook box, and since it isn't editable it's usually difficult
to tell whether it has got it right or not. If you click Options... and
check all the options in there you should at least be able to see the name.
Howeverr, that's not really going to help in itself - if for some reason
that OLE DB method /cannot/ be used, I doubt if ODBC will work unless
perhaps the problem is a long path name. But you can always try - you need a
different syntax, and you must have a suitable ODBC machine DSN (there's
usually one called "Excel Files". Then you need

..OpenDataSource _
Name:="", _
.Connection:="DSN=Excel FIles;DBQ=" & xl_file & f.Name & ";DriverID=790;",
SQLStatement:="SELECT * From [Sheetname]", _

(I leave you to construct the SELECT correctly, but it should be exactly the
same as in the OLE DB version)

Peter Jamieson

"BAC" wrote in message
The name of the only worksheet in each workbook is the same as the name of
the workbook (.xls) file. The help file suggested "Connection:=" method
result in a "Select Table" dialog box with the worksheet name displayed.
If I
select the OK button the Mail Merge proceeds correctly, but I have to hit
for each of the 36 files being merged.

If I use the SQLStatement method, I get a dialog box that has the name of
the file in one box, but the other box (which looks like it's supposed to
a list of tables (wks) is empty.

I have tried both methods with every conceivable format for the sheet name
('SheetName$'; "SheetName"; 'SheetName'; Filename!'Sheetname', etc, etc,

I'm going to try your suggestion of opening each file but I shudder to
of how long this may take!


"Peter Jamieson" wrote:

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
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

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], _

(8 is wdMergeSubtypeWord2000)

which should always open sheet 1. But only if you have Excel on the
and so on.

Peter Jamieson

"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) -

"Peter Jamieson" wrote:


.OpenDataSource Name:=xl_file & f.Name, SQLStatement:="SELECT * From

where Sheetname is the name of the sheet, e.g. for the second sheet in
default workbook with 3 sheets, sheetname would be Sheet2$

Peter Jamieson

"BAC" wrote in message
XP pro, Office 2003 pro
I am trying to automate a mailing from multiple Excel workbooks.
I have only 1 worksheet in each book, my routine still pauses
to select the worksheet before completing the merge,

How do i tell Word to continue, using the only worksheet there, or
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
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:
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

End If

Next f
End With 'mw


End Sub

Thanks for valuable information.