vbs to activate a macro with click

arsene2000

New Member
Joined
Jun 28, 2017
Messages
4
i have a macro that automate the emailing of pivot tables as PDF, but require me to open the excel worksheet and click the button to launch the macro (private).
I want to automate this, and schedule a task to do it daily.
1/ open the excel worksheet
2/ refresh all connection (eventually the source data will come from a connection)
3/ activate the macro (or simulate the click of the button)
4/ save the excel worksheet
5/ close all instances of excel

i'm really new at this and can't figure out how to launch the click macro (or simulate the click of the button)

My VBS so far is :

Code:
Dim objExcel, objWB 




Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = TRUE


Set objWB = objExcel.Workbooks.Open("REPO.xlsm")
objWB.RefreshAll
objExcel.Wait (Now + TimeSerial(0, 0,10))
Call RDB_Outlook()
objWB.Close True 'False to not save
objExcel.DisplayAlerts = True 


objWB.Close
objExcel.Quit
Set objWB = Nothing
Set objExcel = Nothing
Test file is located at TinyUpload.com - best file hosting solution, with no limits, totaly free

Thanks for any help !
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
arsene2000,

(Forgive me if I am oversimplifying this)
So you need to run this bit of code every day at a certain time. Try this link Windows Task Scheduler to schedule opening your workbook everyday. In ThisWorkbook object in your workbook, add your code to the open workbook event ("Private Sub Workbook_Open()"). So that will open your specified excel workbook at a certain time everyday, and then run your code whenever the workbook is opened.

Hope this helps,
Nick
 

arsene2000

New Member
Joined
Jun 28, 2017
Messages
4
the problem is that i can't find a way to run the macro that i'm launching when physically clicking on the buttom form
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
What do you mean "button form"?
Is there a button on your worksheet you want to click, or do you mean clicking the green arrow to run a macro?
 

arsene2000

New Member
Joined
Jun 28, 2017
Messages
4
There is a button on the excel worksheet (from a excel plugin i found).
When i physically click that button, it launches the macro (sub private) that create my PDf and sends them by email.
I can't find a way to call that macro from a VBSscript so that i can automate the process (without having to manually click on that button).
I attached the excel worksheet on my original post if that helps.

Thank you for your help
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
If you use the Task Scheduler from the link I provided in my first reply, you can set it up to open your excel workbook once per day, twice per day, 100 times a day, etc.. however many times, and at what ever time you set it to.

In your excel workbook - in your VBA code, look on the left margin and double click"ThisWorkbook".
In here, type this code
Code:
Private Sub Workbook_Open()
    Call [Enter in the name of your macro here, just after the "Private Sub" part]
End Sub
This will call your macro to run every time the workbook is opened
 

Forum statistics

Threads
1,077,686
Messages
5,335,656
Members
399,032
Latest member
thefinu

Some videos you may like

This Week's Hot Topics

Top