A Microsoft Word forum. Microsoft Office Word Forum - WordBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Microsoft Office Word Forum - WordBanter forum » Microsoft Word Newsgroups » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Selecting which sheet to use from Excel VBA



 
 
Thread Tools Display Modes
  #1  
Old September 18th 07, 07:36 PM posted to microsoft.public.word.mailmerge.fields
BAC
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

Ads
  #2  
Old September 18th 07, 07:55 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,583
Default Selecting which sheet to use from Excel VBA

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


  #3  
Old September 18th 07, 08:32 PM posted to microsoft.public.word.mailmerge.fields
BAC
external usenet poster
 
Posts: 10
Default Selecting which sheet to use from Excel VBA

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



  #4  
Old September 18th 07, 09:01 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,583
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




  #5  
Old September 18th 07, 09:54 PM posted to microsoft.public.word.mailmerge.fields
BAC
external usenet poster
 
Posts: 10
Default Selecting which sheet to use from Excel VBA

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




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





  #6  
Old September 19th 07, 12:12 AM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,583
Default Selecting which sheet to use from Excel VBA

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

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




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






  #7  
Old January 4th 18, 06:41 PM
RaRdEvA RaRdEvA is offline
Junior Member
 
First recorded activity by WordBanter: Jan 2018
Posts: 1
Default

The right way to put the query is like this:

"SELECT * FROM `Hoja1$`"

use the macro recorder to register again the data source, and it will save all the string to connect it.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
sheet tabs in excel JAS Page Layout 1 June 20th 07 03:35 AM
Autosizing Excel Sheet in a word doc Tim Tables 1 August 7th 06 02:36 PM
Excel spread sheet in Word Stan Microsoft Word Help 1 April 18th 05 10:37 PM
Import Excel sheet John Taylor Page Layout 3 March 7th 05 09:04 AM
Can't display excel sheet within Word (EMBED Excel.Sheet.8) HR Duvall Microsoft Word Help 2 February 16th 05 10:27 PM


All times are GMT +1. The time now is 12:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.