Thursday, 23 April 2015

VBA Input Box and Message Box Response to Stop or Run a Macro

How Run or Stop a Macro with VBA Input Box and Message Box Responses
Sub Run_Stop_Macro()

Alert = MsgBox("The Macro will Update the Report with Latest Data ", vbYesNo, "Do You Wants to Update the Data Now ?")
If Alert = vbNo Then Exit Sub

ReTry:
PW = InputBox("Enter the Password To Run a Macro", "Please Enter the Password", vbOKCancel)

If PW = vbNullString Then Exit Sub

If Application.Proper(PW) <> "Tamatam" Then
MsgBox "Please Enter the Correct Password", vbCritical, "Wrong Password! Try Again!"
GoTo ReTry:
End If

'Macro Code Stuff for Your Requirement here.....You Go......

End Sub

Thanks,
TAMATAM

Tuesday, 21 April 2015

How to Run VBA Macro Code with a Microsoft Access Macro

 Running VBA Macro Code with a Microsoft Access Macro Functionality

Suppose we have a Macro Code , developed in MS-Access to fulfill some task as follows :

Sub Exp_Query_Data2Excel()
'Code Stuff here
End Sub

Now I want Run this Macro by assigning it to Quick Access Toolbar / Though Access Macro event.

But , In MS-Access , We cannot assign the VBA Macros to the Quick Access Tool bar. 

So that we can only run the Macro through Access Macro event as follows ;

First  define a Function in the VBA Macro Module and Call the above Macro into that Function as follows :

Function Exp_Query_Data()
Call Exp_Query_Data2Excel
End Function



Next , create Access Macro and define a RunCode event  then we have to Pass the Function Name , in the RunCode event as shown below : 



Now ,we can Run the VBA Code Macro "Exp_Query_Data2Excel()" with Access Macro "Run_A_Macro".

Thanks,
TAMATAM

How to Export MS Access Queries Results into Excel

VBA Macro to Export Microsoft Access Queries Results to an Excel
Option Compare Database
Sub Exp_Query_Data2Excel()
Dim OP_FileName As String

'Capturing Current Database Name

CurDB_Name = Left(CurrentProject.Name, Len(CurrentProject.Name) - 6)

'Defining Output File Name

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

Dim Qry As Object

Dim DB As Object

Set DB = CurrentDb

Set Qry = DB.QueryDefs

'Running all Access Queries and Closing them back.

For Each Qry In DB.QueryDefs
Qry_Name = Qry.Name
DoCmd.OpenQuery Qry_Name
DoCmd.Close acQuery, Qry_Name
Next Qry

'Exporting all Access Queries Results into an Excel Workbook

For Each Qry In DB.QueryDefs
Qry_Name = Qry.Name
DoCmd.TransferSpreadsheet acExport, , Qry_Name, OP_FileName, True
Next Qry
MsgBox "All the Queries Data Successfull Exported", vbOKOnly, "Job Done"
End Sub

Thanks ,
TAMATAM

Saturday, 18 April 2015

How to Export All MS Access Tables Data to Excel

Exporting All MS Access Tables Data Into to Excel Through VBA Macro
'This Macro Ignores or Skip the System Tables.
Sub Exp_Data2Excel()
Dim OP_FileName As String

'Captruring Current Database Name
CurDB_Name = Left(CurrentProject.Name, Len(CurrentProject.Name) - 6)

'Defining Output File Name
OP_FileName = CurrentProject.Path & "\" & CurDB_Name & "-" & Format(Date, "DDMMMYYYY") & ".xlsx"

Dim Tbl As Object
Dim DB As Object

Set DB = CurrentDb
Set Tbl = DB.TableDefs

For Each Tbl In DB.TableDefs
'Skipping System Tables
If Not (Tbl.Name Like "MSys*" Or Tbl.Name Like "~*") Then
Tbl_Name = Tbl.Name
'Exporting Each User Defined Table as a Sheet into Output File
DoCmd.TransferSpreadsheet acExport, , Tbl_Name, OP_FileName, True
End If
Next Tbl
End Sub

Thanks ,
TAMATAM
BI-Reporting Analyst


Monday, 13 April 2015

How to Select Dynamic Used Range in Excel VBA

VBA Macro to Select the Dynamic Actual UsedRange in Excel
Generally in most of the cases the Used Range is always dynamic.In this case the UsedRange.Selection method is not suitable.

So, we will use the following method to find the Actual Dynamic UsedRange.

Sub DynamicUsedRange(ByRef MyRange )
        Dim DataLastCell As Object
        Dim WS As Worksheet
        Dim MyRng
        
        Set WS = ActiveSheet
   
        'To Find the Last Row Number with data in the Range
        Set DataLastCell = WS.Cells.Find("*", WS.Cells(1, 1), , , xlByRows, xlPrevious)
        RC= DataLastCell.Row
        RC =  WS.Cells(Rows.Count, "A").End(xlUp).Row
          
        'To Find the Last Column Number with data in the Range
        Set DataLastCell = WS.Cells.Find("*", WS.Cells(1, 1), , , xlByColumns, xlPrevious)
        CC= DataLastCell.Column
        CC = WS.Cells(1, Columns.Count).End(xlToLeft).Column

        'To Find the Filtered Rows Count based on First Column with in the Filtered Range 
         FRC = WS.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).
         Cells.Count) - 1
        
        'To Find the End of the Range in  Data
        DR = DataLastCell.Address
        
   Set MyRng = WS.Range("A2:" & DR).Select
         MyRange = MyRng.Address   

        'To Find the Last Column Index Name in the Range
        CN = Split(Cells(, CC).Address, "$")(1)        
  Set MyRng = WS.Range("A2:" & CN & RC).Select
        MyRange = MyRng.Address

End Sub

Eg :



Note :
To select a Range from Active selection
Range(Selection, Selection.End(xlDown).End(xlToRight)).Select
Range(Selection, Selection.End(xlDown).Offset(0, 4)).Select
Resizing a Range :
Set rng = Range("A2").Resize(Range("A" & Rows.Count).End(xlUp).Row - 1, 4)

To Find the Last Row and Last Column Numbers in used range with data from a column say 'A', we can find as follows :
RC =  WS.Cells(Rows.Count, "A").End(xlUp).Row
           'Range("A" & Rows.Count).End(xlUp).Row

CC = WS.Cells(1, Columns.Count).End(xlToLeft).Column

To Find the Count of Filtered Rows with data from based on column say 'A',  :
FRC = WS.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count) - 1

Example Macro :
Copying the Dynamic Used Range from One Sheet to Another Sheet 

Sub Calling_DynRange(ByRef MyRange)

Dim WS as Object
Dim SrcSht as Object
Dim TgtSht as Object

Application.Calculation = xlManual
Application.DisplayAlerts = False

Set WS = ActiveSheet
Set SrcSht = ThisWorkbook.Sheets("Source_Sheet")
Set TgtSht  = ThisWorkbook.Sheets("Target_Sheet")

SrcSht.Activate
Call DynamicUsedRange(MyRange)
Range(MyRange).Select

Selection.Copy
'RC = (Selection.Rows.Count) ' Dynamic Used Range Rows Count

TgtSht.Activate
TgtSht .Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'TgtSht.Activate
'Range(MyRange).Copy Destination:=Range("A2")

Set WS=Nothing
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
End Sub

Notes :

Thursday, 9 April 2015

Run-time Error '2342' Can't run a SQL Query - "A RunSQL action requires an argument consisting of an SQL Statement"

Run-time error '3061'. Too few parameters. Expected 1
Generally , we use the DoCmd.RUNSQL Command to performs the SQL  Action queries like UPDATE, DELETE and INSERT.

But in case of Running a SELECT statement , the DoCmd.RUNSQL Command may fails and throws the Run-time errors '2342','3061'.
Example :
Suppose I am performing a Access Form Event , where the the EMP_Name is populating automatically into a Text box of Form after Updating the EMP_ID through Combo Box selection, as follows...

Private Sub Cmbo_EMP_ID_AfterUpdate()

E_ID = Me.Cmbo_EMP_ID.Value

Str_NAME = "SELECT EMP_NAME FROM MyTeam WHERE MyTeam.EMP_ID=" & """" & E_ID & """" & ";"
Me.txt_EMP_NAME.Value=DoCmd.RunSQL( Str_NAME )

End Sub


For the above case , DoCmd.RunSQL method will not workout , so , we have to follow the Recordset method as in the following syntax :

Private Sub Cmbo_EMP_NAME_AfterUpdate()
On Error Resume Next
Dim SQL_Str As String
Dim DB As Object
Dim RS_NAME As DAO.Recordset

