Excel Opertions in VBS

Create Excelsheet
===============
'Bind to the Excel object
Set objExcel = CreateObject("Excel.Application")

'Create a new workbook.
objExcel.Workbooks.Add

'Select the first sheet
Sheet = 1

'Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(Sheet)

'Name the worksheet
objSheet.Name = "VBS_Excel_Example"

'Set the save location
strExcelPath = "C:\Users\Administrator\Desktop\Excel POC\generated excel\Vbs_Excel_Example.xlsx"
'--------------------------------------------------------
'Populate the worksheet with data
'--------------------------------------------------------
'   objSheet.Cells(row, column).Value = "Whatever"

'Add some titles to row 1
objSheet.Cells(1, 1).Value = "Name" 'Row 1 Column 1 (A)
objSheet.Cells(1, 2).Value = "Description" 'Row 1 Column 2 (B)
objSheet.Cells(1, 3).Value = "Something Else" 'Row 1 Column 3 (C)

'Add some data using a loop
For row = 2 to 10
objSheet.Cells(row, 1).Value = "Item " & row & " Name"
objSheet.Cells(row, 2).Value = "Item " & row & " Description"
objSheet.Cells(row, 3).Value = "Item " & row & " Something Else"
Next

'--------------------------------------------------------
' Format the spreadsheet
'--------------------------------------------------------

'Put the first row in bold
objSheet.Range("A1:C1").Font.Bold = True

'Change the font size of the first row to 14
objSheet.Range("A1:C1").Font.Size = 14

'Freeze the panes
objSheet.Range("A2").Select
objExcel.ActiveWindow.FreezePanes = True

'Change column A and B to use a fixed width
objExcel.Columns(1).ColumnWidth = 20
objExcel.Columns(2).ColumnWidth = 30

'Change column C to autofit
objExcel.Columns(3).AutoFit()

'Change the background colour of column A to a light yellow
objExcel.Columns(1).Interior.ColorIndex = 36

'Change the font colour of column C to blue
objExcel.Columns(3).Font.ColorIndex = 5


'--------------------------------------------------------
' Save the spreadsheet and close the workbook
'--------------------------------------------------------
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

'Quit Excel
objExcel.Application.Quit

'Clean Up
Set objSheet = Nothing
Set objExcel = Nothing

Update Excelsheet
==================
Const xlShiftToRight = -4161
Const xlup = -4162
'create the excel object
Set objExcel = CreateObject("Excel.Application")

'view the excel program and file, set to false to hide the whole process
objExcel.Visible = True

'open an excel file (make sure to change the location) .xls for 2003 or earlier
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Administrator\Desktop\Excel POC\generated excel\Vbs_Excel_Example.xlsx")

Set objRange = objExcel.Range("A1").EntireColumn
objRange.Insert(xlShiftToRight)

'Add some titles to row 1
objExcel.Cells(1, 1).Value = "Concatenated Column" 'Row 1 Column 1 (A)

For i = 2 To objExcel.Cells(objExcel.Rows.Count, "B").End(xlup).Row
    objExcel.Cells(i, "A").Value = objExcel.Cells(i, "B").Value & "_" & objExcel.Cells(i, "D").Value
Next

'Put the first row in bold
objExcel.Range("A1:C1").Font.Bold = True

'Change the font size of the first row to 14
objExcel.Range("A1:C1").Font.Size = 14

objExcel.Columns(1).AutoFit()

'save the existing excel file. use SaveAs to save it as something else
objWorkbook.Save

'close the workbook
objWorkbook.Close

'exit the excel program
objExcel.Quit

'release objects
Set objExcel = Nothing
Set objWorkbook = Nothing

Copy Data from one excel to another
==============================
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\HP\Desktop\Excelsheets\Vbs_Excel_Example.xlsx")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\Users\HP\Desktop\Excelsheets\Vbs_Excel_Example1.xlsx")

Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

