Monday, March 19, 2018

Excel Opertions in VBS

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

'Create a new workbook.

'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"

' 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
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

'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

'Quit Excel

'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

'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

'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


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

'close the workbook

'exit the excel program

'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)

'Set objRange = objWorkSheet.Range("A1").EntireColumn
'Set objRange = objWorkSheet.Range("B1").EntireColumn
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", "")
End If


Set objWorksheet2 = objWorkbook2.Worksheets(1)

'Set finalcolumn  =  1 + objWorksheet2.Cells(1, 1).End(-4161).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")


'close the workbook

'exit the excel program

Set objExcel = Nothing
Set objWorkbook2 = Nothing

Friday, August 17, 2012

Speech Bubble Component in flex:
package {
    import flash.display.Graphics;
    import mx.skins.RectangularBorder;   
    public class CustomContainerBorderSkin extends RectangularBorder {
        private var dropShadow:RectangularDropShadow;
        // Constructor.
        public function CustomContainerBorderSkin() {
        override protected function updateDisplayList(unscaledWidth:Number,
            super.updateDisplayList(unscaledWidth, unscaledHeight);
            var cornerRadius:Number = getStyle("cornerRadius");
            var backgroundColor:int = getStyle("backgroundColor");
            var backgroundAlpha:Number = getStyle("backgroundAlpha");
            // 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.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=""
               xmlns:mx="library://" minWidth="955" minHeight="600">
        <!-- Place non-visual elements (e.g., services, value objects) here -->
    <mx:VBox height="100%" width="100%" verticalAlign="middle" horizontalAlign="center">   
    <mx:VBox id="vb1"
             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 horizontalAlign="center" label="Password" styleName="loginFormItem">
                <mx:TextInput id="tipPassword" maxChars="8" fontWeight="normal" displayAsPassword="true" height="29" width="180"/>
            <mx:FormItem direction="horizontal" x="71" y="59" horizontalGap="5">
                <mx:Button id="btnLogin" label="Login" />
                <mx:Button id="btnCancel" paddingLeft="5" label="Cancel"/>


Tuesday, January 4, 2011

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 = "")]
[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

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

        //Uncomment the following line if using designed components

    public string HelloWorld() {
        return "Hello World";
    public List<Doctor> getDoctorDetails()
        var doclist = new List<Doctor>();
        Doctor doc;
        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()
        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

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

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