Excel VBA Removing the top row from a range

Every need a quick way to excise the header row from a range. Well this snippet will work with any range that has header.  The trick is to use the OFFSET() method of the range object.

Dim wksTemplate As Worksheet
Dim objRange As Range
Dim lngRowCount As Long

Set wksTemplate = ActiveWorkbook.Worksheets("Data")

Set objRange = wksTemplate.Cells(1, 1).CurrentRegion

        
lngRowCount = objRange.Rows.Count
Debug.Print "Before resizing " & lngRowCount
Set objRange = objRange.Offset(1, 0).Resize(objRange.Rows.Count - 1, objRange.Columns.Count)
lngRowCount = objRange.Rows.Count
Debug.Print "After resizing " & lngRowCount

C# Excel VBA Side by Side Utility Function: Does file exists

Another common utility function is to test for the existence in a file. In Excel VBA, the Dir function is used while the File method has static method called Exists in C#.

VBA

Public Function FileExists(strFileName As String) As Boolean
FileExists = Len(Dir(strFileName)) > 0
End Function

C#

{
   string strFileName = @"c:\temp\mySpreadsheet.xlsx";
   Console.WriteLine(File.Exists(strFileName) ? "File exists" : "File does not exist");
}

C# Excel VBA How to declare constants

Both in Excel VBA and C#, constants are immutable and must be initialized as they are declared. C# goes a step further and provides the readonly modifier to create an entity that is initialized at runtime and cannot be changed afterwards

VBA

Public Const msMODULE AS String="modApp"

C#

public const string msMODULE="modApp";

See Also

Syntax for using const statement

readonly examples

C# Excel VBA Side by Side Displaying an hourglass for a long running process

During a long running process, its a common Windows convention to display an hourglass/wait cursor.

Excel VBA

Option Explicit

Sub ShowHourGlass()

Application.Cursor = xlWait

‘Code here

Application.Cursor = xlDefault

End Sub

Tip: In Excel, when running a long process you also do additionally things to speed up the apparent speed.  Generally, the following propeties:
Application.ScreenUpdating and Application.DisplayAlerts are set to false and then turned back when the process ends.

Access VBA

 DoCmd.Hourglass True

C#