E_ID = Me.Cmbo_EMP_ID.Value 'Storing the EMP_ID  from the Combo box Input.

Str_NAME= "Select EMP_NAME From MyTeam Where MyTeam.EMP_ID=" & """" & E_ID & """" & ";"

Set DB = CurrentDb
Set RS_NAME = DB.OpenRecordset(Str_NAME)

Me.txt_EMP_NAME.Value = RS_NAME.Fields("EMP_NAME").Value
End Sub

Thursday, 2 April 2015

How to Disable Editing Cells in Excel with Data Validation using VBA Macro

VBA Macro to Disable Editing Cells in Excel with Data Validation
To Disable Editing Cells in a Selected Range:
Sub Disable_Editing_Cells()
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=" "
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "You Cannot Edit the Cell"
        .InputMessage = ""
        .ErrorMessage = "The Cells You are Trying to Edit has a Data Validation , Still if you want to edit Please clear the Data Validation Rules"
        .ShowInput = True
        .ShowError = True
    End With
End Sub
-----------------------------------------------------------------------------------------------------------------
To Enable Editing Cells in a Selected Range:
Sub Enable_Editing()
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With

End Sub

Alert Styles :
AlertStyle:=xlValidAlertStop
AlertStyle:=xlValidAlertWarning  
AlertStyle:=xlValidAlertInformation

How to Insert or Delete Blank or Null Values in to a SQL Table

SQL DELETE Statement
The DELETE statement is used to delete Rows or Records in a Table.

SQL DELETE Syntax:

DELETE FROM table_name WHERE some_column=some_value

Note: 
Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!


Example:


Create Table EMPLOYEE

(
EID INT,
EMPNAME VARCHAR(25) , 
DEPT VARCHAR(25),
DOJ DATE , 
SAL DECIMAL(7,2)
)

GO

Entering Records with Null or Blank Values :


INSERT INTO EMPLOYEE 

SELECT 123,'REDDY','TECH','02-20-2014',25450.25 UNION ALL
SELECT 234,'RAMYA','PROD','03-20-2014',35450.25 UNION ALL

SELECT '','RAJU','MARKETING','04-20-2014',45450.25 UNION ALL 'EID as Blank Value

SELECT NULL,'KAJAL','CRM','04-20-2014',55450.25 UNION ALL 'EID as NULL Value
SELECT 456,'SRUTHI','','05-20-2014',65450.25 'DEPT as BlankValue

GO


EID
EMPNAME
DEPT
DOJ
SAL
123
REDDY
TECH
2/20/2014
25450.25
234
RAMYA
PROD
3/20/2014
35450.25
0
RAJU
MARKETING
4/20/2014
45450.25
NULL
KAJAL
CRM
4/20/2014
55450.25
456
SRUTHI

5/20/2014
65450.25

Deleting Records with Null or Blank Values :


DELETE FROM EMPLOYEE WHERE  EID = '' OR EID IS NULL OR DEPT=''


Note :

When you pass Blank value to a Numeric Field , it will take '0' as Value.
When you pass Blank value to a Text Field , it will take '' as Value.
To delete a record with NULL value , we have to use IS NULL condition. No Logical operator can be applicable.

Wednesday, 1 April 2015

How to Check and Delete if a Table or Database already exists in SQL Server

SQL Query to Check and Delete if a Table or Database already exists in SQL Server
Use caution when dropping tables. Once you drop a table, you will not be able to get it back unless you restore a backup.
'Checking the Table Existence , If yes Dropping it.
'Here it checks the Table Name from all the Schemas.
IF EXISTS ( SELECT NAME from SYS.TABLES where NAME ='EMPLOYEE')
DROP TABLE EMPLOYEE
GO

'Creating the Table again after Dropping.
Create Table EMPLOYEE (
EID INT,
EMPNAME VARCHAR(25)
DEPT VARCHAR(25),
DOJ DATE
SAL DECIMAL (10,2)
)
GO 

'Checking the Database Existence , If yes Dropping it.

IF EXISTS ( SELECT [Name] From SYS.DATABASES Where [Name] = 'TAMATAM' ) 
DROP DATABASE TAMATAM
GO 

'Creating the Database again after Dropping.
CREATE DATABASE TAMATAM
GO

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts