by pass selection

[Curt]

When doing mail merge useing a excel worksheet. I wish to bypass the
selection window that asks for entire spreadsheet or print area. Selection
will all ways be entire spreadsheet. Can not reconize which text in code does
this. Here is the area that it comes up in. What can I change to get it to
auto select entire spreadsheet.
Thanks


ActiveDocument.MailMerge.OpenDataSource Name:="C:\mailEcopy.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="",
SQLStatement1 _
:=""

 

[zkid]

Try this instead:

ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\mailEcopy.xls", ConfirmConversions:=False, _
ReadOnly:=True, LinkToSource:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source="C:\mailEcopy.xls";Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OL" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess

 

[Curt]

Doing good until not sure I am doing right thing
Source="c:\mailEcopy.xls"; chg to : AlsoMode=Read; chg to : this cleared
error color
Next
Properties="" (HDR) error expected end of statement tried what I know cannot
seem to hit right combo
Sure need people like you for us
Thanks

 

[zkid]

What version of Office are you using? Also, did you just copy the text I
gave you or revise your own? If you revised your own, trying copying what I
provided.

 

[Curt]

Am useing 2000 office
Yes I copied and pasted your code as I am not that good to compose my own
for this. If office 2000 is the problem the reason I am doing all of this in
2000. Most of the people I will be sharing this with are useing 2000 or if
they upgrade I believe later versions will run this. This goes to the VA when
done to circulate. I am proud to do this program and know it will assist many
across this Nation.
Thank You Greatly

 

[Russ]

Curt,
I tried to adjust zkid's code so it will copy and paste correctly.
There were three main things wrong with it. You can't split right after :=
Because VBA doesn't expect a space after := ;and everything from
Connection:="...Jet OL" is one long quote, so you can't split it without
creating separate concatenated strings at the split points. The third thing
is, you can't leave any single double quotes around the file path within the
long string because VBA will take them as the end of the quote prematurely.
Yes, this whole thing is actually one long statement.

Every line except the last one should have space and underscore characters
at the end of the line.

I didn't test it because I don't have Access on my Mac. So just trying to
compile it gave me an error for wdMergeSubTypeAccess at the end. If it gives
you the same error on a Windows machine then you may have to go into VBA
Editor Tools/Reference menu and check a reference for databases.

ActiveDocument.MailMerge.OpenDataSource Name:="C:\mailEcopy.xls", _
ConfirmConversions:=False, _
ReadOnly:=True, LinkToSource:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=" & _
"Admin;Data Source=""C:\mailEcopy.xls"";Mode=Read;Extended " & _
"Properties=""" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry " & _
"Path="""";Jet OLEDBatabase Password="""";Jet OL", _
SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

 

[Curt]

got to the same place you stated SubType:=wdMergeSubTypeAccess
Should Access be excel as I am not doing this in access. Reason being more
of the people I will be sharing this with know excel. It didn't dawn on me
till I started to write this that it could be excel same with access error
same
The compile error I get is variable not defined Have never been in this area
before
Seems that in my reading there is something about The SubType you have to set.
I did try excel same error still experminting. Useing XP windows office 2000
Thanks to you I am farther down the road than I was
Thanks again

 

[Russ]

Curt,
If you original code was working without using access, then maybe you just
need to change:

SQLStatement:=""

To:

SQLStatement:="SELECT * FROM `Sheet1$`"

Which I got from zkid's code.

Let me know if that works.

 

[Curt]

Got rid of error messages. Procedure still does not perform takes to long
receive errpr about taking to long. Ran it out until it opened letterhead it
did not do the merge as it did befor trying to remove selection. Ive never
got inthis deep before. Quite a learning curve. Am enclosing complete
procediure code. Your explanitaion about underscores and & sign sure helped
me. Thanks Maybe you can see what I am doing wrong with this. Left in my '
comment lines also
Again Thanks Much


