Opening Excel Application ,Modify,Save and Close in .NET

Opening Excel Application ,Modify,Save and Close in .NET

Public Function Change_Excel_Cell_Formatting(ByVal pFilePath As String, ByVal pSheetNo As Integer, ByVal pRowIndex As Integer, ByVal pMsgTitle As String) As Boolean
        Dim xla As Object
        Dim xlw As Object = Nothing
        Dim xls As Object
        Try
            xla = CreateObject("Excel.Application")
            xlw = xla.WorkBooks.Open(pFilePath)  ' GetObject(pFilePath)
            xls = xlw.sheets(pSheetNo)
            'xls.Rows(pRowIndex & ":" & pRowIndex + 1).Select()   '' Prob with this line that got error  

            ''as Too many fields, so limiting only 255 cols
            xls.Range(xls.cells(pRowIndex, 1), xls.Cells(pRowIndex + 1, 255)).Select()
            xla.Selection.NumberFormat = "@"

            xls.Range(xls.Columns(256), xls.Columns(xls.columns.count)).Select()
            xla.Selection.Delete()

            xla.DisplayAlerts = False
            xlw.save()
            Return True
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message, pMsgTitle, 

            Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error)
            Return False
        Finally
            If Not IsNothing(xla) Then xla.DisplayAlerts = False
            If Not IsNothing(xlw) Then xlw.close()
            xls = Nothing
            xlw = Nothing
            If Not IsNothing(xla) Then xla.Quit()
            If Not IsNothing(xla) Then 

            System.Runtime.InteropServices.Marshal.ReleaseComObject(xla)
            xla = Nothing
            GC.Collect()
        End Try
    End Function

Getting Installed Version of Microsoft Office in .NET

Getting Installed Version of Microsoft Office in .NET

Private Function iCheck_Excel_Version_Installed(ByVal pMsgTitle As String) As Integer

        'The subkey's string value we check is like
        'Excel.Application.<version>, i e Excel.Application.10

        'The subkey we are interested of is located under the
        'HKEY_CLASSES_ROOT class.
        Const stXL_SUBKEY As String = "\Excel.Application\CurVer"

        Dim rkVersionKey As RegistryKey = Nothing
        Dim stVersion As String = String.Empty
        Dim stXLVersion As String = String.Empty

        'A very simple regular expression where:
        '[8-9] means look for the numbers 8 and 9
        'and start in the end of the expression.
        'Dim stRegExpr As String = "[8-9]$"

        'If we need to make sure that for instance Excel 2003 (11) or
        'later is installed then the above expression can be modified
        'to:
        'Dim stRegExpr As String = "[8-9]$|[1]0$"
        Dim stRegExpr As String = "[8-9]$|[1][0-1]$"

        Dim iVersion As Integer = Nothing

        Try
            'Here we try to open the subkey.
            rkVersionKey = Registry.ClassesRoot.OpenSubKey(name:=stXL_SUBKEY, _
                                                           writable:=False)

            'If it does not exist it means that Excel is not installed at all.
            If rkVersionKey Is Nothing Then
                iVersion = 0
                Return iVersion
            End If

            'OK, Excel is installed let's find out which version is available.
            stXLVersion = CStr(rkVersionKey.GetValue(name:=stVersion))

            'Here we match the retrieved value with our created regular
            'expression.
            If Regex.IsMatch(input:=stXLVersion, pattern:=stRegExpr) Then
                'Either Excel 97 or Excel 2000 is installed.
                iVersion = 1
                Return iVersion
            Else
                'Excel 2002 or later is available.
                iVersion = 2
                Return iVersion
            End If
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message, pMsgTitle,   

             Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error)
            Return Nothing
        Finally
            If Not rkVersionKey Is Nothing Then rkVersionKey.Close()
        End Try
    End Function

Releasing Excel Object from Memory in .NET

Releasing Excel Object from Memory / Task manager in .NET

Public Function Excel_Memory_Release(ByRef xla As Object, ByRef xlw As Object, ByRef xls As Object, Optional ByVal pSaveChanges As Boolean = True) As Boolean
        If Not IsNothing(xla) Then xla.DisplayAlerts = False
        If Not IsNothing(xlw) Then
            If Not pSaveChanges Then
                xlw.Close(savechanges:=False)
            Else
                xlw.close()
            End If
        End If
        xls = Nothing
        xlw = Nothing
        If Not IsNothing(xla) Then xla.Quit()
        If Not IsNothing(xla) Then System.Runtime.InteropServices.Marshal.ReleaseComObject(xla)
        xla = Nothing
        GC.Collect()
        Return True
    End Function

Enable Multilanguage Application in .NET

Enable multilanguage application in .NET :