try
                {
                    objTask = new cTask();
                    strFileName = @"Z:\RMS\Back end\CDL\test CDL Reference Data - RMS.xls";
                    strAccessDb = @"H:\Projects\MDL\Locally Booked Update\WizardMDL Front End.accdb";
                    intStartTime = Environment.TickCount;
                    Application.UseWaitCursor = true;
                    recordsAffected = objTask.RefreshCDLReferenceFile(strFileName, strFileNameAccess: strAccessDb);
                    intEndTime = Environment.TickCount;
                    decElapsedTime = (decimal)((intEndTime - intStartTime) * .001);
                    MessageBox.Show("Imported " + recordsAffected.ToString() + " row(s) in " + decElapsedTime.ToString() + " sec(s)", "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, program, MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                finally
                {
                    Application.UseWaitCursor = false;
                }

C# Excel PIA Application level constants

To simplify always having to type the namespace Microsoft.Interop.Excel, an alias (Excel) was created . See this article for more detail.  Application level constants are available via Intellisense just under the Excel alias

C#

objWorksheet.Range["A1","B3"].AutoFormat(Excel.XlRangeAutoFormat .xlRangeAutoFormatClassic2);

C# VBA Side by Side Utility Function: Feature Not Availible Message Box

Sometimes deadlines are approaching and the features are not just up to snuff but the users want to see something.  This utility function will alert that better times are ahead.

Excel VBA

Public Sub FeatureNotAvailibleYet()
MsgBox "In development but feature is not availible yet ", vbInformation, "Confirm action"
End Sub

C#

public void FeatureNotAvailible()
        {

            MessageBox.Show("In development but feature is not availible yet","Confirm Action",MessageBoxButtons.OK,MessageBoxIcon.Information);
        }

C# Excel PIA setup

Referring to the Excel PIA

1. In the Solution Explorer, refer to the Excel Primary Interop Assembly (PIA) by clicking the project file
2. Click the "Add Reference" menu option
3. In the Add Reference dialog, click in the .NET tab and find Microsoft.Office.Interop.Excel.14.0.0.0 and click Ok

Define an Alias for the Excel PIA Namespace

using Excel=Microsoft.Office.Interop.Excel;

Declare and instantiate an Excel application object

Excel.Application objExcel=new Excel.Application();

C# VBA Side by Side Utility Function OkToOverWrite

Tired of always searching through previously written code in other projects for common utilities? Well search no more and use this function to prompt the user whether he/she really wants to delete a file.

VBA version

Public Function bOkToOverWrite(strFileName As String) As Boolean

Dim strUserMsg As String
Dim intResponse As Integer

strUserMsg = strFileName & " already exists.  Do you want to overwrite it?"
bOkToOverWrite = (vbYes = MsgBox(strUserMsg, vbYesNo + vbExclamation + vbDefaultButton2, "Overwrite File?"))

End Function

C# Version

public bool bOkToOverwrite(string strFileName)
        {
            bool bResponse;
            string strUserMsg = strFileName + " already exists. Do you want to overwrite it?";
            bResponse = (DialogResult.Yes == MessageBox.Show(strUserMsg, "OverWrite File?", MessageBoxButtons.YesNo, MessageBoxIcon.Information));
            return bResponse;
        }

C# Establishing A Connection with ADO.NET

To make a connection to a data store, set the ConnectionString property.  Some common connection string examples are:

Access 2003

@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Blog\Northwind.mdb";

Access 2010

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Blog\Northwind.accdb";

Sql Server 2012

"Data Source=MyServer;Initial Catalog=Northwind;Integrate Security=True;";

Example 1 : Connecting to Sql Server 2012

Example 2 : Connecting to Access 2010

string connectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Blog\Northwind.accdb;";

OleDbConnection  objConn = new OleDbConnection();

objConn.ConnectionString = connectionstring;

objConn.Open();

C# Execute an DML statement (INSERT,UPDATE or DELETE command)

To execute an action query (INSERT, UPDATE, or DELETE command), use the ExecuteNonQuery method of the DbCommand object.

 string ConnectionInfo = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Blog\Northwind.accdb;";
            string sql;
            int result;

            using (OleDbConnection objConn = new OleDbConnection())
            {
                try
                {
                    objConn.ConnectionString = ConnectionInfo;
                    objConn.Open();

                    using (OleDbCommand objCmd = new OleDbCommand())
                    {
                        sql = @"INSERT INTO Employees(Company,[Last Name],[First Name],[Job Title]) VALUES ('DUMMY Co','Doe','John','Consultant')";
                        objCmd.CommandText = sql;
                        objCmd.Connection = objConn;
                        objCmd.CommandType = CommandType.Text;
                        result = (int)objCmd.ExecuteNonQuery();
                        Console.WriteLine("Just added {0} rows",result);                       
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error occurred");
                    Console.WriteLine(ex.Message);                    
                }
            }

C# Returning a single value from a command

Sometimes you don't want the overhead of returning a large number of rows in your resultset but just need 1 value.  ADO.NET provides the ExecuteScalar method of the DbCommand object for this purpose:

string ConnectionInfo = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Blog\Northwind.accdb;";
            using (OleDbConnection objConn = new OleDbConnection())
            {
                try
                {
                    objConn.ConnectionString = ConnectionInfo;
                    objConn.Open();

                    using (OleDbCommand objCmd = new OleDbCommand())
                    {
                        objCmd.CommandText = "SELECT COUNT(*) FROM Customers";
                        objCmd.Connection = objConn;
                        objCmd.CommandType = CommandType.Text;
                        return (int)objCmd.ExecuteScalar();
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error occurred");
                    Console.WriteLine(ex.Message);
                    return 0;
                }
            }

Access Performing ETL - Part i

Importing a text file requires determining whether the file is a comma separated values (CSV) file, fixed width file or line appended with a carriage return file. This post will focus exclusively on working with a carriage return text file containing embedded DML sql statements. The logic looks a little like this:

·         Open the text file

·         Save the contents of the current line  to appropriate variable(s)

·         Use Access’s DoCmd. RunSQl method to execute the SQL statement found in the current line in the text file

·         Continuously loop through the text file until its end

How to do it

Useful commands file I/O material

Command	Note
Line Input #	Read a line up until the carriage return and assigns it to a variable
Input #	Read CSV data into variable(s)
Input()	Input function returns all of the characters it reads and unitl the EOF –into one variable,
Write #	Data written with Write # is usually read from a file with Input #.; Writes data to a file – separate by commas
Print #	Data written with Print # is usually read from a file with Line Input # or Input
Get #	Data read with Get is usually written to a file with Put
Put #	Writes data from a variable to a disk file.
EOF(n)
Open	Open a file for input or output
Close	Close a file

This input file contains SQL statement that insert data into a table

Figure 1

Figure 2 





SQL Server Programmable objects:Triggers Trips,Trick and Traps:

Questions:

1.       Triggers are fired for which data manipulation events?

2.       What are the names of the two virtual tables that SQL Server maintains to be used with triggers?

3.       What are the different types of triggers?

4.       If an After trigger and a constraint are defined on table, which fires first:  the trigger or the constraint?

5.       Which triggers types are new to the game (since Sql Server 2000)?

6.       Which execute quicker: triggers or constraints?

7.       What keyword cancels a pending transaction within a trigger?

8.       What function will determine which column have been modified in a trigger?

9.       After records have been deleted , which virtual table would they be stored in  by SQL Server?

10.   What global variable provides the number of records most recently affected by a command?

Answers:

1.       DML events: INSERT, UPDATE AND DELETE

2.       INSERTED DELETED

3.       AFTER AND INSTEAD OF

4.       CONSTRAINTS

5.       INSTEAD OF

6.       CONSTRAINTS because they usually a LESS complex than TRIGGERS are

7.       ROLLBACK

8.       UPDATED()

9.       DELETED

10.   @@ROWCOUNT

SQL Server programmable objects: stored procedures Tips, Tricks and Traps

1.       How to view dependency info on a stored procedure

sp_depends sp_creatediagram

 

2.       How to modify a stored procedure

Alter Procedure <<stored procedure name>>

 

3.       How to find out info about a stored procedure

Sp_help <<stored procedure name>>

 

4.       How to execute a stored procedure

EXEC <<stored procedure name>>

 

5.       How to declare a variable

a.       Regular variable: DECLARE @name  varchar(120)

b.      OUTPUT variable:  see line 3 in Listing 0003

Listing 0001

1.         create proc usp_wdTestWithVariableNOutputVar

2.         (

3.         @stateCode varchar(2),

4.         @Name varchar(50) OUTPUT

5.         )

6.         as

7.         BEGIN

8.         SET NOCOUNT ON

9.         SELECT * FROM States WHERE StateCode =@STATECODE

10.        SELECT @Name='This is explicily set'

11.        END

12.                     RETURN 0

c.       T-SQL variable;

Listing 0002

1.  create proc usp_wdTestWithTSQLvariables

2.  (

3.  @stateCode varchar(2)

4.  )

5.  as

6.  --SET NOCOUNT ON

7.  BEGIN

 

8.  DECLARE @UserMsg VARCHAR(1000)

 

9.  SET @UserMsg='This is relatively pain free query'

 

10.PRINT @UserMsg

11.SELECT *

12.FROM States

WHERE StateCode=@stateCode

 

END

RETURN 0

6.       How to assign a value to a variable

a.       Use the SELECT statement: SELECT @myvariable=’hello’

b.      USE the SET statement:  SET @name=’hello’

 

7.       How to execute a stored procedure?

a.       With no variables:  EXECUTE <<stored procedure name>>

 

b.      With input variables ONLY: Using the stored procedure in Listing 0002, the following command will pass NY to the stored procedure: EXEC usp_wdTestWithTSQLvariables 'NY'

 

 

c.       With an OUTPUT variable (see Listing 0003)

Listing 0003

1.  CREATE PROCEDURE usp_wdTestWIThOutputvariables

2.  (

3.  @FirstName VARCHAR(48) = 'Anonymous',

4.  @TableName varchar(256) OUTPUT

5.  )

6.  AS

7.   

8.  SET NOCOUNT ON

9.  --Step 1 Create local temp table

10. 

11.create table #User

12.(

13.      rOWid INT NOT NULL IDENTITY ,

14.      FirstName Varchar(48),

15.      LastName Varchar(96) NOT NULL

16.)

17. 

18. 

19.-- Step 2- load sample data

20. 

21.DECLARE @NewID INTEGER

22.INSERT INTO #User(FirstName,LastName)

23.VALUES (@FirstName,'Doe')

24.SELECT @Newid=@@IDENTITY

25. 

26. 

27.RETURN @NewId

28. 

d.      Running a query In the immediate window for the query in Listing 0003

Listing 0004

Declare @rv integer

Declare @tbl varchar(255)

Execute @rv=usp_wdTestWIThOutputvariables 'John',@tbl output

print 'The tble name is ' + @tbl

 

e.      Saving the Return value

Listing 0005

1.  Declare @rv integer

2.  Declare @tbl varchar(255)

3.  Execute @rv=usp_wdTestWIThOutputvariables 'John',@tbl output

4.  -print 'The tble name is ' + @tbl

5.  PRINT 'The new id is ' + CAST(@rv as varchar)

 

8.       How to suppress sending info messages back to the client.

SET NOCOUNT ON

 

9.       How to write a single comment

USE the 2 dashes: --. For example,  in Listing 0002, There is a comment  in line 6

 

10.   How to write a multi-line comment?

USE /* to begin a comment block and */to end the comment black

 

11.   After finishing write a stored procedure that complies correctly, what Is the recommended next step?

Assign permissions to the new created objected by using the command:

GRANT EXECUTE ON <<stored procedure name>> TO public