Problem with VBA and Access 2010

[mgjdun]

Hi,

After being forced to use Office 2010 (I was using Office 2003) I'm encountering problems with my database. The function below was working fine in Access 2003. However, now I'm forced to switch to Office 2010, and the function isn't working anymore. Can anybody help me?

Best regards,
Mathijs

Public Function MergeQuest(strFileName As String, strQueryName As String, strDBpath As String)

On Error GoTo ErrHandling

Dim objDoc As Word.Document
Dim objWord As Word.Application
Dim blnCreated As Boolean

On Error Resume Next
Set objWord = GetObject("Word.Application")
If Err Then
    Set objWord = CreateObject("Word.Application")
    blnCreated = True
End If

On Error GoTo ErrHandling

Set objDoc = objWord.Documents.Open("" & strFileName & "")

'Make Word Visible
objWord.Visible = True

'Execute the MailMerge
With objDoc.mailmerge
    'Set Merge Data Source
    objDoc.mailmerge.OpenDataSource Name:=strDBpath, _
    LinktoSource:=True, _
    Connection:="QUERY " & strQueryName, _
    SQLStatement:="SELECT * FROM " & strQueryName
    .Destination = wdSendToNewDocument
    .Execute
    'objWord.ActiveDocument.PrintOut False
    'objWord.ActiveDocument.Close wdDoNotSaveChanges
    ' print the document
    objWord.ActiveDocument.PrintOut
    Do While objWord.BackgroundPrintingStatus > 0
    Loop
End With

'probleem oplossen: automate multiple instances of Microsoft Word simultaneously
objWord.NormalTemplate.Saved = True
'Close The form files and the merged document
objDoc.Close wdDoNotSaveChanges

If blnCreated Then
objWord.Quit
End If

Set objDoc = Nothing
Set objWord = Nothing

Exit Function

ErrHandling:
MsgBox "Whoops" 'Better error handling of course
End Function

 

[spikepl]

"Not working" means you are unhappy. Not very useful information.

What fails, while doing what, and where? What errors? What does it do? As opposed to what?

 

[Mr. B]

First, I do agree with spikepl in that more detailed information about exactly what is "not working" would really be helpful.

Next, from the looks of your code and your statement about being forced to migrate to Access 2010 suggests to me that there may be a problem with "references". If you have migrated to the new version of MS Office, and you did not update your references to now refer to the new version of Office then that could very well be the problem.

If that is not it, please post back with more details about the exact problem.

 

[mgjdun]

Sorry, I could've given more detail.

It always ends with the messagebox "Whoops", while it used to (in Office 2003) do the mail merge, print it and close Word. I think the error is somewhere below the second "On Error GoTo ErrHandling".

I already checked the references, they should be okay...

 

[Mr. B]

For testing, try commenting out all of the lines that start with "On Error". This will allow your code to stop at the error instead of proceeding to the error handling code.

When your code stops, you can identify the exact line that is causing the problem.

 

[vbaInet]

You could try changing this:

Dim objDoc As [COLOR=Red]Word.Document[/COLOR]
Dim objWord As [COLOR=Red]Word.Application[/COLOR]

to this:

Dim objDoc As [COLOR=Blue]Object[/COLOR]
Dim objWord As[COLOR=Blue] Object[/COLOR]

 

[mgjdun]

Dim objDoc As [COLOR=Blue]Object[/COLOR]
Dim objWord As[COLOR=Blue] Object[/COLOR]

That didn't help. I'll try commenting out the lines that start with "On Error".

 

[JANR]

It did not work because you probably still has Microsoft Word refrenced in your refrences.

What VbaINet is suggesting is to use LateBinding of word so that different Word versions dosen't clash, remove the refrence to Word and try again.

However using latebinding you must supply constants to any wd.... methodes you use in code, I only found one that you use namely

wdDoNotSaveChanges

so add this line to your prosedure

Const wdDoNotSaveChanges = 0

JR

 

[JANR]

btw here is a list over Word Constants :http://msdn.microsoft.com/en-us/library/aa211923(v=office.11).aspx

JR

 

[vbaInet]

It did not work because you probably still has Microsoft Word refrenced in your refrences.

What VbaINet is suggesting is to use LateBinding of word so that different Word versions dosen't clash, remove the refrence to Word and try again.

