Thursday, 10 November 2016

Collate multiple excelhseets in to one Excelsheet

In this post we are going to see how to merge the multiple Excelsheets data in to one Excelsheet using VBA Macro, sometimes there is a scenario where you have same structured data in multiple Excelsheets, you may want that data to collate in to one excelsheet, To do this we are going to do a programming which will have two buttons , one is for the browse the folder where you can find the all Excelsheets which are need to merge under one folder path, next button is used to merge.


First we have to check whether Excel is a macro enabled , to do this we have enable macro using the following steps in mentioned in the below url.
http://dotnetvisio.blogspot.in/2016/11/how-to-enable-developer-tab-in.html


Then now need to Developer Tab and click on the Activex Controls and Drag the Two buttons, Then right click on the both the buttons and edit the name of that, Then we have to double click on each button and start writing the code.















First we have to browse a folder where we are kept the files which are needs to merge., Let we start write the code for Browse.


Code for Browse:
*********************
Private Sub CommandButton1_Click()
    Browse
End Sub


Sub Browse()
  Dim folderdlg As FileDialog
  Set folderdlg = Application.FileDialog(msoFileDialogFolderPicker)
  folderdlg.AllowMultiSelect = True
  folderdlg.Show
  
  If folderdlg.SelectedItems.Count > 0 Then
    folderPath = folderdlg.SelectedItems(1)
  End If
  
End Sub






Click on the Merge button, and start typing the code for the Merge.

Code for Collate
**********************
Private Sub CommandButton2_Click()
    Merge
End Sub

Sub Merge()
    
    Dim ProBook As Workbook
    Dim mSheet As Worksheet
    Dim cFileName As String
    Dim savefilepath As String
    Dim cRow, cColumn  As Integer
    Dim pRow, pColumn As Interior
    Dim firsttime As Boolean
    
    Dim filedlg As FileDialog
    
    If folderPath = "" Then
        MsgBox "Please select the folder, for merge the files, then click Merge"
    End If
    
    If folderPath <> "" Then
   
    folderPath = folderPath & "\"
   
    Set filedlg = Application.FileDialog(msoFileDialogSaveAs)
    filedlg.Show
    firsttime = True
    If filedlg.SelectedItems.Count > 0 Then
    
        savefilepath = filedlg.SelectedItems(1)
        cFileName = Dir(folderPath & "*.xl*")
          
        Set mSheet = Workbooks.Add(XlWBATemplate.xlWBATWorksheet).Worksheets(1)
        cRow = 1
        cColumn = 1
            
        Do While cFileName <> ""
           
            Set ProBook = Workbooks.Open(folderPath & cFileName)
      
            Dim LastRow As Long
            Dim lastCol As Long
            Dim srcRange As Range
            Dim dstRange As Range
            
            lastCol = ProBook.Worksheets(1).Cells(1, Columns.Count)_
                      .End(xlToLeft).Column
                   
            LastRow = ProBook.Worksheets(1).Cells.Find(What:="*", _
                       After:=ProBook.Worksheets(1).Cells.Range("A1"), _
                       SearchDirection:=xlPrevious, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows).Row
            
          Dim col_letter As String
          
          col_letter = Column_Letter(lastCol)
          If firsttime Then
            Set srcRange = ProBook.Worksheets(1).Range("A1:" & col_letter & LastRow)
          Else
            Set srcRange = ProBook.Worksheets(1).Range("A2:" & col_letter & LastRow)
          End If
                        
          Set dstRange = mSheet.Range("A" & cRow)
          Set dstRange = dstRange.Resize(srcRange.Rows.Count, _
             srcRange.Columns.Count)
             
          ' Copy over the values from the source to the destination.
          dstRange.Value = srcRange.Value
          
          ' Increase row so that we know where to copy data next.
          cRow = cRow + dstRange.Rows.Count
          
          
          ' Close the source workbook without saving changes.
          ProBook.Close savechanges:=False
          
          firsttime = False
          
          ' Use Dir to get the next file name.
          cFileName = Dir()
          
        Loop
    
    End If
    
    mSheet.Columns.AutoFit
    mSheet.SaveAs (savefilepath)
    
    
    End If
    
    
    
End Sub

Function Column_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Column_Letter = vArr(0)
End Function






Click on the browse button and select the folder, You can see this folder have three excel files, data1.xlsx, data2.xlsx, data3.xlsx




Click in the Merge button, Give the name of the excel file as "MergeData" in which you have to save the collated data, thats it, All data are merged in one excel, in this sample we are collating three Excel files









Data1.xlsx





Data2.xlsx







Data3.xlsx






Output:








From this post you can see how to create a program in excel to collate a data from multiple excel file to one excel file

Friday, 4 November 2016

Find the last Row and last column in Excel

In this post we are going to see how to find out the Last Row and Last Column in a Excel sheet. this will be helpful in many situations.



            Dim LastRow As Long
            Dim lastCol As Long
            
            
            lastCol = ProBook.Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
                   
            LastRow = ProBook.Worksheets(1).Cells.Find(What:="*", _
                       After:=ProBook.Worksheets(1).Cells.Range("A1"), _
                       SearchDirection:=xlPrevious, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows).Row







From this post you can learn how to find the Last Row and Last column in a Excel sheet

How to enable the Developer Tab in the Excelsheet and enable the Macro

In this post we are going to see how to enable the Developer Tab in the Excelsheet and enable the macro in the Excel for processing the VBA Code.


To Enable the Developr tab , please do the following steps.

1.  Right click any where on the Ribbon and click the Customize the Ribbon, this will launch a window .





2. Click the customize the Ribbon and select the Main tab in the customize the Ribbon option, then check the Developer 
     option.





3. This check option enables the Developer Tab in the Ribbon., we can find the Develop tab next to the View Tab.

4. On the Developer tab click the Insert option, where you can find many activex Controls , which is used to do the           
     programming.







Enable a Macro
***********************

5. Click the Design mode button first , which is present in the developer tab.

6. Click the command button and drag it to the Excel sheet, 







7. Right click on the button and you can see many properties like View code, Edit name of the control, etc.

8. Now click on the View code which will bring the macro code for that button .















9. Type some sample code , for example here we are using the Folder browse code , Here in this sample i am creating a method named Browse and called it from inside the button handler.

10. now close the visual basic editor, and deselect the Design mode, 

11. Now click on the button, you can see the code is executed in Excel sheet, a folder browse dialog will appear in front          of you,













From this post you can learn how to enable a developer option in Excel sheet and enable a macro 

Thursday, 3 November 2016

Create a Folder Browse Dialog in Excel using VBA

In this post we are going to see how to create a Folder Browser Dialog in Excel using VBA, normally there is a common object which is used for savefile, select file, open folder separate based on parameters.



Dim folderPath As String


Sub Browse()
  Dim folderdlg As FileDialog
  Set folderdlg = Application.FileDialog(msoFileDialogFolderPicker)
  folderdlg.AllowMultiSelect = True
  folderdlg.Show
  
  If folderdlg.SelectedItems.Count > 0 Then
    folderPath = folderdlg.SelectedItems(1)
  End If
  
End Sub












When click on the Excel Files Folder , it will launch the folder browse dialog.








From the above code you can learn how to browse a folder using VBA in EXCEL

Tuesday, 11 October 2016

Create a angular2 service and inject it in to Components

In this post we are going to see how to create a service in Angular2 application and inject it in to Component by Dependency injection. For this we are going to create a sample employee service  and inject it in to two components , for this we are going to create two components Employee and Student Component, But there is a issue in this if we are injecting service in to two components individually, then the new instance are created independently for this two service, so the data wont share or change wont reflect in other component



Creating a Service:

import { Injectable } from '@angular/core';

@Injectable()
export class DataService{

private data:string[]=["Rajesh","suresh","ramu"];

getEmployees(){
    return this.data;
}

addEmployee(name){
    this.data.push(name);
}

}





Inject Service independently in Component:

   In this we are injecting service in to the components using providers in there Component decorators directly, so what will happen his it will create a new instance , because of this each and every component will use the separate instance, nothing will be shared across the components

import { Component } from '@angular/core';
import { DataService } from './data.service';

@Component({
    selector:'employee',
    template:`
    <div style="display:inline-block;width:400px;vertical-align:top">
        <h1>Employee </h1>
        <div>
            <input type="text" #emp />
            <button (click)="addEmployee(emp.value)" 
                    class="btn btn-sm btn-primary">Add Emp</button>
            <button (click)="getEmployee()" 
                    class="btn btn-sm btn-primary">Get Employees</button>
            <div>
                <ul class="list-group">
                    <li class="panel list-group-item-primary" 
                        *ngFor="let e of employees" style="list-style:none;">
                        {{e}}
                    </li>
                </ul>
            </div>
        </div>
    </div>
    `,
    providers:[DataService]
})
export class EmployeeComponent
{
    employees:string[];

    constructor(private dataservice:DataService){
        
    }

    addEmployee(employeeName)
    {
        this.dataservice.addEmployee(employeeName);
    }

    getEmployee(){
        this.employees = this.dataservice.getEmployees();
    }
}


@Component({
    selector:'student',
    template:`
    <div style="display:inline-block;width:400px;vertical-align:top">
        <h1>Student </h1>
        <div>
            <input type="text" #std />
            <button (click)="addStudent(std.value)" 
                    class="btn btn-sm btn-primary">Add Student</button>
            <button (click)="getStudents()" 
                     class="btn btn-sm btn-primary">Get Students</button>
            <div>
                <ul class="list-group">
                    <li class="panel list-group-item-primary" 
                        *ngFor="let s of students" style="list-style:none;">
                        {{s}}
                    </li>
                </ul>
            </div>
        </div>
        </div>
    `,
    providers:[DataService]
})
export class StudentComponent
{
    students:string[];

    constructor(private dataservice:DataService){
        
    }

    addStudent(studentName)
    {
        this.dataservice.addEmployee(studentName);
    }

    getStudents(){
        this.students = this.dataservice.getEmployees();
    }
}




Module.ts:

    import { NgModule } from '@angular/core';
    import { BrowserModule } from '@angular/platform-browser';
    import { FormsModule } from '@angular/forms';
    import { BaseRequestOptions, Http, Headers } from '@angular/http';
    import { webformComponent } from './Component/web.component'  
    import { EmployeeComponent,StudentComponent } from './employee.component'
    import { DataService } from './data.service';

    @NgModule({
        imports: [BrowserModule,FormsModule],
        exports: [],
        declarations: [webformComponent,EmployeeComponent,StudentComponent],       
        bootstrap:[webformComponent]
    })
    export class AppModule { 

    }


In module we can see the change in both the types

For ex:
In above Example we can see that the element add in the Employee doesn't reflect in Student


Output:

















Inject Service Globally for all component:

  In this we are injecting service in to the module using providers in module decorator directly, so what will happen his it will create a new instance , because of this each and every component will use the same instance, everything will be shared across the components

import { Component } from '@angular/core';
import { DataService } from './data.service';

@Component({
    selector:'employee',
    template:`
    <div style="display:inline-block;width:400px;vertical-align:top">
        <h1>Employee </h1>
        <div>
            <input type="text" #emp />
            <button (click)="addEmployee(emp.value)" 
                    class="btn btn-sm btn-primary">Add Emp</button>
            <button (click)="getEmployee()" 
                    class="btn btn-sm btn-primary">Get Employees</button>
            <div>
                <ul class="list-group">
                    <li class="panel list-group-item-primary" 
                        *ngFor="let e of employees" style="list-style:none;">
                        {{e}}
                    </li>
                </ul>
            </div>
        </div>
    </div>
    `
})
export class EmployeeComponent
{
    employees:string[];

    constructor(private dataservice:DataService){
        
    }

    addEmployee(employeeName)
    {
        this.dataservice.addEmployee(employeeName);
    }

    getEmployee(){
        this.employees = this.dataservice.getEmployees();
    }
}


@Component({
    selector:'student',
    template:`
    <div style="display:inline-block;width:400px;vertical-align:top">
        <h1>Student </h1>
        <div>
            <input type="text" #std />
            <button (click)="addStudent(std.value)" 
                    class="btn btn-sm btn-primary">Add Student</button>
            <button (click)="getStudents()" 
                     class="btn btn-sm btn-primary">Get Students</button>
            <div>
                <ul class="list-group">
                    <li class="panel list-group-item-primary" 
                        *ngFor="let s of students" style="list-style:none;">
                        {{s}}
                    </li>
                </ul>
            </div>
        </div>
        </div>
    `
})
export class StudentComponent
{
    students:string[];

    constructor(private dataservice:DataService){
        
    }

    addStudent(studentName)
    {
        this.dataservice.addEmployee(studentName);
    }

    getStudents(){
        this.students = this.dataservice.getEmployees();
    }
}





Module.ts:

In this we can see that the Providers is injected in to module directly to use it across the components.

    import { NgModule } from '@angular/core';
    import { BrowserModule } from '@angular/platform-browser';
    import { FormsModule } from '@angular/forms';
    import { BaseRequestOptions, Http, Headers } from '@angular/http';
    import { webformComponent } from './Component/web.component'  
    import { EmployeeComponent,StudentComponent } from './employee.component'
    import { DataService } from './data.service';

    @NgModule({
        imports: [BrowserModule,FormsModule],
        exports: [],
        declarations: [webformComponent,EmployeeComponent,StudentComponent],
        providers:[DataService],
        bootstrap:[webformComponent]
    })
    export class AppModule { 

    }


For ex:
In above Example we can see that the element add in the Employee reflect in Student



Output:









 







Main Component to Bootstrap:

import { Component,ViewEncapsulation,Input,Output } from '@angular/core';


@Component({
    selector:'webform',
    template:`
    <form>    
       <student></student> 
       <employee></employee>      
    </form>`,
    styles:[`
        
    .input{        
        border: 1px solid #ddd;
        -webkit-box-shadow: inset 0 1px 2px rgba(0,0,0,.07);
        box-shadow: inset 0 1px 2px rgba(0,0,0,.07);
        background-color: #fff;
        color: #32373c;
        outline: 0;
        -webkit-transition: 50ms border-color ease-in-out;
        transition: 50ms border-color ease-in-out;
    }

    `],
    encapsulation:ViewEncapsulation.None
})
export class webformComponent{

    constructor(){

    }

}







index.html:

<body>
    <webform>Loading...</webform>
  </body>






From above post you learn how to inject the service in to the components in Angular2 applications