Sunday, September 27, 2015

How to Schedule, Call and Run a Macro from a Workbook

How to Schedule, Call and Run a Macro from a Workbook
When we want to run a Macro from an event or from another Macro in the same Workbook , We can call the Macro like this in code :
Call MyMacroName

But if we want to run a macro that is in another workbook or Add-In(File or add-in must be open). We can use Application.Run as follows :
Application.Run "MyWorkBook.xls!MyMacroName"

Case I : 
Suppose if the Workbook name is not known in advance and its dynamically declared in a variable then we call the Macro as follows :
Application.Run " '" & strFileName & "'!MyMacroName"

Case II :
Suppose if the Macro is specific to particular Worksheet Event , then we need to specify the Sheet name along with Macro name as follows :

Application.Run " '" & strFileName & "'!Sheet1.MyMacroName"

Please Note :
To get the Exact Macro name , we need to go to Macros in Excel Workbook where we can see all the Macros with Correct names to be use as reference for Calling/Running.

Scheduling the Macro :
There are many ways to schedule your macros to run using the Application.OnTime method. The most common ways are via specific times (03:00, 11:45, 19:30) or a time relative to the existing time.

Scheduling at a  Specific Time :
If a task must be run every day at the same time, it makes sense to apply the specific time technique. Let’s say you have to run a macro every day at 7am.

Application.OnTime TimeSerial (h, m, s) , "Module1.Morning7AM_Task_Macro"
Application.OnTime  "07:00:00", "Morning7AM_Task_Macro"

Note:
The time can passed as 24 hour format as well like "15:15:00" for 03:15 PM

Scheduling at Relative Times :
You can also schedule macros to run at relative times. The most common way is to schedule it relative to the current time. You would do this by using the Now function to grab the current time then adding some amount of time to it.
The DateAdd function is a good option if you want to add two times together, and it plays well with the Now function. The following code fires off the Macro after nseconds from now, where nseconds is determined by the user.

nseconds = CInt(InputBox("How many Seconds after from Now, the Macro should run?"))
Application.OnTime DateAdd("s", nseconds, Now), "Module1.Macro_to_Schedule"

Note :
It would be helpful if we specify the Module name in the Macro, so that no issue arises even if we have the Macros with the same name in different modules.

Scheduling Macros in Other Workbooks :
The  Application.OnTime method can be used to call and execute the Macros from  other open workbooks,by properly specifying the name of the other workbook. 

A call to a workbook named OtherWorkbook.xlsm with a Macro named MyMacro in it will look like this:
Application.OnTime "17:15:03", "OtherWorkbook.xlsm!Module1.MyMacro"

Scheduling Macros in Closed Workbooks :
Since the scheduling happens at the Application level, as long as the Application is open, the scheduled Macro should execute. A user can close all of the workbooks but leave the  Excel Application(from where we executing a Macro) running and it will be fine. In other words, all the workbooks can be closed but Excel must remain open for the OnTime method to operate.
To call a Macro in a closed workbook, we need to supply the full filepath to the workbook:

Application.OnTime Now + TimeSerial(3, 15, 5), "C:\WorkingFiles\Otherorkbook.xlsm!Module1.MyMacro"

Thanks, TAMATAM

Saturday, September 5, 2015

How to Update a Target Table by Mapping with another Table in SQL Server

SQL Query to Update a Target Table by Mapping with another Table in SQL Server
Suppose , we have a Target Table called 'Tbl_Customers', in which the columns [Cust_Segment] and [Bookings_Flag] are need to update by Mapping it with a another Table called 'Map_Table' . we can do it by using the Update query as follows..

With Inner Join :
UPDATE [Tbl_Customers]
SET [Cust_Segment] = M.[CustSegment],
[Bookings_Flag]= CASE When M.[Bookings_Type]='Product' Then 'Prod' ELSE 'Svc' END
From [Tbl_Customers] C Inner Join [Map_Table] M
On C.Cust_Id=M.CustId

With Where Clause :
UPDATE [Tbl_Customers]
SET [Cust_Segment] = M.[CustSegment],
[Bookings_Flag]= CASE When M.[Bookings_Type]='Product' Then 'Prod' ELSE 'Svc' END
From [Tbl_Customers] C, [Map_Table] M
Where C.Cust_Id=M.CustId


--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog