Friday, May 29, 2015

SQL Server Query to find Highest or Maximum Salary in each Department

SQL Query to Retrieve Highest or Maximum Salary drawing Employee Details from each Department

Example :
Suppose we have a Employee Table as follows:

EMP Table :
EMP_ID
F_Name
L_Name
Dept_ID
Cur_Address
Perm_Address
Salary
1001
Ravi
Kumar
1
Hyderabad
Hyderabad
20000
1002
David
Smith
2
Bangalore
Pune
35000
1003
Victory
Venkatesh
1
Hyderabad
Hyderabad
50000
1004
Tamatam
Reddy
3
Bangalore
Hyderabad
25000
1005
William
Smith
2
Hyderabad
Bangalore
40000
1006
King
Fisher
6
Bangalore
Bangalore
30000


Now if we want to Retrieve Highest or Maximum Salary drawing Employee Details of each Department , from the above table , we can use the following SQL Sub Query :

SELECT E_Id,(F_Name+'_'+L_Name) as E_Name,Dept_Id,Salary From EMP Where Salary IN (SELECT MAX(Salary)AS Max_Sal From EMP GROUP BY Dept_Id)

Output :
E_Id
E_Name
Dept_Id
Salary
1003
Victory_Venkatesh
1
50000
1004
Tamatam_Reddy
3
25000
1005
William_Smith
2
40000
1006
King_Fisher
6
30000

#--------------------------------------------------------------Thanks--------------------------------------------------------------#

SQL SERVER CARTESIAN JOIN or CROSS JOIN Syntax and Example

SQL Server CARTESIAN Product or CROSS JOIN 
The Cross Join returns the Cartesian product of the sets of records from the two or more joined tables.
when a join condition is omitted when getting result from two tables then that kind of query gives us Cartesian product, in which all combination of rows displayed. All rows in the first table is joined to all rows of second table.


Syntax :
Select* from Table_I , Table_2....
OR
Select* from  T1.Column1,T1.Column2, T2.Column3,T2.Column4 
from Table_I  T1, Table_2 T2

Example :
Suppose we have a Two Tables as follows:
EMP Table :
DEPT Table :

Cartesian or Cross Join Query :
SELECT E_Id,(F_Name+'_'+L_Name) as E_Name , D.Dept_Id,D.Dept_Name 
from EMP E, DEPT D

Output :
As the Table_1(EMP) contains 6 records and Table_2(DEPT) has 3 records , the Cartesian/Cross Join gives an output of 18 (6*3) records as shown below :



--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Thursday, May 28, 2015

How to ceate a Leave Tracker Form in MS Access

Sample Leave Tracker Form to Store PTO(Paid Time Off)  details of Employees in MS Access
Let us suppose we have a sample Employee Table as follows :


Now we need to store the PTO Details of the Employees in the below format :


For this , I have designed a Form as shown below :


-----------------------------------------------------------------------------------------------------------------
Code for the Above Form as follows :
-----------------------------------------------------------------------------------------------------------------
Option Compare Database

'Refreshing the Form on Load
Private Sub Form_Load()
Form.Refresh
End Sub
----------------------------------------------------------------------------
'Refreshing the EMP_ID combo values 
Private Sub cmbo_EMP_ID_GotFocus()
Me.cmbo_EMP_ID.Requery
End Sub
----------------------------------------------------------------------------
'Retriving EMP_ID ,EMP_Process values from the Employee Table[MyTeam] based on the selection of E_Name , and loading in to text boxes  EMP_ID ,EMP_Process of Form.
Private Sub Cmbo_EMP_NAME_AfterUpdate()

Dim SQL_Str As String
Dim DB As Object
Dim RS_ID As DAO.Recordset
Dim RS_Process As DAO.Recordset

'Retriving EMP_ID ,EMP_Process values from the Employee Table[MyTeam] based on the selection of E_Name.

E_NAME = Me.Cmbo_EMP_Name.Value

Str_ID = "SELECT EMP_ID FROM MyTeam WHERE MyTeam.EMP_NAME=" & """" & E_NAME & """" & ";"

Str_Process = "SELECT EMP_Process FROM MyTeam WHERE MyTeam.EMP_NAME=" & """" & E_NAME & """" & ";"

Set DB = CurrentDb
Set RS_ID = DB.OpenRecordset(Str_ID)
Set RS_Process = DB.OpenRecordset(Str_Process)

'Getting EMP_ID ,EMP_Process values from the Employee Table[MyTeam] to respective Texboxes of Form based on the selection of E_Name.

Me.txt_EMP_ID.Value = RS_ID.Fields("EMP_ID").Value
Me.txt_Process = RS_Process.Fields("EMP_Process").Value

End Sub
----------------------------------------------------------------------------
'At any moment while inputting into the form , if you want to Reset the form on clicking the Reset button , you can assign the below code to 'Reset' button.
Private Sub cmd_Reset_Click()

Me.Cmbo_EMP_Name = ""
Me.txt_EMP_ID = ""
Me.cmbo_LEAVE_TYPE = ""
Me.txt_Process = ""
Me.txt_COMMENTS = ""
Me.txt_PTO_START_DATE = ""
Me.txt_PTO_END_DATE = ""

End Sub
----------------------------------------------------------------------------
'Validating the Form Input on clicking the Submit button and then submitting/updating the data to the Output Table(PTO_Details) , assign the below Macrp code to 'Submit' button :
Private Sub cmd_Submit_Click()
DoCmd.SetWarnings False

Checking that mandatory input values should not be left blank
If Me.Cmbo_EMP_Name.Value = "" Then
MsgBox "Employee Name Field Cannot be Blank", vbCritical, "Please Enter Employee Name"
Exit Sub

ElseIf IsNull(Me.txt_EMP_ID.Value) Or Me.txt_EMP_ID.Value = "" Then
MsgBox "Employee ID Field Cannot be Blank", vbCritical, "Please Enter Employee ID"
Exit Sub

ElseIf Me.txt_Process = "" Then
MsgBox "Employee Process Field Cannot be Blank", vbCritical, "Please Enter Employee Process"
Exit Sub

ElseIf Me.cmbo_LEAVE_TYPE = "" Then
MsgBox "Leave Type Field Cannot be Blank", vbCritical, "Please Enter Leave Type"
Exit Sub

ElseIf Me.txt_PTO_START_DATE = "" Then
MsgBox "PTO Start Date Field Cannot be Blank", vbCritical, "Please Enter PTO Start Date"
Exit Sub

ElseIf Me.txt_PTO_END_DATE = "" Then
MsgBox "PTO End Date Field Cannot be Blank", vbCritical, "Please Enter PTO End Date"
Exit Sub

'Checking that PTO End Date should be greater that PTO Start Date
ElseIf Me.txt_PTO_END_DATE.Value < Me.txt_PTO_START_DATE.Value Then
MsgBox "The End Date should be Greater Than or Equal to Start Date", vbCritical, "Please Select Correct Date"
Me.txt_PTO_END_DATE = ""
Exit Sub

End If

'After data validation , submitting the Form Input to the Output Table ( PTO_Details):
Update_Query1 = "INSERT INTO PTO_Details ([Employee_ID],[Employee_Name],[Process],[Leave_Type],[PTO_Start_Date],[PTO_End_Date],[Comments])" _
& "Select[Forms]![PTO_Tracker]![txt_EMP_ID] AS [Employee_ID],[Forms]![PTO_Tracker]![cmbo_EMP_NAME] AS [Employee_Name]," _
& "[Forms]![PTO_Tracker]![txt_PROCESS] AS [Process],[Forms]![PTO_Tracker]![cmbo_LEAVE_TYPE] AS [Leave_Type]," _
& "[Forms]![PTO_Tracker]![txt_PTO_START_DATE] AS [PTO_Start_Date],[Forms]![PTO_Tracker]![txt_PTO_END_DATE] AS [PTO_End_Date]," _
& "[Forms]![PTO_Tracker]![txt_COMMENTS] AS [Comments]"

DoCmd.RunSQL Update_Query1

'Clearing the Form Input fields After submitting the Form Input to the Output Table ( PTO_Details):

Me.Cmbo_EMP_Name = ""
Me.txt_EMP_ID = ""
Me.cmbo_LEAVE_TYPE = ""
Me.txt_Process = ""
Me.txt_COMMENTS = ""
Me.txt_PTO_START_DATE = ""
Me.txt_PTO_END_DATE = ""
DoCmd.SetWarnings True

End Sub
----------------------------------------------------------------------------

'Exporting the Form Data when you click on the button 'Export PTO Data' , assign the below Macro code to 'Export PTO Data' button.
Private Sub Exp_PTO_Data_Click()
Dim OP_FileName As String

CurDB_Name = Left(CurrentProject.Name, Len(CurrentProject.Name) - 6)
OP_FileName = CurrentProject.Path & "\" & CurDB_Name & "-" & Format(Date, "DDMMMYYYY") & ".xlsx"

Dim Tbl As Object
Dim DB As Object

Set DB = CurrentDb


Tbl_Name = "PTO_Details"

'Exporting 'PTO_Details table to Excel in same location of Form/Database
DoCmd.TransferSpreadsheet acExport, , Tbl_Name, OP_FileName, True

MsgBox "PTO Data Exported SuccessFully", vokonly, "Export Success"

End Sub
-----------------------------------------------------------------------------------------------------------------
Key Notes :
-----------------------------------------------------------------------------------------------------------------
In the above from :
[Me.Cmbo_EMP_Name]  is a Combo Box
Me.txt_EMP_ID] is a Text Box
[Me.cmbo_LEAVE_TYPE] is a Combo Box
[Me.txt_Process] is a Text Box
[Me.txt_COMMENTS] is a Text Box
[Me.txt_PTO_START_DATE] is a Text Box with Date Picker
[Me.txt_PTO_END_DATE] is a Text Box with Date Picker

My Dear users please try understand that , we have to assign the right Macro code to the right Event/Property of the Form Control.

To understand the above Form structure completely we need to look into the Properties of the each Form Control.

But the above piece of Information will give you an Idea of Form Design and Validating and  Submitting Form Input as well as Exporting Form Data.

-----------------------------------------------------------------------------------------------------------------
Thanks,TAMATAM

Friday, May 22, 2015

VBA StrReverse Function Syntax and Example

StrReverse Function
The VBA StrReverse Function reverses the specified String.

Syntax :
StrReverse(String)

Here :
String is the string that you want to reverse , it may be a Number or Text.

Example :

Sub StrSearch()
MyStr = "Str_12345"

Rev_Str = StrReverse(MyStr)

MsgBox Rev_Str
End Sub

Output :

Thanks, TAMATAM

Thursday, May 21, 2015

How to Join an Array of Strings into a single String in VBA

VBA Join Function
The VBA 'Join' Function joins the Substrings contained in an array, and returns a String by Concatenating the  Substrings separated by a specified delimiter.

Syntax :
StringName = join(Array Input, Delimiter)

Here , Array Input is the array that you want to place into a string.

Delimiter is an optional parameter that indicates what you want to place between elements are added to the string. By default this parameter is set to "".

Sub Split_Store_In_Array()

Dim MyStr(6) As String

'Array Input as follows
MyStr(0) = "This"
MyStr(1) = "Is"
MyStr(2) = "What"
MyStr(3) = "I"
MyStr(4) = "Want"
MyStr(5) = "To"
MyStr(6) = "Store"


'Concatenating/Joining all Arrary Values so as to form a single String
Conc_Str = Join(MyStr, "_")

MsgBox Conc_Str

End Sub

How to use VBA Split Function

VBA Split Function Definition and Syntax with Example
Split a one of the String function that can split a text string into an array, by making use of a delimiter character. It returns a zero-based, one-dimensional array holding the parts of the original text string.

Syntax :
Split (Text_String, Delimiter, Limit, Compare)

Here, ‘Text_String’ refers to the string that you need to break.

'Delimiter' refers to the character which divides the string into parts. This is an optional argument, space character “ ” is treated as the default delimiter.

‘Limit’ is an optional parameter. It specifies the maximum number of parts into which the input string should be divided. The default value is -1, which means that the string should be divided at each occurrence of ‘Delimiter’.

‘Compare’ is also an optional argument that specifies the comparison method to be used while evaluating the strings. 'Compare’ argument can have anyone of the below values:

