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]

Hi Jon

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...

Ah, okay. Well, it is a problem for me! It shouldn't be doing that. Looking at your code again, I wonder if it looks like that's the case because of the way you are applying the autofilter. Let's test it?

If you change your Excel ChooseRegion procedure to this:

Public Sub ChooseRegion(RegionNum As Integer)
    'Choose Region According to Region Number
    Stop
    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

And then, I assume your Word procedure now looks like this:

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")
    Call objExcel.Run("ResetMacro")
    
    wb.Close SaveChanges:=True
    Set wb = Nothing
    objExcel.Quit
    Set objExcel = Nothing
End Sub

If you run your Word procedure, does it stop once or twice?

 

[Azerty99]

If you run your Word procedure, does it stop once or twice?

Colin.

I was running it with F8 i.e. watching what happened at every step, and could see the that the debugger (strange word that) simply stepped over the first call. Not like a Dim call or comment, It hightlighted the step, but didn't branch off to the call. For the second call, it jumped to Excel and started doing its stuff (I have the visible Excel call on just to view Excel in debug mode). I also have the debug.print line in the call so I can see when that runs too.

So I added the Stop as suggested, now the debugger on F8 jumps to the Stop! and seems to run the code..............and does the debug.print line - but it didn't do that before. But it doesn't go line by line, it goes straight to the Stop

Was it running the code all the time, even on step by step mode? But debug.print says no.

In the original program, the first call was the call to fetch the external data. It was very obvious that that didn't work the first time.

 

[Azerty99]

yes now I get two stops