'Set objRange = objWorkSheet.Range("A1").EntireColumn
'Set objRange = objWorkSheet.Range("B1").EntireColumn
'objRange.Copy
Set aCell = objWorksheet.Rows(1).Find("Subnet Name",,,1)

If Not aCell Is Nothing Then
       'MsgBox "Value Found in Cell " & aCell.Address & _
        '" and the Cell Column Number is " & aCell.Column
Dim colLetter = Replace(objWorkSheet.Cells(1, aCell.Column).Address(False, False), "1", "")
MsgBox(colLetter)
objWorkSheet.Range(colLetter).EntireColumn.Copy
End If

'objWorkSheet.Range("C:D").EntireColumn.Copy


Set objWorksheet2 = objWorkbook2.Worksheets(1)
objWorksheet.Activate

'Set finalcolumn  =  1 + objWorksheet2.Cells(1, 1).End(-4161).Column
'MsgBox(objWorksheet2.UsedRange.Columns(objWorksheet2.UsedRange.Columns.Count).Column) 
'To Find Last Column Letter
'MsgBox Replace(objWorksheet2.Cells(1, objWorksheet2.UsedRange.Columns(objWorksheet2.UsedRange.Columns.Count).Column+1).Address(False, False), "1", "")

objWorkSheet2.Paste objWorkSheet2.Range(Replace(objWorksheet2.Cells(1, objWorksheet2.UsedRange.Columns(objWorksheet2.UsedRange.Columns.Count).Column+1).Address(False, False), "1", "")&"1")

objWorkbook2.Save

'close the workbook
objWorkbook2.Close

'exit the excel program
objExcel.Quit

Set objExcel = Nothing
Set objWorkbook2 = Nothing

Speech Bubble Component in flex:

CustomContainerBorderSkin.as
-------------------------------------
package {
    import flash.display.Graphics;
    import mx.graphics.RectangularDropShadow;
    import mx.skins.RectangularBorder;   
    public class CustomContainerBorderSkin extends RectangularBorder {
       
        private var dropShadow:RectangularDropShadow;
       
        // Constructor.
        public function CustomContainerBorderSkin() {
        }
       
        override protected function updateDisplayList(unscaledWidth:Number,
                                                      unscaledHeight:Number):void
        {
           
            super.updateDisplayList(unscaledWidth, unscaledHeight);
           
            var cornerRadius:Number = getStyle("cornerRadius");
            var backgroundColor:int = getStyle("backgroundColor");
            var backgroundAlpha:Number = getStyle("backgroundAlpha");
            graphics.clear();
           
            // Background
            drawRoundRect(0, 0, unscaledWidth, unscaledHeight,
                {tl: cornerRadius, tr:cornerRadius, bl: cornerRadius, br: cornerRadius},
                backgroundColor, backgroundAlpha);
           
            // Shadow
            if (!dropShadow)
                dropShadow = new RectangularDropShadow();
           
            dropShadow.distance = 8;
            dropShadow.angle = 45;
            dropShadow.color = 0;
            dropShadow.alpha = 0.4;
            dropShadow.tlRadius = cornerRadius;
            dropShadow.trRadius = cornerRadius;
            dropShadow.blRadius = cornerRadius;
            dropShadow.brRadius = cornerRadius;
            dropShadow.drawShadow(graphics, 0, 0, unscaledWidth, unscaledHeight);           
            graphics.beginFill(0xCCCC99,0.8);
            graphics.moveTo(this.x-80, this.height/2+45);
            graphics.lineTo(this.x, this.height/2-20);           
            graphics.lineTo(this.x, this.height/2+20);
           
        }
    }
}

