I am trying to do a mail merge from access and I have a couple of problems. I am creating a excel file and mergeing off of that. But everytime I save the excel file it prompts if the user wants to overwrite the file. I would like to get rid of that and I have tried:

DoCmd.SetWarnings False
objExcel.ActiveWorkbook.Close True, "c:\merge.xls"
DoCmd.SetWarnings True

But that doesn't do it.

Then when I do the merge:

DoCmd.SetWarnings False
With objword
.Visible = True
.Documents.Open "C:\merge.doc", , False
.ActiveDocument.Mailmerge.OpenDataSource "C:\merge.xls", , , False, True
.ActiveDocument.Mailmerge.Destination = wdSendToNewDocument
.ActiveDocument.Mailmerge.Execute
End With
DoCmd.SetWarnings True

but it prompts the user what sheet to do the merge off of. I would like to get rid of that but as you see I have tried the setwarnings to false with no luck. I am working with access 2003 with a 2000 file and I don't know if it is something 2000+. I know that it used to do it in 97 and it is not working here.


If anyone has any suggestions for me it would be a lot of help. I am a newbee.

Thanks
Kevin

The file overwrite warning probably comes from Excel. Before the close try:

objExcel.DisplayAlerts = False

I don't have time to find out, but I think your merge needs something like:

.ActiveDocument.Mailmerge.OpenDataSource "C:\merge.xls![Sheet1]",,,false,true

In other words, specify the sheet in the data source. Another option could be to delete the extra worksheets in Excel before you close the file. I think if Excel has only one worksheet it knows where to go for the data.

Of course you might try skipping the whole output to Excel and use a query in Access as your data source. Of course that depends on whether your Excel output is more than just a simple query. Not sure the syntax for that but it might be as simple as:

.OpenDataSource CodeDB().Name & "![queryname]" ???

You could record a macro in Word to get all of the proper commands to copy (and update as appropriate) in your Access code. That's usually the way I figure out what I need.


Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org