However using latebinding you must supply constants to any wd.... methodes you use in code, I only found one that you use namely

wdDoNotSaveChanges

so add this line to your prosedure

Const wdDoNotSaveChanges = 0

JR

... and comment out all the On Error ... code lines until you get it working.

 

[mgjdun]

YES!!! That did the trick! Thank you very much JANR and vbaInet! And also Mr. B and spikepl for the helpful comments.

'Dim objDoc As Word.Document
'Dim objWord As Word.Application
Dim objDoc As Object
Dim objWord As Object
Const wdDoNotSaveChanges = 0

 

[vbaInet]

Good to hear!

By the way, it's not a particularly good idea to use reserved keywords as constants. If at some point you add the right reference to Word it may conflict. But in most cases it should override the original method or function.

 

[mgjdun]

I'm afraid I was celebrating too soon. I copypasted the code in a module, and after making the above changes and going through it using F8, it worked. However, when I use the code as a function, and run it from a form by pressing a command button, it again says whoops.

 

[vbaInet]

Please next time you post about an error, don't just say "whoops", tell us the exact error message.

 

[mgjdun]

I had to figure out how to comment out the On Error, I added:

ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
Exit Sub

That's what you mean with commenting out the On Error? If so, is returns Error #4198.

 

[vbaInet]

Commenting out a line in VBA means prefixing the line with a single quote, e.g.:

[COLOR=Red]'[/COLOR] On Error GoTo ErrHandling

Can you see the single quote? What that does is it makes the compiler skip that line of code. Also, when you have error handling in place and an error occurs, it won't give you much debugging tips.

So comment out all the lines that begin with On Error, re-run your code and when the error message comes up click the Debug button and it will highlight a line of code in yellow. Tell us what the error message is (not the code) and the line it highlighted.

 

[mgjdun]

It returns:
Run-time error ..... (a long number)
Automation error
Invalid Syntax

It highlights:
Set objWord = GetObject("Word.Application")

 

[vbaInet]

Ok, uncomment On Error Resume Next and rerun it.

 

[mgjdun]

Ok, uncomment On Error Resume Next and rerun it.

Yes, I already saw where you're getting at. After making the following changes I got it to work!

Private Sub MergeQuest(strFileName As String, strQueryName As String, strDBpath As String)

[COLOR="red"]'[/COLOR]On Error GoTo ErrHandling

Dim blnCreated As Boolean
Dim objDoc As Object
Dim objWord As Object
Const wdDoNotSaveChanges = 0

Set objWord = GetObject([COLOR="Red"], [/COLOR]"Word.Application")
[COLOR="red"]    If Err.Number <> 0 Then blnCreated = True
    Err.Clear    ' Clear Err object in case error occurred.[/COLOR]

[COLOR="red"]'[/COLOR]On Error GoTo ErrHandling

'Set objDoc = objWord.Documents.Open(strFileName)
[COLOR="red"]Set objDoc = GetObject(strFileName)[/COLOR] 'instead of previous line

'Make Word Visible
objWord.[COLOR="red"]Application[/COLOR].Visible = True

'Execute the MailMerge
With objDoc.mailmerge
    'Set Merge Data Source
    objDoc.mailmerge.OpenDataSource Name:=strDBpath, _
        LinktoSource:=True, _
        Connection:="QUERY " & strQueryName, _
        SQLStatement:="SELECT * FROM " & strQueryName
    .Destination = wdSendToNewDocument
    .Execute
    objWord.ActiveDocument.PrintOut False
    objWord.ActiveDocument.Close wdDoNotSaveChanges
'    ' print the document (misschien werkt dit beter?)
'    objWord.ActiveDocument.PrintOut
'    Do While objWord.BackgroundPrintingStatus > 0
'    Loop
End With

'probleem oplossen: automate multiple instances of Microsoft Word simultaneously
objWord.NormalTemplate.Saved = True
'Close The form files and the merged document
objDoc.Close wdDoNotSaveChanges

If blnCreated Then
objWord.Quit
End If

Set objDoc = Nothing
Set objWord = Nothing

'Exit Sub
'
'ErrHandling:
'    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
'    Exit Sub
End Sub

 

[vbaInet]

Good catch with the comma in GetObject.

I would still include the CreateObject line either with On Error Resume Next (like you had in your first post) or On Error Go To someline and CreateObject there (preferrable).