Excel Visual basic Editor multi line comment and uncomment

How to do multiline comment/uncomment?

Solution:

1. Open Visual Basic Editor
2. Click on View->Toolbars->Customize
3. Select Edit->Drag the Commands scroll bar to find the comment and uncomment icons
4. Select Comment Block/Uncomment Block and drag drop in Edit(can be any) menu
5. Select the multi-line code in editor and click on Comment/Uncomment menu item 

Please send your valuable feedback to psrdotcom@gmail.com

Finding missing records in worksheet w.r.t other worksheet (or) intersection of two worksheets

Description:

We have two worksheets with similar records with same structure)(not in number of records). We want to know missing records in one worksheet with reference to other worksheet (Or) we want to find the intersection of both sheets.

Solution

Workbooks used are Workbook1.xls, Workbook2.xls, Results.xls
Workbook1 is having Src1 worksheet
Workbook2 is having Src2 worksheet
Results.xls is having Dest1 worksheet which will have Src1-Src2 records

Results.xls is having Dest2 worksheet which will have Src2-Src1 records

    Dim Workbook1, Workbook2, Results As Workbook
    Dim Src1, Src2, Dest1, Dest2 As Sheet1
  
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
  
    Windows("Workbook1.xls").Activate
    Set Workbook1= ActiveWorkbook
  
    Windows("Workbook2.xls").Activate
    Set Workbook2= ActiveWorkbook
  
    Windows("Results.xls").Activate
    Set Results = ActiveWorkbook
    
    'Renaming Results workbook sheet1 and sheet2 to Dest1 and Dest2
    Results.Sheets(1).Name = "Dest1"
    Results.Sheets(2).Name = "Dest2"

    Workbook1.Activate
    ActiveWorkbook.Sheets("Src1").Activate
    Set Src1= ActiveSheet
  
    Workbook2.Activate
    ActiveWorkbook.Sheets("Src2").Activate
    Set Src2= ActiveSheet
  
    Results.Activate
    ActiveWorkbook.Sheets("Dest1").Activate
    Set Dest1 = ActiveSheet
    ActiveWorkbook.Sheets("Dest2").Activate
    Set Dest2 = ActiveSheet
   
    Dim Src1rng As Range
    Dim Src2rng As Range
  
    'Select and assign range for Src1 sheet data
    Src1.Activate
    'Selecting range without headers by using offset(1,0)
    Set Src1rng= Range("A1").CurrentRegion.Offset(1, 0)

    'Select and assign range for Src2 sheet data
    Src2.Activate
    Set Src2rng= Range("A1").CurrentRegion.Offset(1, 0)
  
    Dim row_not_exist As Boolean
    'Select each row from the Src1rng
    Src1.Activate
    For Each Src1Row In Src1rng.Rows
        row_not_exist = True
        Src2.Activate
        For Each Src2Row In Src2rng.Rows
            'Comparing cell(s) in Src1rng with cell(s) with Src2rng
            If Src1Row.Cells(1).Value = Src2Row.Cells(1).Value Then
                If Src1Row.Cells(2).Value = Src2Row.Cells(2).Value Then
                            'if record exists in both sheets, no check needed further
                            row_not_exist = False
                            Exit For 'Don't compare with remaining records
                        End If
            End If
        Next Src2Row  'Go to next record in Src2


        'If Src1rng record not exists in Src2rng then 
        'copy the Src1 record into Results workbook Dest1 sheet
        If row_not_exist = True Then
            Src1.Activate
            Src1Row.Cells.Select
            Selection.Copy
            Dest1.Activate
            ActiveCell.Offset(1, 0).Select
            Dest1.Paste
            Application.CutCopyMode = xlCopy
        End If
    Next Src1Row  'Go to next record in Src1
    
    'we can find the viceversa by just changing the names from Src1 to Src2
  
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

Sample Output:

Src1 Sheet data	Src2 Sheet data
Names Required ABC  DEF XYZ  WUV PSR  DOT	Names Required ABC  DEF GHI  LKV PSR  DOT
Dest1 Sheet	Dest2 Sheet
Names Required XYZ  WUV	Names Required GHI  LKV

Note:
Dest1 contains the missing(intersection) data, which available in Src1 but not in Src2
Dest2 contains the missing(intersection) data, which available in Src2 but not in Src1

Please let me know if any further queries, contact me psrdotcom@gmail.com 

PL/SQL Developer Useful Settings

Hi friends,

I am using Pl/Sql Developer IDE to connect different databases and performing different database operations at the same time. Its been proved that Pl/Sql developer is very useful tool for sql and/or plsql statement/query execution.

What I liked in Pl/Sql Developer are

1. Select some part of the query and execute the selected part in the query. I liked it very much.
2. Export output result to excel as a table
3. Code assistant which will display all the tables in that schema when you entered your_schema_name.
4. Working in different windows like SQL window, Test window, Program window, Command window and Diagram window at the same time
5. Macros which can do certain action once recorded

What I have learn today is

i) Logon History

Are you able to remember all the username and passwords of different databases?
Fed up of typing username and password everytime when logging?


Use "Fixed Users" option in Logon History Preferences

We can create different menus to differentiate users or type of logons

Syntax:
>Menu
username/password@database

Example:
>Test
abc/abc123@xyz
def/def234@psr

Note: 

• More than one users under the same menu should be in next line

