2

I have a macro enabled excel file in which I am trying to create a macro to run functions every 2 minutes.

The following is my code:

Sub macro123()

Application.SendKeys ("%{A}")
Application.SendKeys ("R")
Application.SendKeys ("A")
Call test

End Sub

Sub test()

Application.OnTime Now + TimeValue("00:02:00"), "macro123"

End Sub

The macro123 gets executed the first time I run it. After 2 minutes it tried to run it again, that's when I am getting the following error.

[![enter image description here][1]][1]

The macro settings seem to be greyed out, probably due to domain settings of organization?

[![enter image description here][2]][2]

Is there any other way for me to execute those statements every n minutes?

Tango
  • 386
  • 1
  • 6
  • 29
  • 2
    You're not specifying which `Workbook` your `Macro123` is coming from. Excel is assuming the macro is in the `ActiveWorkbook` when it's really someplace else. Make sure it's fully specified. – PeterT Mar 23 '17 at 20:50
  • Consider getting rid of the `.SendKeys` and using `Workbooks("myWorkbook").RefreshAll`. –  Mar 23 '17 at 21:03
  • @PeterT I have updated the post with a screenshot of the VBA editor. That is the structure of the workbook. Please advice how to achieve this? – Tango Mar 23 '17 at 21:06
  • @Jeeped Considering the configuration, RefreshAll only refreshes the cells but it does not download the latest data. Check: http://stackoverflow.com/questions/42937961/salesforce-excel-add-in-auto-refresh-doesnt-trigger-a-data-connection-downloa – Tango Mar 23 '17 at 21:06

1 Answers1

2

If your macro is in the code module ThisWorkbook, then you should specify it including the code module's name.

Try this:

Application.OnTime Now + TimeValue("00:02:00"), "ThisWorkbook.macro123"
'                                               ^^^^^^^^^^^^^^
A.S.H
  • 29,101
  • 5
  • 23
  • 50