Selecting which sheet to use from Excel VBA

[BAC]

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

 

[Peter Jamieson]

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$

 

[BAC]

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) &
"'$"

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

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

 

[Peter Jamieson]

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

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)
&
"'$"

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

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

 

[BAC]

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 will
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 OK
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 be
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, etc.)

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

Thanx

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

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)
&
"'$"

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

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

 

[Peter Jamieson]

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]", _
Subtype:=8

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

--
Peter Jamieson
http://tips.pjmsn.me.uk

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
will
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
OK
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
be
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,
etc.)

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

Thanx

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

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)
&
"'$"


:

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

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