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 !
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top