auto run vba module at 09:10:05

[Dipak543]

currently, I need to press a button at 09:10:05 every day to run the attached (photo shared) module in VBA after the code runs automatically in five minutes, Is there any way possible I can automate that at 9:10:05 the module runs automatically?

 

[Joe4]

Here is one way:

How To Automatically Run Excel VBA Macros Daily

Learn how to utilize a few programs you already have to create automation rules that will automatically open up your Excel files, run VBA code, save the changes, and then close the file. These automations can even be setup to run while you sleep!

www.thespreadsheetguru.com

 

[johnnyL]

Application.OnTime TimeValue("09:10:05"), "Algomojo"

 

[Dipak543]

Application.OnTime TimeValue("09:10:05"), "Algomojo"

where should I place this code? earlier I tried to place after end if but it's not working

 

[Dipak543]

Here is one way:

How To Automatically Run Excel VBA Macros Daily

Learn how to utilize a few programs you already have to create automation rules that will automatically open up your Excel files, run VBA code, save the changes, and then close the file. These automations can even be setup to run while you sleep!

www.thespreadsheetguru.com

sir, it's a different thing other codes ( in worksheets ) working automatically but for modules, I need to press the button to start

 

[Joe4]

sir, it's a different thing other codes ( in worksheets ) working automatically but for modules, I need to press the button to start

The potential problem with using Application.OnTime is that the workbook needs to be open at that particular time on a computer.
If you know that will always be true, then go ahead and use that method.

However, in most of my experiences, that is not always true, as we usually want to run jobs overnight or on days in which the computer may not be on.
In those cases, it works better to run the job from a Scheduler on a Server which is always turned on (then it is not dependent on a computer being on and the file being open). So my option deals with that situation.

Use whatever is better for you.

 

[GWteB]

where should I place this code? earlier I tried to place after end if but it's not working

@Dipak543, if using WTS isn't an option for you, you might take a look at this thread for an example on how to implement an OnTime scheduled macro; the OP's issue is different, implementation details are similar:

OnTime Applying all open Workbook and reopening the Excel sheet which was closed

I have This in My ThisWorkbook Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) With ThisWorkbook.Worksheets("My_Subs") Application.Run "CommandButton2_Click" End With End Sub Private Sub Workbook_Open() Application.OnTime Now + TimeValue("00:00:30"), "Save1" End Sub And...

www.mrexcel.com

 

[Dipak543]

The potential problem with using Application.OnTime is that the workbook needs to be open at that particular time on a computer.
If you know that will always be true, then go ahead and use that method.

However, in most of my experiences, that is not always true, as we usually want to run jobs overnight or on days in which the computer may not be on.
In those cases, it works better to run the job from a Scheduler on a Server which is always turned on (then it is not dependent on a computer being on and the file being open). So my option deals with that situation.

Use whatever is better for you.

yes sir my excel automatically open at 9 am but the module is not running still i need to press the start button to run the code.
where should I use application.ontime code in module?

 

[Dipak543]

okay i am going through it

here is the code


Sub Algomojo()


If Minute(Now()) Mod 5 = 0 And (Sheets("Place Order").Range("F5") <> "") Then

Dim apikey As String
Dim apisecret As String
Dim Broker As String
Dim Version As String
Dim stgy As String
Dim ClientID As String
Dim Symbol As String
Dim Exchange As String
Dim Ttranstype As String
Dim qty As String
Dim Price As String
Dim TrigPrice As String
Dim Pcode As String
Dim response As String
Dim Order As String
Dim Product As String
Dim Variety As String
Dim Token As String
Dim NumRows As Integer
Dim X As Integer