Option Explicit
Dim jCtr As Integer
Dim HowMany As Variable
Private Sub OptionButton1_Click()
' blanks Macro This with takes care of space symbols in cell not seen.
Dim jCtr As Integer
Dim HowMany As Integer
OptionButton1.Value = False
Worksheets("Data").Activate
With ActiveSheet
For jCtr = 100 To HowMany
Columns("D:I").Replace _
What:=Space(jCtr), _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next jCtr
End With
'This locates missing entries
Range("d5:m100").Select
Selection.specialcells(xlCellTypeBlanks).Select
Selection.ClearContents
Dim Blanks As Long
Blanks = msgbox(prompt:="Do you have blanks to complete?",
Buttons:=vbYesNo)
If Blanks = vbYes Then
UserForm4.Hide
Userform1.Hide
Exit Sub
If Blanks = vbNo Then
'Creates worksheet for mail merge deleted at end
Sheets("MailE").Select
Sheets("MailE").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Parade\MailEcopy.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If
End If
'Above works OK
'I have a macro in Excel that calls a macro in a Word document with this code:
'Dim wordapp As Application (This was copied from net)
' wordapp.Visible = True
' wordapp.Activate
' wordapp.Run '("My Macro")(Have not used this)
'You use Word's activewindow, kind of like this. Use the macro recorder in
Word to get the syntax
'correct, then just copy it over to Excel and use the With structure:
Dim oWord As Word.Application
Dim myDoc As Word.Document
Set oWord = CreateObject("word.application")
oWord.Application.Visible = True
' slower than 7 year itch doesnot complete action error taking to long
' MailE_2 Macro
' Macro recorded 7/23/2007 by Curtiss A. Greer
With Selection
ChangeFileOpenDirectory "C:\Parade\"
Documents.Open Filename:="LetterHead.doc", ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:="C:\Parade\mailEcopy.xls", _
ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=" & _
"Admin;Data Source=""C:\mailEcopy.xls"";Mode=Read;Extended " & _
"Properties=""" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry " & _
"Path="""";Jet OLEDBatabase Password="""";Jet OL", _
SQLStatement:="SELECT * FROM `Sheet1$`"
', SQLStatement1:="", _
'SubType:=wdMergeSubTypeAccess
'Connection:="", SQLStatement:="", SQLStatement1:=""
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM C:\Parade\mailEcopy.xls WHERE ((Contact_Person IS NOT
NULL ))" _
& ""
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = False
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
ActiveDocument.SaveAs Filename:="mailE2.doc",
FileFormat:=wdFormatDocument _
, LockComments:=False, Password:="", AddToRecentFiles:=True, _
WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False,
SaveAsAOCELetter:= _
False
CommandBars("Stop Recording").Visible = False
End With
' oWord.Application.Quit
' Set oWord = Nothing
Sheets("Parade\mailEcopy").Select
Sheets("Parade\mailEcopy").Delete
UserForm4.Hide
Userform1.Hide
Sheets("Data").Select
Range("A3").Select
Userform1.Show
End Sub

 

[zkid]

Sorry it's taken me so long to get back to this - I've been out of town.

Anyway, the entire problem here is that you are running 2000. The code is
for XP and above. I'll need to boot up a 2000 machine and re-do the code.
The merge process was completely revamped after 2000. So much, in fact, that
you will need to check for which version the current computer is running and
use a decision statement to run whichever code is appropriate.

I'll try to get back to you by tomorrow. As far as the extra spaces, etc.,
that is because of the way this site wraps the text. There is nothing wrong
with the code - just the Office version on which it is being run.

 

[Curt]

Thanks for your reply and help I have went this far novice as I am. For some
reason It does not do the merge as befor. I will enclose complete procedure
as I have it with comments part of it I've got some not. Also it takes to
long and I get error message taking to long. This is not a time senstive
project have until Nov to get done. Vet's Parade day need friday before. Here
is procedure see what you think any changes I can compare to my copy to see
what I did wrong
Thanks

