Integrating Word and Excel

[JennyEx]

Hello All,



This is my first post here. I have no formal education in VB, but with the help of internet based forums like this one and the helpful members that use, I have managed over a course of two years to develop an Excel based application that wonderful potential. I am nearing completion, but have hit a significant hurdle that most have been unable to provide assistance with to over come.

My Excel application unites a data from a worksheet with Word's mail merge functions. The desire is that with a push of a button in Excel, the completed mail merge document will be completed, saved and printed, with no user interaction other than the initial button push. I have developed the mail merge document, and set it up effectively to the dataset with the appropriate filters and sorting actions needed via Word's GUI.

And now I'm stuck. The integration.

This is the code that I have established so far.

Sub report()
Dim ObjWord As Object
Set ObjWord = CreateObject("Word.Application")
ObjWord.Documents.Open ("E:\SA09-02\Reports10\SO10-DR_HPL.Doc"), vbNormalFocus
ObjWord.Visible = True
ObjWord.MailMerge.OpenDataSource Name:="E:\SA09-02\Reports\reportdata.xls" _
, ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="", SQLStatement:="", SQLStatement1:=""
End Sub

The document opens, but without the mail merge functionality. It results in and error with the red highlighted code "Object doesn't support this property or method."

It also does not address the specific worksheet from within the workbook where the data is.

I can only assume that this is the same line in which the SQL for filtering and sorting is applied as well. The Word prompt requesting approval to use SQL prior to opening the merge document is:
SELECT * FROM 'CONTROL_1$' WHERE 'subresp' = 'HPL1' AND 'Type' = 'DR' ORDER BY 'Start' ASC, 'Unit$' ASC
I'm not sure how to integrate that into the code.

The next would be how to bypass the user needing to push the "Merge To New Document" button to get actually prepare the document.

And of course, returning the user back to the Excel application after the report has been acknowledged after printing.

I am desperate for help at this point so close to the completion of my application, so I hope someone can help me here. I do not have the strongest grasp of highly complex VBA, so my apologies in advance if my blondness seems apparent, and ask for patience.

Thanks you!!!

Jenn

[lucas]

Hi Jenn, Welcome to the board.

Is your excel data in rows so that each row will be sent to a word document?

[JennyEx]

Hi Jenn, Welcome to the board.

Is your excel data in rows so that each row will be sent to a word document?

Each row is capable of being sent to a word document. Which ones go to which report (there are a few different ones) will depend on the filtering (ie mail merge recipients)

[lucas]

I'm not much on mailmerge but I have an example that I can post and maybe you can sort it out from that.

I do have a mailmerge imitation that is much easier to work with and uses bookmarks instead of formfields or whatever mailmerge uses.

You can click in the cell in column A and you get a checkmark. Each row that is checked will be sent to the word document. It does not save one mailmerge file, it saves one document for each row of data.

Which would you like to see?

[JennyEx]

Hey Lucas!

Not sure what would be most effective at this point. I took me two years to tweak my Excel application ... an application who's primary purpose is to prepare data for Word mail merge. I'd like to stat away from playing around with that.

Maybe your first option would be better ... it may be easier to reproduce the word documents than the data. In this case, does the word still access Excel for its source of data, is it "filterable" and most importantly, the data must present data in "directory" format ... ie ... several records per page.

Jenn

[lucas]

Jenn, I tinkered with it and filtered the state row to just CA and it only added two letters to the mailmerge.

The one thing it doesn't seem to do is print it for you but it leaves the mailmerge document open so you can just print it. I'm sure we can figure out how to get it to print the document and save it and close it if it looks like you can work with it.

It is an excel file and is set up as a small database. The userform is mostly for adding, editing and deleting entries.

What I did was just filter for the state of CA and then hit the button to open the form and just hit the button in the lower left corner that says Merge FRM. That's it as it's set up.

Again. I'm not much help in how many columns and where they get sent to in the word doc in this format.

I hope this helps. Just unzip both files in the same directory and run the excel file.

[lucas]

Jenn, I have to leave for a while so I am going to post the other set of mailmerge files that use bookmarks.

Remember, you can Change your Template to suit your needs. The bookmarks are filled in order from 1 to 2 to 3, etc. And from the left of the excel row one at a time.

In other words the first excel cel(Column B) in the row goes to bookmark bm_1.

The data in column C goes to bm_2, etc.

It is very efficient and easy to maintain. You decide and if I can help you more please post back.

[lucas]

I moved this to the Word forum in hopes that someone with real experience with mailmerge might give Jenn some assistance.

[Tinbendr]

I'm confused on your setup.

Is your dataset different than the Excel App? OR is the data IN the Excel App?

If the latter, just manually use Excel to perform the mailmerge via a Word template.

[Opv]

I moved this to the Word forum in hopes that someone with real experience with mailmerge might give Jenn some assistance.

Thanks, Lucas. I checked out the thread and clicked several times on the zip link but couldn't get it to do anything. Has the file been removed?

[Opv]

I'm confused on your setup.

Is your dataset different than the Excel App? OR is the data IN the Excel App?

If the latter, just manually use Excel to perform the mailmerge via a Word template.

The database is an Excel document. The merge via a Word template is precisely what I'm trying to do. I have the correct Avery template selected.

[lucas]

I just downloaded it. Did you try right clicking and select "save target as"

If you can't get it and still want to look at it, pm me with an email addy.

[Opv]

I just downloaded it. Did you try right clicking and select "save target as"

If you can't get it and still want to look at it, pm me with an email addy.

Thanks, I tried right clicking but I'm in Firefox and for some reason I'm not seeing a Save Target option. I'll send a PM shortly.