Solved: Runtime Error 462 Can't Find Excel DataSource In Word MailMerge

[SammyB]

Hi

Firstly, I hope this is on the correct forum as it an Excel/Word issue.

After a week of searching forums and borrowing bits of code, I finally have an end product that is run from Excel but calls a Word Mailmerge, using an Excel Datasource.

I have five macros which all do the same job with the exception of pulling information for different days of the week. When I run Monday's Macro everything is fine, but when I run Tuesday's macro I get a Runtime error 462 saying it can't find the datasource. When I click into the VBA editor and click debug, reset and then run it again it is fine.

Does anyone have any ideas as to what I may have missed. The piece of code that is causing the problems is as follows:



Dim wdApp As Word.Application, wdDoc As Word.Document
On Error Resume Next

Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application") End If
On Error Goto 0


Set wdDoc = wdApp.Documents.Open("C:\garden waste\mergealetter.doc")
wdApp.Visible = True
ActiveDocument.MailMerge.OpenDataSource Name:="c:\garden waste\mergeadata.xls", _
SQLStatement:="SELECT * FROM `Sheet1$`"
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
CommandBars("Task Pane").Visible = False
wdApp.Quit SaveChanges:=wdDoNotSaveChanges
Set wdApp = Nothing
Application.Goto Reference:="home"
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub





Any help would be most appreciated.

Many thanks

Sam

[fumei]

What is "Monday's Macro "?
What is "Tuesday's macro"?

Why are they different? Are they different? I see no dates in your code. Which one is the one you posted? is this Monday or Tuesday?

"click debug, reset" - Reset? Reset what?

"then run it again it is fine." - Fine, in that it run fine from the VBE? Or fine, in that you can run the macro properly from Excel?

Nothing jumps out at me as being wrong. I assume that each of the macro has the correct Sheet named.....

[SammyB]

Thanks for replying Gerry, sorry if my info was a bit vague. I only copied the bit that was causing the problem, the whole procedure works like this:

1. From an excel database the macro extracts all the information for customers who will have their bin delivered on a Monday using an advanced filter.

2. This information is copied and pasted into a new Excel workbook called mergeadata.xls, which only contains this data which is relevant to the merge and is stored on sheet1. This workbook is then saved to be used as the DataSource.

3. The macro then calls Word and starts the Mailmerge using an existing form letter called mergealetter.doc which sends the resulting merge straight to the printer. No documents are saved and word is closed.

4. This macro is then duplicated for each day of the week, each instance uses advanced filter to extract the information from the main database table for each day. Resulting in 5 separate macros.

5. The macros are assigned to 5 separate buttons on a custom toolbar in Excel.

Problems occur:

6. When I run the first macro everything is fine. When I run the second macro, the first part is fine until it hits the datasource line. the mergealetter.doc is opened but it does not complete the merge.

7. When I switch into the visual basic editor I have the run-time error 462. I then click Debug and on the Visual basic editor toolbar I click the Reset button.

8. Back in my Excel workbook I run the same macro again and it works perfectly.

9. Now I'm confused!

Over to you...



Thanks again

Sam
PS - After a lot of googling - I have found people with similar problems but no answers!

[fumei]

OK, let me run though this.

You have 5 macros. They are identical in that they get a datasource form the same file The only difference is that this file is different because it is dynamically created by some other code (not posted). That is:

Monday macro gets XXXXX data and creates the datasource file mergeadata.xls.

Tuesday macro gets YYYYY data and creates the datasource file mergeadata.xls.

Wenesday macro gets ZZZZZ data and creates the datasource file mergeadata.xls.

But in all cases, the macros to get the datasource from Sheet 1 of mergeadata.xls.

So the separate macros must have other code (not posted) that does the extraction of data that is then dumped into mergeadata.xls. Right?

1. Is there some other code that deals with mergeadata.xls?

2. Is it possible that there is some other process going on that you need to wait for? This could explain why doing the ReSet works. By the time you do the reset, that process is complete, so now it works.

3. Is the previous data content of mergeadata.xls removed before the next macro is fired?

Other than that...I don't know. I am sort of stumped myself.

