objExcel.Application.Run Parameter Syntax

[Azerty99]

Calling Excel from Word.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have a sub in Excel called ChooseRegion, Excel syntax:<o></o>
ChooseRegion 2<o></o>
Or <o></o>
Call ChooseRegion(2)<o></o>
<o></o>
I have a Sub called Reset which resets Excel to the top of the list:<o></o>
Excel:<o></o>
Call ChooseRegion(0)<o></o>
This works fine <o></o>
<o></o>
Syntax From Word:<o></o>
objExcel.Application.Run ("Reset")<o></o>
This runs within Excel<o></o>
Call ChooseRegion(0)<o></o>
This works fine <o></o>
<o></o>
But<o></o>
Syntax From Word:<o></o>
objExcel.Application.Run ("ChooseRegion(2)")<o></o>
Doesn't run the macro <o></o>
<o></o>
The help suggests the following syntax:<o></o>
objExcel.Application.Run ("ChooseRegion" ,"2") <o></o>
But the VBA editor claims that this is bad syntax and flags it as Red<o></o>
<o></o>
Details about the functionality:<o></o>
The chooseRegion macro sets the filter name in a data matrix for a chart. Word opens Excel, retrieves data from an External Database, filters the data and plots a chart. It then copies this chart into Word. There are 12 Regions. <o></o>
<o></o>
Office 2003, Windows XP<o></o>
<o></o>
Any ideas as the the correct syntax?

This is in Excel as a Module:
Public Sub ChooseRegion(RegionNum As Integer)
Sheets("Chart").Select
Range("SelRegion").Value = RegionNum
RegionName = Sheets("Info").Range("RegionName") 'This is a VLookup
Selection.AutoFilter Field:=2, Criteria1:=RegionName
Debug.Print "Chosen Region: " & RegionName
End Sub<o></o>

 

[Colin Legg]

Untested**

Either of these should be fine:

Call objExcel.Run("ChooseRegion", 2)

objExcel.Run "ChooseRegion", 2

 

[Azerty99]

Colin,

Works just fine. Thank you very much Sir!

Jon

 

[Azerty99]

Although now my code runs fine, I have found that the first time I call an Excel Sub from Word, it ignores the call. Thereafter it runs everything else. So I have inserted a dummy call just to get past that "funny".

I do hope this is not indicative of something else going wrong!

 

[Colin Legg]

Hi Jon,

Sounds strange to me. If you post the Word VBA and Excel VBA code I'd be happy to have a quick look at it for you?

 

[Azerty99]

Hi Jon,

Sounds strange to me. If you post the Word VBA and Excel VBA code I'd be happy to have a quick look at it for you?

Colin,

I've been running for a few days now, using the calls dozens of times, with no issues (YET!). At the risk of being over confident by even thinking of posting here - you know how Dr. Murphy behaves!

Word (Module):

Sub InsertCharts(WidgetPath As String, exWorkBook As String)
    Dim objExcel As New Excel.Application
    Set wb = objExcel.Workbooks.Open(WidgetPath & exWorkBook)
    Call objExcel.Run("ResetMacro") ' First instance does not work...
    Call objExcel.Run("ResetMacro") ' This works fine
    wb.Close SaveChanges:=True
    Set wb = Nothing
End Sub

Excel (Module):

Public Sub ResetMacro()
    'Reset to the top of the list
    Call ChooseRegion(0)
End Sub
 
Public Sub ChooseRegion(RegionNum As Integer)
    'Choose Region According to Region Number
    Sheets("Chart").Select
    Range("SelRegion").Value = RegionNum
    RegionName = Sheets("Info").Range("RegionName")
    Selection.AutoFilter Field:=2, Criteria1:=RegionName
    Debug.Print "Chosen Region: " & RegionName
End Sub

The issue might be that I have the Excel VBA code in an Add-In. I.e. I use this same code across many workbooks. There is no VBA code in the work book itself with the chart in it.

Obviously this is not all the code, as I have code to copy the plotted chart into the corresponding bookmarked place in the Word document, but hopefully I have extracted what is needed.

 

[Colin Legg]

I haven't tested, but if you change your Word module to this does it work 1st time?

Sub InsertCharts(WidgetPath As String, exWorkBook As String)
    Dim objExcel As Excel.Application
    Dim wb as Excel.Workbook
 
    Set objExcel = New Excel.Application
    Set wb = objExcel.Workbooks.Open(WidgetPath & exWorkBook)
 
    Call objExcel.Run("ResetMacro") ' This works fine
 
    wb.Close SaveChanges:=True
    Set wb = Nothing
    objExcel.Quit
    Set objExcel = Nothing
End Sub

 

[Azerty99]

Tried this.

Still no luck!

Sub InsertCharts(WidgetPath As String, exWorkBook As String)
    Dim objExcel As Excel.Application
    Dim wb As Excel.Workbook
 
    Set objExcel = New Excel.Application
    Set wb = objExcel.Workbooks.Open(WidgetPath & exWorkBook)
    objExcel.Visible = True
 
    Call objExcel.Run("ResetMacro") ' Does not work
    Call objExcel.Run("ResetMacro") ' This works fine

But will leave it as your fix above cured the main problem!

 

[Colin Legg]

Hi,

But will leave it as your fix above cured the main problem!

Just to be sure... it's working okay now?

 

[Azerty99]

Hi,

Just to be sure... it's working okay now?

Yes Colin. Your fix on the Call fixed the main communication problem between Word and Excel.
The latter problem with the first call not working is not really a problem, if ya know about it! So problem solved. Documenting it, well that's another story...

Thank you once again.

Jon