apikey = "25699101818aa2fe74509d3688"
apisecret = "29254291f3121062c769bffa"
Version = "1.0"
stgy = "Excel"
Broker = Sheets("Place Order").Cells(5, "B").Value
ClientID = Sheets("Place Order").Cells(5, "C").Value
Exchange = Sheets("Place Order").Cells(5, "D").Value
Symbol = Sheets("Place Order").Cells(5, "E").Value
Ttranstype = Sheets("Place Order").Cells(5, "F").Value
Order = Sheets("Place Order").Cells(5, "G").Value
qty = Sheets("Place Order").Cells(5, "H").Value
Price = Sheets("Place Order").Cells(5, "I").Value
TrigPrice = Sheets("Place Order").Cells(5, "J").Value
Product = Sheets("Place Order").Cells(5, "K").Value
Variety = Sheets("Place Order").Cells(5, "L").Value
Token = Sheets("Place Order").Cells(5, "M").Value

response = PlaceOrder(apikey, apisecret, Broker, Version, stgy, ClientID, Symbol, Exchange, Ttranstype, Order, qty, Price, TrigPrice, Product, Variety, Token)
Sheets("Place Order").Cells(9, "C") = response
'MsgBox (tostring(NumRows))


End If

Application.OnTime Now + TimeSerial("00:05:00"), "Algomojo"



End Sub



Public Function PlaceOrder(user_apikey As String, api_secret As String, Broker As String, Version As String, stgy_name As String, ClntID As String, Tsym As String, exch As String, Ttranstype As String, Order As String, qty As String, Price As String, TrigPrice As String, Product As String, Variety As String, Token As String) As String
Dim InTD As String
Dim sOutput As String
Dim Ret As String
Dim Duration As String
Dim squareoff As String
Dim stoploss As String
Dim TrailingStoploss As String
Dim disclosedquantity As String



Duration = "DAY"
squareoff = "0"
stoploss = "0"
TrailingStoploss = "0"
disclosedquantity = "0"


If (TrigPrice = "") Then
TrigPrice = "0"
End If
AMO = "NO"
InTD = "{""" _
& "stgy_name" & """:""" & stgy_name & """,""" _
& "variety" & """:""" & Variety & """,""" & "tradingsymbol" & """:""" & Tsym & """,""" _
& "symboltoken" & """:""" & Token & """,""" & "transactiontype" & """:""" & Ttranstype & """,""" _
& "exchange" & """:""" & exch & """,""" & "ordertype" & """:""" & Order & """,""" _
& "producttype" & """:""" & Product & """,""" & "duration" & """:""" & Duration & """,""" _
& "price" & """:""" & Price & """,""" & "squareoff" & """:""" & squareoff & """,""" _
& "stoploss" & """:""" & stoploss & """,""" & "quantity" & """:""" & qty & """,""" _
& "triggerprice" & """:""" & TrigPrice & """,""" & "trailingStopLoss" & """:""" & TrailingStoploss & """,""" _
& "disclosedquantity" & """:""" & disclosedquantity & """}"

'MsgBox (InTD)
PlaceOrder = AMConnect(user_apikey, api_secret, Broker, Version, "PlaceOrder", InTD)
End Function


Private Function AMConnect(api_key As String, api_secret As String, Broker As String, Version As String, api_name As String, InTD As String) As String
Dim objHTTP As Object
Dim result As String
Dim postdate As String
Dim BrkPrefix As String
Dim BaseURL As String
BaseURL = "https://" & LCase(Broker) & "api.algomojo.com/" & Version & "/"
postdata = "{""" & "api_key" & """:""" & api_key & """,""" _
& "api_secret" & """:""" & api_secret & """,""" _
& "data" & """:" & InTD & "}"
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Url = BaseURL & api_name
objHTTP.Open "POST", Url, False
objHTTP.setRequestHeader "Content-type", "application/json"
objHTTP.send (postdata)
result = objHTTP.responseText
Set objHTTP = Nothing
AMConnect = result

End Function

 

[Joe4]

If you want it to happen automatically, then you would need to put it in the "Workbook_Open" event procedure, like shown in the link GWteB shows in the link provided. "Event procedure" VBA code is the only code that will run automatically. All other VBA code has to be run manually.