Merge all excel sheet data into one

Hi Friend,

Lets see how can we merge the data from all the excel sheet into one.  Lets consider we have one
excel file containing 4-5 excel sheet and all the excel sheet have some data and we want to merge
all the data into one excel sheet so that we can use that result excel sheet instead referring all 4-5
sheets.

You may find few Excel add-ins those are paid, So here I came with zero cost solution.  I used the VBA to merge the sheet.  To make faster I applied a condition so it will not copy the row found blank and immediately script will switch to the another sheet to copy.

Consider we have

Sheet 1 sheet1 data1 sheet1 data2 sheet1 data3		Sheet 2 sheet2 data1 sheet2 data2 sheet2 data3
Sheet 3 sheet3 data1 sheet3 data2 sheet3 data3		Sheet 4 sheet4 data1 data1 data1 sheet4 data2 data2 data2 sheet4 data3 data3 data3 sheet4 data4 data4 data4

if we try to merge it we will get a new sheet named as 'Result' which will have the data like -

Result

sheet1	data1
	sheet1		data2
		sheet2	data1
sheet2	data2
sheet2		data3
sheet4	data1	data1	data1
sheet4	data2	data2	data2
sheet4	data3	data3	data3
sheet4	data4	data4	data4

Notice 4th line from sheet 1 not copied because line no 3 is blank similar for other sheets.  Sheet 3 not got copied because first line is empty.


Now lets see the VBA Code open you excel file

1. Press Alt + F8 to open the Macro prompt

2. Provide the macro name in Text Field and click the Create button

3. It will open the editor to write the code.

4. Remove these two lines and paste the following code

Sub Merge()   

    MSG1 = MsgBox("Merge all sheet and create result sheet", vbYesNo, "Mind It")

    If MSG1 = vbYes Then

        'create the first worksheet for the result

        Call CreateNewResultSheet(Sheets)           

        'call the function to append the other sheet data to the first one (named as result)

        Call AppendData(Sheets)

    Else

        MsgBox "Bbye..!!!"

    End If       

End Sub

---

Private Function CreateNewResultSheet(ws As Sheets)

    Dim i As Integer   

    ' removing first sheet if name is result 
    ' this can be old result sheet

    If ws(1).Name = "Result" Then

        Application.DisplayAlerts = False

        ws(1).Delete

        Application.DisplayAlerts = True

    End If

    ' now checking if any data sheet name is result then
    ' rename it append the date and time in name  

    For i = 1 To ws.Count

        If ws(i).Name = "Result" Then

            Dim str As String

            str = "Result" & Format(DateTime.Now, "yyyy-MM-dd hh-mm-ss")

            ws(i).Name = str

        End If

    Next i

    Worksheets.Add Count:=1, Before:=Sheets(1)

    Sheets(1).Name = "Result"

End Function

---

Private Function AppendData(ws As Sheets)

    Dim result As Worksheet

    Dim i As Long    ' sheet count

    Dim j As Long    ' row count in from sheet

    Dim k As Long    ' row count for result sheet

    Set result = ws(1)

    k = 1

   

    For i = 2 To ws.Count

        For j = 1 To ws(i).Rows.Count

            If WorksheetFunction.CountA(ws(i).Rows(j)) <> 0 Then

                ws(i).Rows(j).EntireRow.Copy result.Rows(k)

                k = k + 1

            Else

                Exit For

            End If

        Next j

    Next i

End Function

5.  Save it, remember you can not save permanently macro in .XLS or .XLSX file, to save the macro permanently you have to save the file as '.XLSM' called macro enable excel file.

6.  Now to run the macro again press the ALT+F8, select the macro with the name Merge and click to run the macro.


So can download the sample file from here

Happy Coding.. :)

Assign VS2010 to devenv when we have VS2007 as default

Hi Friends,


We generally face this problem if we have Visual Studio 2007 and as well as Visual Studio 2010.  To
start Visual Studio we use the shortcut command devenv stands for development environment, but as we have installed VS 2007, this devenv commend is assigned to Visual Studio 2007 so it starts the Visual Studio 2007  not 2010.  Even I we installed Visual Studio 2010 after  VS2007 but it do not update the registry to open 2010 with that shortcut command.

To assign the Visual Studio 2010 to that shortcut command devenv you have to modify the registry.  You can go to the following path and change the value by the

"C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"

here this value is the path of your 2010 Visual Studio installed directory.

1.  Open the Run prompt by pressing (Window Key + R)

2. type regedit and hit enter

