• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to schedule a Macro to run spontaneously?

shahin

Active Member
I've written a macro using IE to parse some value from a webpage and it is running great. What I wish to do with it is schedule the macro to run spontaneously. However, complying the guideline described in this webpage "http://analystcave.com/excel-schedule-excel-macros-to-run-periodically/" I took an initiative to give a go but unfortunately I'm not being able to. Any kind touch on this will be highly appreciated.

The macro is within a module named "MyMacro" and within a workbook named "Book1.xlsm"
Code:
Sub Get_Value()
    Dim HTML As HTMLDocument, tags As Object
    Dim tag As Object, ival As Object

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "https://www.gdax.com/trade/LTC-EUR"
        While .Busy = True Or .readyState < 4: DoEvents: Wend
        Set HTML = .document

        Do: Set tags = HTML.querySelector("[class^='OrderBookPanel_text_']"): DoEvents: Loop While tags Is Nothing
        Do: Set tag = HTML.querySelector("input[name='amount']"): DoEvents: Loop While tag Is Nothing
        tag.Focus
        tag.innerText = 100

        Set ival = HTML.querySelector("[class^='OrderForm_total_']")
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ival.innerText
        .Quit
    End With
End Sub

Here is the scheduler. The macro is within a file ".vbs" extension (without the quotes).

Code:
RunMacro
Sub RunMacro()
  dim xl,path,xlBook
  path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open(path & "\Book1.xlsm", 0, True)    
  xl.Application.Visible = False
  xl.DisplayAlerts = False  
  xl.Application.run "Book1.xlsm!Module.MyMacro"
  xl.ActiveWindow.close
  Set xlBook = Nothing
  xl.Quit
  Set xl = Nothing
  Application.Wait Now+Timevalue("00:00:20")
  RunMacro
End Sub

I tried to run it manually to see if it works but it throws an error: "The macro may not be available in this workbook."
Btw, I kept the workbook in desktop. Thanks in advance.
 
You could run a windows task, that fires up a file.bat (2 lines of code in notepad) that starts the Excel with macro to run.
Code:
"C:\Program Files\Microsoft Office\root\Office16\excel.exe"
"\\fullpath\filename.xlsm"
Code:
"C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"
"\\fullpath\filename.xlsm"

Would love to see if and how it is possible with a macro.
 

Call procedure from Workbook_Open event …

Or better no code within the workbook but just use a VBSCript file !​
 
Is something like this not enough?
Code:
If Weekday(Now, vbMonday) < 6 Then Application.OnTime TimeValue("09:00:00"), "Get_Value"
In this example the code wil run the Get_Value macro from monday to friday at 09.00 if your workbook is open.
 
If you are using the macro as you've posted then it will not run:
Code:
xl.Application.run "Book1.xlsm!Module.MyMacro"

Per your description:
The macro is within a module named "MyMacro" and within a workbook named "Book1.xlsm"
It should be:
Code:
xl.Application.run "Book1.xlsm!MyMacro.Get_Value"

Also test the output of following line as to it is pointing to desktop correctly or not:
Code:
path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
 
@ shrivallabha, thanks a lot. You are the guy with very sharp eyes. You always notice where I'm going wrong. You gave the right nudge. Finally I could make my macro run using the "maco.vbs" file. However, the thing is the macro is being executed but no data is saved in "Book1.xlsm" file when it is done. What should I do now to get the output written? Btw, before executing the macro I took out few lines from "macro.vbs" file and added the portion you suggested.

Here it is:

Code:
RunMacro
Sub RunMacro()
  Dim xl, path, xlBook
  path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open(path & "\Book1.xlsm", 0, True)
  xl.Application.Visible = False
  xl.DisplayAlerts = False
  xl.Application.Run "Book1.xlsm!MyMacro.Get_Value"
  xl.ActiveWindow.Close
End Sub
 
Finally, putting this "macro.vbs" file in startup folder I can see that "Book1.xlsm" file is being executed in every 50 seconds automatically. However, the "Book1.xlsm" file still doesn't contain any data. How to save data automatically?

This is the code in "macro.vbs" file within startup folder which forces the macro run spontaneously in a regular interval:
Code:
RunMacro
Sub RunMacro()
  Dim xl, path, xlBook
  path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open(path & "\Book1.xlsm", 0, True)
  xl.Application.Visible = False
  xl.DisplayAlerts = False
  xl.Application.Run "Book1.xlsm!MyMacro.Get_Value"
  xl.ActiveWindow.Close
  WScript.Sleep(50000)
  RunMacro
End Sub
 
I've got everything fixed. To get the data written in the "Book1.xlsm" file, all I needed to do is put this line "xl.Activeworkbook.Save" just before "xl.ActiveWindow.close" in "macro.vbs" file. Thanks everyone for your valuable inputs.
 

Or without Save as Close is enough with right argument
like you can see in VBA inner help ...​
 
Back
Top