• Avoid @ usage in password because database delimeter is @ so it will treat first @ onwards as database

Example: ghi/lkj@123@qwe
Here

username: ghi

password: lkj@123

database: qwe

but
pl/sql developer will treat

username: ghi

password: lkj

database: 123@qwe

Because first @ onwards it will treat as database.
We can have many menus with many logon to connect different databases with very less effort.
Click the Logon Button Arrow and select the menu->username/password@database
It will get connected automatically without typing username and password

Very Useful .. Try it

ii) Auto Replace facility

Open Pl/Sql Developer->Click on Tools->Preferences->Select Editor Preferences

Scroll down to AutoReplace tab and use the syntax and example to use the facility

syntax: shortcutword=full(part)command

example: saf = select * from 

When you type saf and space then you can see select * from as a replacement of saf

Excel VBA: Multi-selection Listbox using pop-up window to display column values

Description:

We have following excel sheet with the two columns Names and Requirednames.
Names column has all the names
Required Names column should have some selected name(s)

Names	Required Names
ABC,DEF,GHI
PSR,DOT,COM

What we can do other than copy and paste?

We can create a pop-up window which will display all the names by using list box and we can select the required names from that list box.

Procedure:

1.	Go to Developer Tab -> Design Mode
2.	Click on Insert and click -> CommandButton
3.	Drag the cursor to create a button
4.	Rename button with name "Show Names" in sheet
5.	Assign a macro to it with name "DisplayNames"
6.	Open VBEditor
7.	Goto Insert -> User Form
8.	We will get one form, Design the form as follows
9.	UserForm1
10.	Change Caption to "Names Selection" in Form Properties
11.	Change Caption to "List of Names" in Frame Properties
12.	Change Caption to "Select the names and click OK" in Lable Properties
13.	Change Name to "lbEmp", MultiSelect to 1-fmMultiSelectMulti in ListBox Properties
14.	Change Name to "submit" and Caption to "OK" in Button1 Properties
15.	Change Name to "close" and Caption to "Close" in Button2 Properties
16.	Change Name to "reset" and Caption to "Reset" in Button3 Properties
17.	Select Excel window, Go to Macros and Select "DisplayNames" macro(which is already assigned to the Button in Sheet), Click on Edit
18.	Write the following code: Dim rngEmp() As String Dim emps As String Dim i As Integer Application.Sheets("Sheet1").Select emps = ActiveCell.Offset(0, -1).Value If emps = "" Then MsgBox ("No Name(s) Available") End Else rngEmp = Split(emps, ",") End If For i = LBound(rngEmp) To UBound(rngEmp) UserForm1.lbEmp.AddItem rngEmp(i) Next UserForm1.Show
19.	Double-click on the UserForm1 from the Forms directory in VBEditor
20.	Double-click on any one of the button on the form to view code(shortcut: F7)
21.	Select all the code and delete it. Paste the following code Private Sub close_Click() Unload UserForm1 End Sub Private Sub reset_Click() UserForm1.lbEmp.Clear End Sub Private Sub submit_Click() Dim emps As String emps = "" For i = 0 To UserForm1.lbEmp.ListCount - 1 If lbEmp.Selected(i) = True Then 'MsgBox lbEmp.List(i) If emps = "" Then emps = lbEmp.List(i) ElseIf emps <> "" Then emps = emps & "," & lbEmp.List(i) End If End If Next i ActiveCell.Value = emps End Sub
21.	Save and Close the VBEditor. Goto Excel sheet
22.	Select the right adjacent Cell say B1 Cell
23.	Click the button which we created on the sheet
24.	Select the name(s) and Click Ok to see values in the selected cell
25.	We can select Multiple Names at a time which will display names with the delimiter "," in the selected cell
26.	If A's cell doesn't contain any value then it will display a message "No Name(s) Available"
27.	We have to select Close to close the pop-up window
28.	We can select Clear to clear list and then select close to cancel

You can see the sample output

Names	Required Names
ABC,DEF,GHI	ABC
PSR,DOT,COM	PSR,COM

Note:

For any further queries and/or suggestions please contact psrdotcom@gmail.com

Bangalore to Tirupathi Weekend Travel for Lord Balaji darshan

Myself and my friend(Arya Kumar Mohanthy) started in Bangalore at around 10:30pm by KSRTC Volvo. It was nice journey and we reached Eadu Kondalu(Tirupathi) Bus Station at around 4:15am.

We stud in line for dharma dharshan Rs. 50/- ticket at bus station. We got ticket around 6:15AM. Then we went to Alapiri from where we started climbing all the 7 hills around 3550 steps. we had been reached there with in 3 hours.

We stud in line for dharshan and with god's grace we had very good dharshan with in 3 hours.

We took bus to Tirupathi and from there to Bangalore. We reached Bangalore at around 11:00PM.

Luckily Both reached home safely.

Thanks GOD

Learning Basics in Windows Scripting

I am trying to learn some basics in windows scripting.

What I am thinking like, If you are doing some similar work in some application in different times, Why can't we make it automated?

Ex:
1) For some mail, we know that, we will send the same content whenever it comes from some particular id. Can't we automate it?
2) We are running some java application and i will do some similar procedure every time, can't we automate the process?

Currently I am learning Windows Scripting.


References:
http://technet.microsoft.com/en-us/scriptcenter/dd940112.aspx

Please post comments if you have any idea