3. due the some security setting it will prompt you to continue or not  click 'OK'

4. goto the following path
     

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\App Paths\dev.exe

5. In right pane you will find a single REG_SZ entry with the name 'default', double click it.

6. provide the installed directory path in text box (like the path I provided very top)

7.  in previous step this textbox contains the path of VS2007 IDE path you have to overwrite it by the Visual Studio 2010 path.

8. Its done.



Or you can download the registry file directly from here and just double click it and its done..

if you are using Window 64-bit download from Here

if you are using Window 32-bit download from Here

the code of reg file looks like.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\devenv.exe]
@="C:\\Program Files\\Microsoft Visual Studio 10.0\\Common7\\IDE\\devenv.exe"

Happy Tricks.. :)

Run an EXE file as an image without letting you friend know

 Hi Friends,

Several times we want to run the an EXE file in our friend's system without letting him/her
know about it.  Because we can not say them to run the exe file so we always search a way
to do the same without asking them to run an EXE.

  I am here with solution we can run the exe as an image. follow the steps and we are done.

1.  create a new folder and paste you exe inside the folder.

2.  Rename the file as filename.jpeg (so if we click it will not run the exe file as well as friend
    will think its image not an EXE file)

3.  Create a new folder "Password" (folder is empty)

4.  Copy the folder and at the place right click and select the option "Paste shortcut"



5.  Rename the created shortcut as 'Password' from 'Password - shortcut'

6.  Right click this shortcut and select the option 'Properties'

7.  Change the target value to "C:\WINDOWS\system32\cmd.exe /c 1.jpeg"



    remember here is this value 1.jpeg is your EXE file.  replace this 1.jpeg with the name you
   gave to your exe file in Step 2. If this step create the cmd icon you can change the icon by
   clicking the button in properties ( See the above image 'Change Icon' button)

8.  Now you can delete the folder you created in Step 3. Remember delete the folder not the
    shortcut. Its done, now if you give this folder you created in Step 1, to your friend then
    definitely he/she will double click this Password named shortcut and by clicking this it will
    run the exe.

 Happy Windows Tricks.. :)

How to set Icon of Drive

Hi Friends,

Sometime we want to set the icon of drive in my computer or any other removable device.  Today we will see how to set the icon of drives (HD partitions or removable devices)

open notepad and paste the following code

     [autorun]
     icon = .\media.ico

here media.ico is the icon file I want to set to the drive.  '.\media.ico' it shows media.ico are at same place.  Now save file file to the drive in which you want to set the icon as 'autorun.inf' extension.  Just consider the case if you want to set the icon to 'D:\' drive then save this file to 'D:\' drive.  "D:\autorun.inf". Now save the that icon file in 'D:\' drive as 'media.ico' file name we specified in autorun.inf file.

if the drive in which you set the icon file is removable remove it and reinsert it, it will show you the icon.  But if the drive is HD partition you have to restart the system. And you will see the icon at the place of default drive icon.

See the icon of 'I:\' drive (Pendrive).

Happy Tricks.. :)

Chat using the Remote Method Invocation (RMI) in Java

Hi Friends,

  I hope we all are aware with RMI (Remote Method Invocation).  In simple RMI is very powerful concept by Java, by which we can invoke the method of class residing on another machine.

 Just think we have a class A on machine M1, and we want to invoke the method of class A from our machine which is connected to machine M1 by any medium.  Java provide the feature to perform this task.

We can design our chat server using the RMI.  We have a server and several clients. In general in a chat room clients call or invoke a method of server or any how pass the message to server and then server re-transmit the message to particular client.

We can design chat application using the RMI.  You can download executable jar file from here.
First you need to run the Server jar file and click the button to start the server. Once the server started you can run the client. if you run the client on the same machine where you started the server, pass the server name localhost only, and then provide your nick name and you are logged in to chat server.  You can start multiple clients to test it.  There is no need to start the server again and again for each client. only on server will handle all the clients requests.

1.  Run the Server

 

2.  Once the server started run the client

          because server and client running on the same machine you can use the 'localhost' else
          you have to provide the machine name or IP address (priority) and after clicking the OK
          button provide you nick name.

3.  To test the chat server you can start the multiple clients let start one more client with nick
     name 'Gaurav' for this client also we will use the same server name 'localhost' or other
     machine name (or IP).

4.  Once you are done with this you can see the two user in online user box.

if you need any suggestion or help, feel free to contact me.. :)

You can download executable jar file from here.

You can download the source code also from here.. 

Happy Coding.. :) Happy Java.. :)