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"
Here is the scheduler. The macro is within a file ".vbs" extension (without the quotes).
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.
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.