Excel VBA Copy the contents of a recordset to a range

To copy the results of query, you first have to execute a query - using either ADO or DAO (for this example, ADO will be used). Then, you use the CopyRecordset method of the range object

Excel VBA

Dim objConn as ADODB.Connection

Dim objCmd as ADODB.Command

Set objConn = Application.CurrentProject.Connection
Set objCmd = New ADODB.Command
With objCmd
    Set .ActiveConnection = objConn
    .CommandType = adCmdStoredProc
    .CommandText = "qryBrazilianRevenueExtract"
    Set rs = .Execute
End With


Set objRange=Worksheets(1).Cells(2,1).CurrentRegion
objRange.Cells(1, 1).CopyFromRecordset rs

C# VBA Side by Side using Enumerations

An enumeration is a set of named constants whose values are generally related.  In Excel VBA, an enumeration statement (Enum) is made in the Declarations section of a standard or public class module. In C#, an enumeration is defined directly within a namespace.  By default, the first enumerator is assigned the value of 0 and the value of each successive enumerator is increased by 1.

Excel VBA

Tip: IF the enum description, contains blanks or special characters, then surround the enum name with brackets ([ ]).

Public Enum OfficeProduct
    Access2007Only = 1
    [MSAccess95-2003]
    Excel2007Only
    [MSExcel95-2003]
    [XML]
End Enum

C#

public enum OfficeProduct {
       Access2007Only=1,
       MSAccess95_2003=2,
       Excel2007Only=3,
       MSExcel95_2003=4,
       XML=5};

C# Excel VBA Side by Side Putting a value in a cell

To enter a value in cell, use the value property of range object.  A range object can consist of a cell or range of cells.  One example is the following

C#

Prior to running this code, you will need to  follow these steps in 

using Excel=Microsoft.Office.Interop.Excel;
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application objExcel=new Excel.Application();
            //Excel.Worksheet objWks;
            try
            {  
                objExcel.Visible=true;
                var objWb = objExcel.Workbooks.Add();

                Excel.Worksheet objWks = objWb.Worksheets[1] as Excel.Worksheet;
                
                objWks.Cells[1, 1].value = 1;
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (objExcel !=null)
                {
                    objExcel=null;
                }
            }
        }
    }

C# Excel VBA Side by Side Open a workbook

The Workbook object has an Open method that opens a workbook. The following samples show how to both open a workbook in edit mode and read-only mode in both Excel VBA and C#.

Excel VBA

Readonly:

Dim strFileName as string
Workbooks.Open(FileName:=strExcelFile, ReadOnly:=True)

Normal mode:

Dim strFileName as string
Workbooks.Open(FileName:=strExcelFile)

C#

Readonly:

Excel.Workbook objWb;
objWb = objExcelApp.Workbooks.Open(Filename: fileName,ReadOnly:true);

Normal mode:

Excel.Workbook objWb;
objWb = objExcelApp.Workbooks.Open(Filename: fileName);

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;
                }
            }