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