Key characteristics of a Split Function:
Split Function returns a String Array and not a String.If you omit the ‘compare’ argument then, Split Statement uses the Binary comparison method as default.
If the ‘Text_String’ that you pass to Split is a zero length string then, it returns a single-element array containing a zero-length string.
If the ‘Delimiter’ is not found anywhere in the ‘Text_string’, then Split returns the single-element array containing 'Text_String', as it is.

Example :
Sub Split_Store_In_Array()
Dim WS As Worksheet
Dim MyStr() As String
Dim Str As String

Set WS = ActiveSheet
Str = "This_Is_What_I_Want_To_Store"
'Storing the String in an Array by Spliting it by delimiter'_'
MyStr() = Split(Str, "_")
'After Spliting the String it will store in Array as follows
MyStr(0) = "This"
MyStr(1) = "Is"
MyStr(2) = "What"
MyStr(3) = "I"
MyStr(4) = "Want"
MyStr(0) = "To"
MyStr(0) = "Store"

'Looping through Array to Display the Stored values.
For x = LBound(MyStr) To UBound(MyStr)
MsgBox MyStr(x)
Next x

End Sub
------------------------------------------------------------------------------------------------------------------
Suppose if you specifies the 'Limit' , the maximum number of parts into which the input string should be divided as follows :
MyStr() = Split(Str, "_" , 2)
Now the String is Split into 2 Parts , string up to first occurrence of Delimiter '_' is One part and remaining part of the string is Second part.
Str = "This_Is_What_I_Want_To_Store"
MyStr(0) = "This"
MyStr(1) = "Is_What_I_Want_To_Store"


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Sunday, May 17, 2015

How To Calculate the Time of Your Macro Takes To Run

VBA Code to Calculate How Much Time Your Macro Takes To Run
Method-I : To Calculate Macro Time in Seconds using Timer () Method
Sub Macro_Time_Seconds()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

StartTime = Timer

For x = 1 To 100000
Cells(x, 1) = "Hi"
Next x

EndTime = Timer

TimeTaken = Round(EndTime - StartTime, 2)

MsgBox "Time To Run the Macro: " & TimeTaken & " Seconds"
Debug.Print "Time To Run the Macro: " & TimeTaken & " Seconds"

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

--------------------------------------------------------------
Method-II : To Calculate Macro Time in Seconds using Now() Method
Sub Macro_Time_from_Now()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

StartTime = Now

For x = 1 To 100000
Cells(x, 1) = "Hi"
Next x

EndTime = Now

TimeTaken = Format((EndTime - StartTime) * 24 * 60 * 60, "#,##0")

MsgBox "Time To Run the Macro: " & TimeTaken & " Seconds"
Debug.Print "Time To Run the Macro: " & TimeTaken & " Seconds"

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

--------------------------------------------------------------
Method-III : To Calculate Macro Time in Minutes using Timer() Method
Sub Macro_Time_Minutes()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

StartTime = Timer

For x = 1 To 1000000
Cells(x, 1) = "Hi"
Next x

EndTime = Timer

TimeTaken = Format((EndTime - StartTime) / 86400, "hh:mm:ss")

MsgBox "Time To Run the Macro: " & TimeTaken & " Minutes"
Debug.Print "Time To Run the Macro: " & TimeTaken & " Minutes"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Thanks , TAMATAM

How to Optimize the VBA Code to Improve the Speed of the Macros

Techniques to Optimize the VBA Code to Improve the Speed of the Macros
Analyze the Code logic:
Before optimizing the syntax, pay more attention in optimizing the logic. Without a good logic, a good written VBA macro program has no value. So streamline your program logic and get the best performance of macros.

Avoid 'Screen Flickering' or 'Screen Repainting':
Use
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = False 'To Turn on at the end of the code.


The ScreenUpdating property controls most display changes on the monitor while a procedure is running. When screen updating is turned off, toolbars remain visible and Word still allows the procedure to display or retrieve information using status bar prompts, input boxes, dialog boxes, and message boxes.

You can increase the speed of some procedures by keeping screen updating turned off.
You must set the ScreenUpdating property to True when the procedure finishes or when it stops after an error.


Turn off automatic calculations:
Whenever content(s) of a cell or range of cells are changed, the formulas dependent on them and Volatile functions are recalculated. You may turn off the automatic calculation using 