[SammyB]

Hi Gerry

Thanks once again for replying, you seem to have sussed what I am trying to do, in answer to your questions -

1. Yes there is other code that appears before the bit I posted (quite a lot in fact, but all excel functions that work fine) Would you like me to post it to the forum or mail it to you separately (if you tell me how).

2. I have tried waiting between macros and even checking processes in task manager to see if there is anything obvious holding it up, but the same problem exists.

3. No, part of the code which I didn't post opens mergeadata.xls and then clears the previous day's entries, before copying and pasting the new entries. I am now wondering if this could be the problem since you asked you the question.

Thanks again for your time.

Cheers

Sam

[mdmackillop]

Hi Sammy,
As the datasource is constant, why not just save the document as a mailmerge document and add the merge code to a Document Open event.

Don't know if it will solve the problem, but it would appear to simplify the process.
Regards
MD

[SammyB]

Hi MD

Thanks for your input - it sounds like you could be onto something as the datasource has already been attached to the document and it does remain the same (although the data is changed by excel but re-saved with the same workbook name). Would I just miss out the datasource statement? So it would look something like this:

Set wdDoc = wdApp.Documents.Open("C:\garden waste\mergealetter.doc")
wdApp.Visible = True
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With


Just tried it but now crashes on

.Destination = wdSendToPrinter

Any other suggestions gratefully received

Thanks

Sam

[mdmackillop]

Happened to me too, but I thought it was just my setup! Now I know it's not, I'll investigate further.

[mdmackillop]

I'm suspecting that there is a problem with this warning. (Display Alerts doesn't stop it) The merge is running OK if I open the merge document from word (no macro)

[mdmackillop]

I've applied the workaround here which seems to have solved the problem. http://support.microsoft.com/kb/825765/en-us

Excel code

Sub DoMerge()
Dim wdApp As Word.Application, wdDoc As Word.Document
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdDoc = wdApp.Documents.Open("C:\garden waste\mergealetter.doc")
wdApp.Visible = True
CommandBars("Task Pane").Visible = False
wdApp.Quit SaveChanges:=wdDoNotSaveChanges
Set wdApp = Nothing
Application.Goto Reference:="home"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub



Word Code

Private Sub Document_Open()
Application.DisplayAlerts = False
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Application.DisplayAlerts = True
End Sub

[SammyB]

Thanks MD, I'll give that a go, and report back.

Sam

[Dave]

I think you had some type of error which wasn't handled leaving the Word application running and/or your code doesn't quit the Word application before starting another application ie. Tuesday's macro. Word yields erroneous results when 2 applications are running. Trial your original code by running Monday's macro then Tuesdays Macro then hit Cntrl-Alt-Del. Are there 2 Winwords running? Md's prevention fixes this problem. Dave

[SammyB]

Yipee! It works - Thank you soooooo much - You are a Star! That was the one KB article I think I hadn't read, of course if I had read my screen properly I should have linked the SQL Security as the issue, but sometimes you can't see the wood from the trees.

Anyway, I am now on :cloud9:

Thank you again

Sam xxx

[SammyB]

Just thought it was worth posting an update to this thread in case anybody has a similar problem in the future.

Went into work this morning all excited that I had a fix to the problem, replicated everything that I had done on my home machine but exactly the same problem occured!

Had a cup of coffee and determined not to be beaten, I stared at my code long and hard (willing the answer to come to me!)

And it did (a truly inspired moment)

It was crashing on this line:

With ActiveDocument.MailMerge

So I changed it to:

With wdDoc.MailMerge

And it worked - perfectly!

A happy ending. :)

[mdmackillop]

Hi Sam,
If you run all the code from Excel then wdDoc is correct. If you paste the merge code as my post 10 into the Word document, then ActiveDocument is correct.
Glad you got it working, whichever way.
Regards
MD

[SammyB]

Thanks MD - Yes all code was run from Excel, used your Word code (10)but sloted it in to the existing Excel code that I had. I now understand the difference (completely logical of course) I'm slowly getting to grips with this VBA malarky - trouble is it gets a bit addictive!

Sam