Evaluate command not working in Excel X under OSX 10.1.3
BK
I am trying to get Excel to import data when it is told to do so by
FileMaker.
In a nutshell, I have a FileMaker script which checks certain
conditions and then executes an embedded AppleScript to tell Excel to
execute a VBA command to refresh a data link. here is the AppleScript:
tell application "Microsoft Excel"
activate
evaluate "test.xls!RefreshMacro()"
end tell
and RefreshMacro is:
Sub RefreshMacro()
ActiveWorkbook.RefreshAll
End Sub
When I run the Macro within Excel, the refresh works fine, but if I
try to kick this of from AppleScript, nothing happens and AppleScript
returns "=" in the result window, which appears to indicate that the
Evaluate command does not understand the arguments, because the same
"=" is returned if you were to do some nonsense like Evaluate
"safhkjasldhasdgklh".
I have also tried Evaluate "ActiveWorkbook.RefreshAll" and other
variations but no matter what, the Evaluate command doesn't seem to
recognise any VBA or Macro calls whatsoever.
Am I missing something here or is there something broken ?
Any hints appreciated.
rgds
bk
Dave Glass
39d9c156.02032...@posting.google.com, "BK"
<bk_u...@yahoo.co.uk> wrote:
> Hi,
>
> I am trying to get Excel to import data when it is told to do so by
> FileMaker.
>
>
> tell application "Microsoft Excel"
> activate
> evaluate "test.xls!RefreshMacro()"
> end tell
>
> and RefreshMacro is:
>
> Sub RefreshMacro()
> ActiveWorkbook.RefreshAll
> End Sub
>
>
> I have also tried Evaluate "ActiveWorkbook.RefreshAll" and other
> variations but no matter what, the Evaluate command doesn't seem to
> recognise any VBA or Macro calls whatsoever.
>
> Am I missing something here or is there something broken ?
>
> Any hints appreciated.
>
> rgds
> bk
Greetings,
As I mentioned in my response to your other question, you may need to change
the referencing of your macro. And possibly drop the parentheses.
Oh, one other thing, the AS dictionary for Excel indicates that the Evaluate
command takes a list, have you tried: evaluate {"test.xls!RefreshMacro()"}
--
Dave Glass
dgl...@bigfoot.com
Private responses are OK, but public ones are better.
BK
> As I mentioned in my response to your other question, you may need to > change the referencing of your macro. And possibly drop the parentheses.
With OnTime, yes, but tried that with Evaluate and no luck there.
> Oh, one other thing, the AS dictionary for Excel indicates that the Evaluate
> command takes a list, have you tried: evaluate {"test.xls!RefreshMacro()"}
Yes, tried that, doesn't work either.
It seems the Evaluate command is broken in respect of anything that is
not an Excel formula, ie. Evaluate "=SUM(....)" is OK, but VBA and
Macros don't seem to be accepted as arguments (even though the
dictionary claims otherwise).
thanks anyway
rgds
bk
blank
This functionality is broken in Excel X. This will be fixed whenever
the patch for Excel X is released.
You might want to try using "RunAutoMacros" and putting your
refreshMacro into an automacro such as auto_open.
BK
>
> bk_u...@yahoo.co.uk (BK) wrote ...
>>
> > tell application "Microsoft Excel"
> > activate
> > evaluate "test.xls!RefreshMacro()"
> > end tell
>
> the patch for Excel X is released.
I see. That would probably be the service pack due some time in May.
Meanwhile I have to try something else then. Thanks.
> You might want to try using "RunAutoMacros" and putting your
> refreshMacro into an automacro such as auto_open.
No, I wouldn't want to do that, because I need FileMaker to explicitly
tell Excel when there is new data. I need near real-time data and
would have to run the macro every second otherwise. Also, as I am
importing from a comma-delimited file there is the problem that Excel
will get confused when it is trying to read the CSV file at the same
time FileMaker is still writing it; with 1 second refresh that happens
far too often.
May be unusual for you to hear this in combination with Microsoft
products, but stability is paramount.
And just in case, ... yes, I have tried to import directly from
Filemaker, but that is broken, too: it brings Excel to the front
whenever the refresh is done. That will be fixed in the service pack,
too, but I am looking for a workaround meanwhile. Anyway, it is better
to refresh only when there has been a modification on the database.
thanks anyway
kind regards
bk
BK
> blank_...@yahoo.com (blank) wrote ...
> > This functionality is broken in Excel X. This will be fixed whenever
> > the patch for Excel X is released.
>
> I see. That would probably be the service pack due some time in May.
Today I got an email from Microsoft and they confirmed that this will
be fixed with the service pack in May.
Alternative to use until then would be ...
tell application "Microsoft Excel"
OnTime EarliestTime (time string of (current date)) RunMacro
"Test.xls!ThisWorkbook.MyMacro" LatestTime (time string of (current
date))
end tell
thanks for your help
regards
bk