Here i'm going to explain about how to enable multilanguage support in windows application ( VB.NET ).

Multilanguage can be done through resources file creating. Multilanguage enabled Resource files  can be created in various ways based on your requirements.


A. System Resource files Generation
-----------------------------------------------
1. Resource files generation for each form. 
      You have to do the following steps for each form.
   

    1.Create a new Windows Application named "WindowsApplication1". For details, see How to: 
       Create a Windows Application Project.

    2.In the Properties window, set the form's Localizable property to true.
       The Language property is already set to (Default).

    3.Drag a Button control from the Windows Forms tab of the Toolbox to the form, and set its Text   
       property to Hello World.

    4.Set the form's Language property to German (Germany).

    5.Set the button's Text property to Hallo Welt.

    6.Set the form's Language property to French (France).

    7.Set the button's Text property to Bonjour le Monde. You can resize the button to accommodate the   
       longer string, if necessary.

    8.Save and build the solution.

    9.Click the Show All Files button in Solution Explorer.
        The resource files appear underneath Form1.vb, Form1.cs, or Form1.jsl. Form1.resx is the                   
        resource file for the default culture, which will be built into the main assembly. Form1.de-DE.resx is the  
        resource file for German as spoken in Germany. Form1.fr-FR.resx is the resource file for French as    
        spoken in France.

        In addition, you will see files appear named Form1.de.resx and Form1.fr.resx. Visual Studio  
        automatically creates these files in order to work around a limitation in Visual SourceSafe having to do 
        with adding new files to a project during a save operation. The .resx files are empty and contain no    
        resources.

  10.Press the F5 key or choose Start from the Debug menu.  

  Assigning Language based on selection :   Selection control may be link or combobox or form load etc..
  on selection changed , plz add code like this
   
        ' Visual Basic
        ' Sets the UI culture to French (France).
        Thread.CurrentThread.CurrentUICulture = New CultureInfo("fr-FR")

        // C#
        // Sets the UI culture to French (France).
        Thread.CurrentThread.CurrentUICulture = new CultureInfo("fr-FR");

         Note : this seems easier but in real time, we dont want to translate in each screen which already  
         translated.
          ex. customer code label control will be used through out the application or in various places.
                now nobody will like to translate customer code label in all forms. so in this case, we can go for  
                Manual Resource file approach.

2. common resource files for whole application.
    Here we can muliple resource files in MyProject like Resources.resx, Resources.zh-CN.resx. In each file  
    you can transate it but key should be same in both file.
    Getting Resource file values :
   
    My.Resources.<Key>.  

    when you change culture to Zh-CN then it would take values from specified language resource file  
    automatically. 

   