Application.Calculation = xlCalculationManual 'To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation

Now, whenever due to the program logic(that due to macros dependent on existing formulas) you need to calculate the formulas, you may use the following code accordingly.

ActiveSheet.Calculate ' To calculate the formulas of Active Worksheet
Application.Calculate ' To calculate the formulas of Active workbook or all workbooks in current application.

Stop Events: 
Use Application.EnableEvents to tell VBA processor whether to fire events or not. We rarely fire an event for each cell we're changing via code. Hence, turning off events will speed up our VBA code performance.

Hide Page Breaks:
When we run a Microsoft VBA macro in a later version of Microsoft Excel, the macro may take longer to complete than it does in earlier versions of Excel. 
For example, a macro that required several seconds to complete in an earlier version of Excel may require several minutes to complete in a later version of Excel. This problem may occur if the following conditions are true:
The VBA macro modifies the properties of many rows or columns.
An operation has been performed that forced Excel to calculate page breaks. Excel calculates page breaks when we perform any of the following operations:
  • We display a print preview of your worksheet.
  • In Microsoft Office Excel 2003 and in earlier versions of Excel, we click Page Setup on the File menu.
  • We modify any of the properties of the PageSetup object in a VBA macro.
In Excel 2003 and in earlier versions of Excel, we selected the Page breaks check box on the View tab of the Options dialog box.
Solution: 

Disable Page breaks using ActiveSheet.DisplayPageBreaks = False

Use 'WITH' statement when working with objects: 

If we have to access an object's properties and methods in several lines, we must avoid using object's name or fully qualified object path again and again. It is annoying for VBA processor as it needs to fully qualify the object each time.
SLOW MACRO


FAST MACRO