Usage of the above component:
----------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<s:Application xmlns:fx="http://ns.adobe.com/mxml/2009"
               xmlns:s="library://ns.adobe.com/flex/spark"
               xmlns:mx="library://ns.adobe.com/flex/mx" minWidth="955" minHeight="600">
    <fx:Declarations>
        <!-- Place non-visual elements (e.g., services, value objects) here -->
    </fx:Declarations>
    <mx:VBox height="100%" width="100%" verticalAlign="middle" horizontalAlign="center">   
    <mx:VBox id="vb1"
             borderSkin="CustomContainerBorderSkin"
             backgroundColor="0xCCCC99"
             backgroundAlpha="0.8"
             cornerRadius="70"
             paddingLeft="20"
             paddingTop="20"
             paddingRight="20"
             paddingBottom="20"
             verticalAlign="middle" horizontalAlign="center"
             >       
        <mx:Label text="Speech Bubble" fontSize="24" fontWeight="bold"/>
        <mx:Form horizontalCenter="0" verticalCenter="0" verticalGap="5" horizontalGap="5">       

            <mx:FormItem horizontalAlign="center" label="User Name" styleName="loginFormItem">
                <mx:TextInput id="tipUsername" fontWeight="normal" width="180"  height="29" maxChars="15"/>
            </mx:FormItem>
            <mx:FormItem horizontalAlign="center" label="Password" styleName="loginFormItem">
                <mx:TextInput id="tipPassword" maxChars="8" fontWeight="normal" displayAsPassword="true" height="29" width="180"/>
            </mx:FormItem>
            <mx:FormItem direction="horizontal" x="71" y="59" horizontalGap="5">
                <mx:Button id="btnLogin" label="Login" />
                <mx:Button id="btnCancel" paddingLeft="5" label="Cancel"/>
            </mx:FormItem>
        </mx:Form>       
    </mx:VBox>
    </mx:VBox>
</s:Application>

Output:
---------------

How to Create a sample ASP.NET Web Service and retrieve values from the database :

Step 1:

Create a database table by name

doctormaster(DoctorID,Doctor_Name,Specialist,Gender,Phone as columns)in MSSQL
Server database with some data.
Open Microsoft Visual Studio 2008--->>>File--->>> New--->>>
Web Site--->>>Select ASP.NET Web Service--->>>
Choose language to "visual c#"

Step 2:

In the Service.cs File--->>>
Copy paste the given code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX,

uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
    SqlConnection con = new SqlConnection("Data

Source=VIJAYVIGNESH\\SQLEXPRESS2008;Initial Catalog=hospital1;Integrated

Security=True");
    SqlCommand cmd = new SqlCommand();
    SqlDataReader dr;
    public Service () {

        //Uncomment the following line if using designed components
        //InitializeComponent();
    }

    [WebMethod]
    public string HelloWorld() {
        return "Hello World";
    }
    [WebMethod]
    public List<Doctor> getDoctorDetails()
    {
        var doclist = new List<Doctor>();
        Doctor doc;
        con.Open();
        cmd.Connection = con;
        cmd.CommandText = "SELECT * from doctormaster";
        dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            doc = new Doctor
            {
                DoctorID = dr["DoctorID"].ToString(),
                Doctor_Name = dr["Doctor_Name"].ToString(),
                Specialist = dr["Specialist"].ToString(),
                Gender = dr["Gender"].ToString(),
                Phone = dr["Phone"].ToString()
            };
            doclist.Add(doc);
        }
        return doclist;
    }
   
}
public class Doctor
{
    public string DoctorID = string.Empty;
    public string Doctor_Name = string.Empty;
    public string Specialist = string.Empty;
    public string Gender = string.Empty;
    public string Phone = string.Empty;
}

thats it...!!!!Now you can run the program and click the "getDoctorDetails" Web
Method link in the browser--->>> press invoke.
Output will be in XML Format.Now you can use this web service in any front end
applications.

[Note: Make Your Own Connection String Instead Of "Data

Source=VIJAYVIGNESH\\SQLEXPRESS2008;Initial Catalog=hospital1;Integrated

Security=True"]