Automation error after Application.Run MacroName (1 Viewer)

perlfan · May 25, 2017

Hi there,

without any problems I execute a macro in a Word file from Access with this command:

Code:

Application.Run MacroName:="fill_with_data"

Now I would like to pass a parameter with this command:

Code:

Application.Run MacroName:="fill_with_data", varg1:=strDirectoryPath

In Word I retrieve the variable with:

Code:

Public Sub fill_with_data(Optional strDirectoryPath As String)

Even though I only check if the variable has been transmitted with MsgBox (strDirectoryPath) in the Word file, all of a sudden I get an automation error in Access. Can anybody help me with this? These are the only changes I did, and the message box shows the variable content, so passing the parameter works. But what does cause the error? Thanks for help.

Frank

Uncle Gizmo · May 25, 2017

Try making "varg1" a string variable...

perlfan · May 26, 2017

That didn't do it :-( I might use a workaround with a temporary txt file...

JHB · May 26, 2017

Could you post the whole code in that procedure/Sub then I can't see any references to the Word application?

perlfan · May 26, 2017

Sure, here we go! (In Word I only retrieve the variable as shown above)

Code:

Public Function open_quote_in_word(req_quoteID)
Dim strFileName, varg1, strDirectoryPath As String

strDirectoryPath = CurrentProject.Path & "\"

Set db = CurrentDb          
strSQL = "Select quotes.QuoteID_ext_long, company_information.quote_template, company_information.filename_format from company_information INNER JOIN (customers INNER JOIN quotes ON customers.CustomerID = quotes.CustomerID) ON company_information.ID = customers.ResponsibleCompany WHERE quotes.QuoteID= " & req_quoteID & ""
Set rs = db.OpenRecordset(strSQL)  ' Recordset oeffnen
Template = rs!quote_template

strFileName = Template
If FileExists(strFileName) = True Then
            Set wordApp = CreateObject("Word.Application")
            With wordApp
                .Documents.Open filename:=strFileName
                On Error GoTo ende
                .Application.Run MacroName:="fill_with_data", varg1:=strDirectoryPath
                .Visible = True
                .WindowState = wdWindowStateMaximize
                .Activate
            End With
....

JHB · May 26, 2017

I just tested you code and no error occurs by me.
I would suggest you comment out the error handling to show which code line cause the error.
Maybe create a new Word file or create a new database and import all into it.
Else strip down your database and post it with some sample data + the Word file.

perlfan · May 26, 2017

The error occurs here:

Code:

.Application.Run MacroName:="fill_with_data", varg1:=strDirectoryPath

The error I get in Access is:
Automation error. Exception occurred

This is caused by passing the parameter to the word file as everything runs smootly when I do not pass the parameter.
Debugging the access and word files shows no errors.

The parameter is defined as string in Access & Word.

JHB · May 26, 2017

I post the sample database I've used, without problem, try it and see if you get any error.

perlfan · May 26, 2017

Thank you, this help to narrow down the error. Your file works fine on my PC.
I realized that the error occurs as soon as I add the optional parameter in my Word file: "Public Sub fill_with_data(Optional strDirectoryPath As String)".

Even if I don't pass anthing to the Word file, the automation error shows up. It's the exact header used by you so it must be something in my Word VBA code that doesn't like the "Optional strDirectoryPath As String" part. This is frustrating...

JHB · May 26, 2017

Could you post your Word file?

The_Doc_Man · May 26, 2017

It MIGHT be useful for you to run your misbehaving operation and vary carefully note the time of day (to the second) at which it occurs. If you have a "clock" gadget on your desktop, that would help.

Then, open your Control Panel >> Administrative Tools >> Event Viewer in the system and application logs for that exact time of day and maybe up to 5 seconds before that time to see if anything more specific is logged. The word "Exception" (as in Exception Occurred) has a specific meaning in system context and I would expect an event log to be present. If there IS such a log, copy the Exception code which will look like 0x plus 8 more hexadecimal digits, probably something like 0x80000005 or something similar.

perlfan · May 26, 2017

Yes, of course. Attached you'll find the Word template file. It's a bit lengthy though. Maybe you'll be directed to the error somehow.
Thanks for taking a look, FRANK

JHB · May 26, 2017

I didn't get any error using your Word document.

perlfan · May 27, 2017

Strange, but I get the automation error again! All I did is:
1. Changed this line in your database to:

Code:

strFileName = "Quote template_afp.docm"

2. Copied the beginning of your word file into mine:

Code:

Public Sub fill_with_data(Optional strDirectoryPath As String)
'
' fill_with_data Makro
' Makro indspillet 26-05-2017 af Joern
'
 MsgBox ("Hej" & strDirectoryPath)
  Selection.TypeText Text:="der asa"

Could you please attach your two files that work (incl. the template file)? Maybe you made further adapations to the template file that cause the error in my file... Thank you so much!!

JHB · May 27, 2017

Hmm - strange, no problem here at all.
It's just before I say that either your MS Access or your Word installation is corrupt.
I use Windows 8, MS-Access 2010 and Word 2000 with program "extension" to open newer word files.
Could you try to run your program on another computer?

The_Doc_Man · May 27, 2017

Have you tried to check for an error in the system error logs or the application error logs? If not, try it. If so, at least advise us if there was an entry.

Gasman · May 27, 2017

You have not said what error message you get.
I was having similar problems with Excel.

https://www.access-programmers.co.uk/forums/showthread.php?t=288236

Caused by an addin.

perlfan · May 28, 2017

Many thanks, however I can't open the .docm file at all (Win 10/Office 2013).
Error message: "Die Datei kann nicht geöffnet werden, weil das Dateiformat nicht der Dateierweiterung entspricht" (translation - The file cannot be opened as the file format is not consistent with the file extension).

perlfan · May 28, 2017

The_Doc_Man said:
....
Then, open your Control Panel >> Administrative Tools >> Event Viewer in the system and application logs for that exact time of day and maybe up to 5 seconds before that time to see if anything more specific is logged. ....

I triggered the error again but there is not error log in the application log for today. So apparently it's not logged there. However the exact message I get in Access is:

Code:

Laufzeitfehler: '-2147352567 (80020009)':
Automatisierungsfehler
Ausnahmefehler aufgetreten.

(literal translation: runtime-error -> automation error -> exception error occurred)

BTW: With no addins activated in Word the error still occurs.

Gasman · May 28, 2017

Well if you cannot open the document manually, I cannot see how it could be done via automation?

perlfan said:
Many thanks, however I can't open the .docm file at all (Win 10/Office 2013).
Error message: "Die Datei kann nicht geöffnet werden, weil das Dateiformat nicht der Dateierweiterung entspricht" (translation - The file cannot be opened as the file format is not consistent with the file extension).

Automation error after Application.Run MacroName (1 Viewer)

Registered User.

Nifty Access Guy

Registered User.

Have been here a while

Registered User.

Have been here a while

Registered User.

Have been here a while

Attachments

Registered User.

Have been here a while

Immoderate Moderator

Registered User.

Attachments

Have been here a while

Registered User.

Have been here a while

Attachments

Immoderate Moderator

Enthusiastic Amateur

Registered User.

Registered User.

Enthusiastic Amateur

Similar threads

Users who are viewing this thread