Sheets(1).Range("A1:K1").Font.Italic = True
Sheets(1).Range("A1:
K1").Font.Interior.Color = vbBlue
Sheets(1).Range("A1:
K1").MergeCells = True
With Sheets(1).Range("A1:K1")
    .Font.Italic = True
    .Font.Interior.Color = vbBlue
    .MergeCells = True

End With

The point here to understand is minimum qualifying of an object by VBA processor. i.e. using minimum dots/periods(.) in the code. This concept tells us to use [A1] rather than Range("A1") and Range("StockRange")(3,4) rather than Range("StockRange").Cells(3,4)

Use vbNullString instead of ""(2 double quotes) :
vbNullString is slightly faster than "", since vbNullString is not actually a string, but a constant set to 0 bytes, whereas "" is a string consuming at least 4-6 bytes for just existence.

For example: Instead of strVariable = "", use strVariable = vbNullString.


Release memory from object variables: 

Whenever we create an object in VBA, we actually create two things -- an object, and a pointer (called an object reference). We might say, "VB does not use pointers", but it is not true. "VB does not let you manipulate pointers" is more precise. Behind the scenes, VB still makes extensive use of pointers. 

To destroy an object in VB, you set it to Nothing. But wait a minute. If all we ever use are object pointers, how can we set the object itself to Nothing? The answer is: We can't.

When we set a reference to Nothing, something called the garbage collector kicks in. This little piece of software tries to determine whether or not an object should be destroyed. There are many ways to implement a garbage collector, but Visual Basic uses what is called the reference count method.


 Set objXL=Nothing.

When VB interprets the last line(where we generally sets our objects to Nothing), it will remove the existing reference. At this point, if the object has no more references, the garbage collector will destroy the object and de-allocate all its resources. If any other references point to the same object, the object will not be destroyed. 


Reduce the number of Lines: 
Avoid multiple statements especially when they can be clubbed into one line. For example - See these 2 macros
SLOW MACRO

    With Selection
        .WrapText = True
        .ShrinkToFit = False

    End With
FAST MACRO


    With Selection
        .WrapText = True: .ShrinkToFit = False

    End With

As you can see, you can club multiple statements into one using colon character(:). When you do this with multiple statements, it will decrease the readability but will increase the speed.

Compiler Logic: 


When we save the macro, it is virtually compiled and unlike it's human readable form as we saw in VBE(Visual Basic Editor), keywords(the dark blue words which you cannot use as variable) are saved as three-byte token which are faster to process as machine understand them better and variables, comments and literal strings which are not either keyword or directive are saved "as is". 

However VBA compiler tokenizes the word but it does not compress the lines and each line is maintained as is ending with the 'Carriage Return'. When the VBA macro is executed, VBA processor fetched single line at a time.

The tokens of the fetched line saved by virtual compiler are now interpreted and executed then next line is fetched and so on. When we combine multiple lines using colon into one line, we're reducing the number of fetch cycles the processor must go through.



Suggestion:
This change will bring minor difference in time due to faster processors today. Moreover, you cannot have more than 255 characters in a line and you won't be able to debug your code using F8 efficiently. So it's a kind of useless, there is no reason to trade-off with readability with such a minor change in time.

Declare variable as Variable and constant as Constant:
Many of us don't follow it. Like 
      Dim Pi As Double
      Pi = 3.14
instead use
      Const Pi As Double
      Pi = 3.14
Since, its value is never changed so it will be evaluated once during compilation unlike variable which are evaluated many times during the run-time.

Avoid Unnecessary Copy and Paste: 

Follow this table rules:
Avoid this Prefer this:
Sheet1.Range("A1:A300").Copy
Sheet2.Range("B1").PasteSpecial
Application.CutCopyMode = False   
'Clear Clipboard
'Bypass the Clipboard
Sheet1.Range("A1:A300").Copy Destination:= Sheet2.Range("B1")
Sheet1.Range("A1:A300").Copy
Sheet2.Range("B1").PasteSpecial xlPasteValues
Application.CutCopyMode=False

'Clear Clipboard
'Bypass the Clipboard if only values are required
Sheet2.Range("B1:B300").Value = Sheet1.Range("A1:A300").Value
Sheet1.Range("A1:A300").Copy
Sheet2.Range("B1").PasteSpecial xlPasteFormulas
Application.CutCopyMode=False

'Clear Clipboard
'Bypass the Clipboard if only formulas are required
Sheet2.Range("B1:B300").Formula = Sheet1.Range("A1:A300").Formula

'Same can be done with FormulaR1C1 and Array Formulas.

Use Worksheet Functions rather developing own logic: 

By using Application.WorkSheetFunction, we tell VBA processor to use native code rather than interpreted code as VBA understands the worksheet functions better than your algorithm. So, for example use :
      tProduct = Application.WorkSheetFunction.Product(Range("C5:C10"))
rather than defining your own logic like this:
      mProduct = 1
      For i = 5 to 20
            tProduct = tProduct * Cells(3,i)

      Next

Use 'For Each' rather than 'indexed For'

We can avoid using Indexed For when looping through collections. For example, take the code just before this tip. It can be modified to:
      For Each myCell in Range("C5:C20")
            
tProduct tProduct * myCell.Value
      Next
This is in relation to qualifying object again and again as using "WITH" statements.

Avoid using 'Macro Recorder' style code:Ya, the code will look genius and eventually perform like Genius too ! You'll better catch it with example, so use:
      [A1].Interior.Color = vbBlue
rather than
      Range("A1").Select
      Selection.Interior.Color = 
vbBlue
Using too many Select and Selection effects the performance drastically. 

Avoid using Variant and Object in declaration statements: 

Do not use Dim As Variant or Dim mCell As Object. By trying to be specific,we will save a lot of system memory this way, particularly in case of large projects. We may not remember which has been declared variant above and misuse a variable assigning any value to it which will be type-casted without errors. 

A variant's descriptor is 16 bytes long while double is 8 bytes, long is 4 bytes and integer is 2 bytes. Hence use Variant cautiously. As an example, use:
      Dim As Long rather than Dim As Variant
Similarly use:
      Dim tCell As Range 'or
      Dim tSheet As Worksheet
rather than
      Dim 
tSheet As Object 'or
      Dim 
tSheet As Object

Declare OLE objects directly: 

Declaring and defining the OLE objects in declaration statement is called 'Early Binding' but declaring an object and defining it later is called 'Late Binding'. Always prefer 'Early Binding' over 'Late Binding'. Now for example use:
      Dim 
objXL As Excel.Application
rather than
      Dim objXL As Object
      Set objXL = CreateObject("Excel.Application")

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

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