B. Manual Resource file
-------------------------
    Instead of transating on each screen (Approach 1 here), we can cumulate all distinct words together and  
    make a separate resource file. then convert it once.
    but you have to assign manually to a control or you can write common function to load.
 
         Reading value from resource file :

    1.On the Project menu, click Add New Item.
    2.In the Templates box, select the Assembly Resource File template. Type the file name   
       "WinFormStrings.resx" in the Name box. The file WinFormStrings.resx will contain fallback resources 
        in English. These resources will be accessed whenever the application cannot find resources more 
        appropriate to the UI culture.

        The file is added to your project in Solution Explorer and automatically opens in the XML Designer in   
        Data view.
    3.In the Data Tables pane, select data.
    4.In the Data pane, click an empty row and enter strMessage in the name column and Hello World in the  
       value column.
       You do not need to specify the type or mimetype for a string; they are used for objects. The type   
        specifier holds the data type of the object being saved. The MIME type specifier holds the base type 
         (base64) of the binary information stored, if the object consists of binary data.
    5.On the File menu, click Save WinFormStrings.resx.
    6.Do steps 1-5 twice more to create two more resource files named WinFormStrings.de-DE.resx and   
       WinFormStrings.fr-FR.resx, with the string resources specified in the following table. The file 
       WinFormStrings.de-DE.resx will contain resources that are specific to German as spoken in 
        Germany.  The file WinFormStrings.fr-FR.resx will contain resources that are specific to French as   
        spoken in France.

        To access the manually added resources :
   
         ' Visual Basic
         Imports System.Resources

         // C#
         using System.Resources;
   

         ' Visual Basic
         ' Declare a Resource Manager instance.
         Dim LocRM As New ResourceManager("WindowsApplication1.WinFormStrings",       
         GetType(Form1).Assembly)
   
          ' Assign the string for the "strMessage" key to a message box.
           MessageBox.Show(LocRM.GetString("strMessage"))

    // C#
    // Declare a Resource Manager instance.
    ResourceManager LocRM = new      
    ResourceManager("WindowsApplication1.WinFormStrings",typeof(Form1).Assembly);
    // Assign the string for the "strMessage" key to a message box.
    MessageBox.Show(LocRM.GetString("strMessage"));


    Example :

    I'm assiging cultures in form load here.


    Public Sub Load_Grid_ResourceFile(ByVal LangId As String)
            Dim _Curr_culture As String = My.Application.UICulture.Name
            Dim _culture As String
   
            '' RESOURSE SETTINGS

            '' gRm_Screen instance is for Screen Labels
            '' resource_manager instance is for grid columns text
            '' _Culture instance is for error msgs

            If LangId = "2" Then
                resource_manager = New System.Resources.ResourceManager("<Other language Resource file  
                name>", Me.GetType.Assembly)
                gRm_Screen = New Resources.ResourceManager("<Other language Resource file name>", 
                Me.GetType.Assembly)
                _culture = "zh-CN"
            Else
                   resource_manager = New System.Resources.ResourceManager("<English language Resource 
                   file name>", Me.GetType.Assembly)
                   gRm_Screen = New Resources.ResourceManager("<English language Resource file name>", 
                   Me.GetType.Assembly)
                   _culture = "en-US"
               End If
       
               Dim _cultureinfo As New System.Globalization.CultureInfo(_culture)
               '_cultureinfo.DateTimeFormat.DateSeparator = "/"
               My.Application.ChangeUICulture(_culture)
               BusinessLogicLayer.My.Resources.Culture = _cultureinfo
               DataAccessLayer.My.Resources.Culture = _cultureinfo
    END sub
       
     

    Common function to load to controls :
   
    Public Sub get_Literals(ByRef FrmObj As Object, ByVal LangId As String)
        If gRm_Screen Is Nothing Then Exit Sub
        Write_Literals(FrmObj, Nothing)
        For Each Cntrl In FrmObj.Controls
            GetControls(FrmObj, Cntrl)
        Next
    End Sub
   
   
    Private Sub Write_Literals(ByRef FrmObj As Object, ByRef Cntrl As Control)
        If Cntrl Is Nothing Then
            If Trim(FrmObj.Text) <> "" Then
                Dim _ResourceValue = get_ResourceString(FrmObj.Text)
                If Trim(_ResourceValue) <> "" Then
                    FrmObj.Text = _ResourceValue
                End If
            End If
        Else
            If Trim(Cntrl.Text) <> "" Then
                'Try
                Dim _ResourceValue = get_ResourceString(Cntrl.Text)
                If Trim(_ResourceValue) <> "" Then
                    Cntrl.Text = _ResourceValue
                End If
                'Catch ex As Exception

                'End Try
            End If
        End If
     End Sub


    Public Function get_ResourceString(ByVal pValue As String, Optional ByVal     
        ReturnOldValueWhenNotFound As Boolean = False) As String
        Dim _ValidValue As String
        Dim _ResourceValue As String
        '' Converting current value to resource file's format value
        _ValidValue = get_ValidString(pValue)

        '' Getting value from resource file
        _ResourceValue = gRm_Screen.GetString(_ValidValue)
        If Trim(_ResourceValue) <> "" Then
            Return _ResourceValue
        Else
            If ReturnOldValueWhenNotFound Then
                Return pValue
            Else
                Return ""
            End If
        End If
        End Function
   
    Private Function get_ValidString(ByVal pValue As String) As String
        Return UCase(Replace(Replace(Trim(pValue), " ", "_"), vbCrLf, "_"))
        End Function

    Private Sub GetControls(ByRef FrmObj As Object, ByRef Cntrl As Control)
        If Cntrl.HasChildren = True Then
            Write_Literals(FrmObj, Cntrl)
            For Each chldcntrl In Cntrl.Controls
                GetControls(FrmObj, chldcntrl)
            Next
        Else
            Write_Literals(FrmObj, Cntrl)
        End If
    End Sub





You can refer this to get clear idea

    http://social.msdn.microsoft.com/Forums/en-US/vblanguage/thread/57a00566-e13d-449d-bd30-a53a9dc6b838
    http://msdn.microsoft.com/en-us/library/y99d1cd3(v=VS.80).aspx (Windows Forms)
    http://msdn.microsoft.com/en-us/library/c6zyy3s9.aspx (ASP.NET)

Adding Listview / Additional / ActiveX Control in Excel 2007 VBA

Question :

  How to add listview control in Excel 2007 VBA ?

 

Answer :  

Step 1 : Open Excel Run->Excel-> OK which will open New Excel

Step 2 : Right click on any sheet and Click View Code, which would open VBA Window

Step 3 : Right click on VBA Project  ->Insert -> UserForm , which would create New User Form

Step 4 : Default Toolbox contains only standard controls ,if you want additional / ActiveX controls then    you have add those controls manually

Step 5 : To Add Additional Controls manually select Tools menu->Click Additional Controls.. 

Step 5 : Choose any control that you want. for example select Microsoft ListView Control Version 6.0 and click OK

Step 6 : Now ListView Control is added in Toolbox

Step 7 : Now drag and place listview control in Form.

Step 8 :  Now you can start write your logic, Sample code here

              http://www.dailydoseofexcel.com/archives/2006/12/26/listview/

   

   

How to create Batch file to execute sql scripts ?

Question :

Hi friends,  

      i want to create batch file to execute all my .sql scripts.

I have all table ( all table scripts in single file ) ,Udds ( all udds in single file ) ,Stored procedures( separate file for each SPs ),Functions ( Separate file for each Functions ),Triggers and views scripts in .SQL file.  

can anybody tell me how to create batch file for executing all these scripts in sql server ?.  

   while executing, it should ask Database name,server name, password. if these details are given then it should execute my all scripts in given database

, if any error thrown then that error and procedure name alone have to move to separate log file..

Answer :  

1. Open New notepad 

2. Paste the below code and change content as per your requirement

@echo off
cls

echo *******************************************************************************
echo *                     DATABASE DEPLOYMENT                    *
echo *******************************************************************************
echo *                     WARNINGS                                        *       
echo *******************************************************************************
echo *  1. You can give some warnings like take backup before executing             *
echo *******************************************************************************


set /p SName=Server Name :
set /p UName=User Name :
set /p Pwd=Password :
set /p DbName=Database Name :


set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?

if '%choice%'=='y' goto begin
goto end

:begin
if exist _Deploy.txt del _Deploy.txt

@echo on



@echo UDDs >>_Deploy.txt
@echo ******************* >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "UDDs"\UDDs.sql >> _Deploy.txt 2>&1


@echo TABLES >>_Deploy.txt
@echo ******************* >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Tables"\TABLE_SCRIPT.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Tables"\Insert_Script.sql >> _Deploy.txt 2>&1


@echo FUNCTIONS >>_Deploy.txt
@echo ******************* >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Functions"\TEST_FN.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Functions"\TEST1_FN.sql >> _Deploy.txt 2>&1


@echo STORED PROCEDURES >>_Deploy.txt
@echo ***************** >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Stored Procedures"\CheckInternalmachineType_SP.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Stored Procedures"\CheckLineType_SP.sql >> _Deploy.txt 2>&1


@echo TRIGGERS >>_Deploy.txt
@echo ***************** >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Triggers"\Master_TR.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Triggers"\Price_Master_TR.sql >> _Deploy.txt 2>&1


@echo VIEWS >>_Deploy.txt
@echo ***************** >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Views"\Gate_Pass_Rpt_VW.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Views"\GatePass_VW.sql >> _Deploy.txt 2>&1

@notepad _Deploy.txt

:end

 

 3. then Save this file as filename.bat

4. now execute this by giving correct server details . if any error then it would write in _Deploy.txt.

 

How to restore the database through Query

How to restore the SQL SERVER database through Query ?

Question :


Hi,
     Everybody knows that restoring the database from backup file through sql server restore wizard.  but sometimes we may get error while restoring through sql server restore wizard like 'failed,Reached the end of the file'.

Now you can get success restore by restoring through query.


Answer : 

Restore the database through query by following,


1. JUST TO GET HEADER DETAILS

    RESTORE HEADERONLY
    FROM DISK = N'D:\DB\SPICE.bak'
    WITH NOUNLOAD;
    GO

2. TO KNOW THAT BACKUP FILE IS CORRUPTED

    RESTORE VERIFYONLY
    FROM DISK = N'D:\DB\SPICE.bak'
    GO

3. EXECUTE THIS AND YOU WILL GET LOGICAL NAME FOR TYPE D AND L (i,e DATA FILE AND LOGICAL FILE )

    RESTORE FILELISTONLY
    FROM DISK = N'D:\DB\SPICE.bak'


4. TO RESTORE BAK FILE IN NEW DATABASE

    RESTORE DATABASE NEWDB
    FROM DISK = N'D:\DB\SPICE.bak'
    WITH REPLACE ,
    MOVE 'MDF_FILENAME'  
-- SPECIFY LOGICAL NAME OF DATAFILE HERE FROM THE ABOVE FETCH  (ie where type = D)
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Spice.mdf',     -- SPECIFY NEW DATAFILE NAME
    MOVE 'LDF_FILENAME'   
-- SPECIFY LOGICAL NAME OF LOGICALFILE FROM THE ABOVE FETCH   (ie where type = L)
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Spice_log.ldf'     -- SPECIFY NEW LOGICALFILE NAME
   
   
5. Now you will get success restore message.