Option Explicit
Dim jCtr As Integer
Dim HowMany As Variable
Private Sub OptionButton1_Click()
' blanks Macro This with takes care of space symbols in cell not seen.
Dim jCtr As Integer
Dim HowMany As Integer
OptionButton1.Value = False
Worksheets("Data").Activate
With ActiveSheet
For jCtr = 100 To HowMany
Columns("D:I").Replace _
What:=Space(jCtr), _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next jCtr
End With
'This locates missing entries
Range("d5:m100").Select
Selection.specialcells(xlCellTypeBlanks).Select
Selection.ClearContents
Dim Blanks As Long
Blanks = msgbox(prompt:="Do you have blanks to complete?",
Buttons:=vbYesNo)
If Blanks = vbYes Then
UserForm4.Hide
Userform1.Hide
Exit Sub
If Blanks = vbNo Then
'Creates worksheet for mail merge deleted at end
Sheets("MailE").Select
Sheets("MailE").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Parade\MailEcopy.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If
End If
'Above works OK
'I have a macro in Excel that calls a macro in a Word document with this code:
'Dim wordapp As Application (This was copied from net)
' wordapp.Visible = True
' wordapp.Activate
' wordapp.Run '("My Macro")(Have not used this)
'You use Word's activewindow, kind of like this. Use the macro recorder in
Word to get the syntax
'correct, then just copy it over to Excel and use the With structure:
Dim oWord As Word.Application
Dim myDoc As Word.Document
Set oWord = CreateObject("word.application")
oWord.Application.Visible = True
' slower than 7 year itch doesnot complete action error taking to long
' MailE_2 Macro
' Macro recorded 7/23/2007 by Curtiss A. Greer
With Selection
ChangeFileOpenDirectory "C:\Parade\"
Documents.Open Filename:="LetterHead.doc", ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:="C:\Parade\mailEcopy.xls", _
ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=" & _
"Admin;Data Source=""C:\mailEcopy.xls"";Mode=Read;Extended " & _
"Properties=""" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry " & _
"Path="""";Jet OLEDBatabase Password="""";Jet OL", _
SQLStatement:="SELECT * FROM `Sheet1$`"
', SQLStatement1:="", _
'SubType:=wdMergeSubTypeAccess
'Connection:="", SQLStatement:="", SQLStatement1:=""
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM C:\Parade\mailEcopy.xls WHERE ((Contact_Person IS NOT
NULL ))" _
& ""
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = False
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
ActiveDocument.SaveAs Filename:="mailE2.doc",
FileFormat:=wdFormatDocument _
, LockComments:=False, Password:="", AddToRecentFiles:=True, _
WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False,
SaveAsAOCELetter:= _
False
CommandBars("Stop Recording").Visible = False
End With
' oWord.Application.Quit
' Set oWord = Nothing
Sheets("Parade\mailEcopy").Select
Sheets("Parade\mailEcopy").Delete
UserForm4.Hide
Userform1.Hide
Sheets("Data").Select
Range("A3").Select
Userform1.Show
End Sub

 

[Russ]

That part that you recorded with a macro, did you literally add the With
Selection and End With after recording the macro, or was it part of the
recording? I'm thinking that it shouldn't be there if it was not part of the
original recording. Also, that is the area where it hangs up, right?

 

[Curt]

yes I added the with selection took it out it went to the letterhead and
stoped also get mess starting excel orange bar in start border useing task
mgr says word not responding closed word in task mgr then returned to program
said out of memory. I am in over my head but not drowning. Where does it end.
Got to have some humor
Thanks Again

 

[zkid]

Turns out I no longer have Office 2000, so it's difficult to help further.
Apparently, in 2000, you don't need to use any of the optional lingo
referring to the connection or SQL.

I found the following code on the web for 2000, though:

Dim wdApp As Word.Application
Dim WordWasNotRunning As Boolean
Dim wdDoc As Word.Document

'Get existing instance of Word if it's open; otherwise create a new one

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
Set wdApp = New Word.Application
WordWasNotRunning = True
End If

wdApp.Visible = True
wdApp.Activate
Dim t
t = "template name.dot"
Set wdDoc = wdApp.Documents.Add(t)

With wdDoc.MailMerge
.OpenDataSource Name:="source file.xls
.Destination = wdSendToNewDocument
.Execute
End With


Also, you should read this article:
http://support.microsoft.com/default.aspx/kb/289830

Try reposting in the Word VBA General community. Make sure you tell people
from the get-go that you need this for Office 2000 and that you are running
it from Excel.

 

[Curt]

Thanks much I will see what I can do
Thanks Again

 

[Curt]

RE: code Finally all I had to do was change connection"" to
"entire sheet" useing 2000
word macro now goes thru procedure straight to printer. All I've got to do
now is control it from Excel
Thanks for walking me thru this

 

[Curt]

just so others may not go thru this for by pass selection in 2000 change
connection from"" to "entire sheet" I was suprised how simple when found. Not
quite clear on how to inject my word macro into this my macro is "macro3"
still at it
Thanks for all your effort it is greatly appreciated.
Thanks Again

 

[Russ]

One way to change the name of your macro is to go into the VBA editor and at
the top of the subroutine where it says macro3, just type over it with a new
significant name. Don't use spaces, underscores are OK. You could capitalize
the beginning of each word in the name and/or use underscores to make it
easier to read. Don't use a name like Main or Delete, etc. that might mean
something to the application, unless you are specifically trying to
intercept a application command with your own code. Macros can be added to
menus, toolbars, and hotkeys combinations.
Go to this site to learn about Word macros.
<http://word.mvps.org/>
I'm sure Excel has a similar site. There's always Google for searching.

 

[Russ]

You 'call' one macro from another. Go into Work VBA help for 'call' for more
information.

 

[Russ]

That should be Word VBA help for 'call'

You 'call' one macro from another. Go into Work VBA help for 'call' for more
information.