Fun with Excel

Delete Single or Multiple Worksheet In an Excel Workbook - Tutorial 4

noreply@blogger.com (Sri Sri) — Fri, 07 Feb 2014 19:48:00 +0000

Manually

(a) Delete a single worksheet

By default when you open a new excel workbook, three worksheets - Sheet1, Sheet2 and Sheet 3 are displayed. If there is a need to delete one of these worksheets, right-click the worksheet's tab (at the bottom where you see the worksheet name as Sheet1, Sheet2 or Sheet3 etc.,.) and either press d or click on delete.

(b) Delete multiple worksheets

If the desire is to delete many worksheets following the method in point (a) would be a tedious process. So, follow one of the below methods to do the work easily:

Deleting sequential multiple worksheets

If the need is to delete worksheets that are in one after another like Sheet1, Sheet2, etc.,. click on sheet 1 tab, hold SHIFT key and then click on the last worksheet you want to delete (say sheet 10). This would highlight all the 10 sheets, now right click and either press d or click on delete. If there is any content in the worksheet, a warning will be displayed for this operation because it cannot be undone, so proceed carefully if you have created some complicated or important worksheets.

Deleting random multiple worksheets

Above method of 'Deleting sequential multiple worksheets' can be followed for this but instead of holding SHIFT key, hold the CTRL key while selecting the random worksheets for deletion.

Automatically using VBA Code or Macro

(a) vbscript or macro code to delete a single worksheet

Set newExcel = CreateObject("Excel.Application")

newExcel.Visible = True

Set newWorkbook = newExcel.Workbooks.Add()

newExcel.DisplayAlerts = False
newWorkbook.Sheets("Sheet1").Delete
newExcel.DisplayAlerts = True

(b) vbscript or macro code to delete multiple worksheets with name starting with 'test'

Dim InSheet
Set newExcel = CreateObject("Excel.Application")
Set newWorkbook = newExcel.Workbooks.Open("C:\Documents and Settings\KOTLAS02\Desktop\testing.xls")

newExcel.Application.Visible = True

newExcel.DisplayAlerts = False
For Each InSheet In newExcel.ActiveWorkBook.Worksheets
If LCase(Left(InSheet.Name, 4)) = "test" Then
InSheet.Delete
End If
Next

newExcel.DisplayAlerts = True

Create A New Worksheet In an Excel Workbook - Tutorial 3

noreply@blogger.com (Sri Sri) — Tue, 27 Aug 2013 21:26:00 +0000

Manually

(a) Add a worksheet at the end

By default when you open a new excel workbook three worksheets - Sheet1, Sheet2 and Sheet 3 are displayed. If there is a need to create more worksheets hover on the 4th tab (last icon tab with empty sheet name and an asterisk mark) and it will show "Insert Worksheet". Clicking on this would create a new worksheet with default name as 'Sheet4'. Likewise more excel worksheets can be added at the end of the existing worksheets by clicking on the last empty sheet name or icon tab.

(b) Add a worksheet before a particular sheet

To add a new worksheet directly before some tab, right-click that tab and either press 'i' or click 'Insert' in the pop-up menu and then click OK with the Worksheet icon highlighted.

(OR)

To add a new worksheet directly before some tab, highlight that tab and use the command Shift + F11 to insert a new worksheet before that tab.

Automatically using VBA Code or Macro

(a) Code to add worksheets at the beginning

Set newExcel = CreateObject("Excel.Application")

newExcel.Visible = True

Set newWorkbook = newExcel.Workbooks.Add()
Set newSheets = newWorkbook.Sheets

newSheets.Add ,,2

Note: Third parameter (here '2') tells the number of new worksheets that needs to be added.

(b) Code to add worksheets at the end or after default end sheet 'Sheet3'

Set newExcel = CreateObject("Excel.Application")

newExcel.Visible = True

Set newWorkbook = newExcel.Workbooks.Add()
Set newWorksheet = newWorkbook.Worksheets(3) 'default 3rd sheet is the last worksheet in a new worksheet
Set newSheets = newWorkbook.Sheets

newSheets.Add ,newWorksheet,5 'Adds 5 worksheets after Sheet3

(c) Code to add worksheets before a particular sheet

Set newExcel = CreateObject("Excel.Application")

newExcel.Visible = True

Set newWorkbook = newExcel.Workbooks.Add()
Set newWorksheet = newWorkbook.Worksheets(2)
Set newSheets = newWorkbook.Sheets

newSheets.Add newWorksheet,,4 'Adds 4 worksheets before Sheet2

Tip: For testing the above code, just paste the code in a notepad, save it as a vbs in a known location and double click on it.

Know How To Close or Terminate Excel - Tutorial 2

noreply@blogger.com (Sri Sri) — Tue, 13 Aug 2013 14:15:00 +0000

There are many ways to close or terminate or stop an excel that is open. Below are some of the methods that will be useful.

Method 1:

Clicking the X icon, the rightmost icon on the title bar

Method 2:

Clicking the Office (Excel) Button at the top left corner and selecting the Close icon.

Method 3:

Clicking the File button and the Exit Excel command at the bottom.

Method 4:

Using the keyboard command ALT + F4.

Method 5:

Start Task Manager

a) Applications - Highlight the excel that needs to be closed and click on "End Task".
b) Applications - Highlight the excel that needs to be closed, right click, select "Go to Process" and "End Process".

Note: If any changes are made to excel and then terminating it in any way, will prompt you whether or not to save your changes. If the work is not saved already then there are three options:
a) Save and close,
b) Don't save and close
c) Cancel the close / exit / termination command itself and return to excel to proceed further with the changes.

Method 6:

If you want to close and excel using macro script, use .Quit command. Find below a small sample vbs script that opens a new excel and closes it.

Set newExcel = CreateObject("Excel.Application")

newExcel.Visible = True
newExcel.Workbooks.Add

newExcel.Quit

Know How To Start Excel - Excel Tutorial 1

noreply@blogger.com (Sri Sri) — Wed, 07 Aug 2013 15:58:00 +0000

There are many ways to open a new excel workbook. Below are some of the methods that will be useful assuming Microsoft Office is installed

Method 1

Click Start | All Programs | Microsoft Office | Microsoft Excel

Method 2

Click Start | Run | Excel

Method 3

Right Click Start | Open Windows Explorer | Go to \Program Files (or) Program Files (x86)\Microsoft Office\Office14 | Excel.exe

Also,

The typical location of the Excel.exe file depends on what version you have installed.

Microsoft Excel 2000

C:\Program Files\Microsoft Office\Office

Microsoft Excel 2002

C:\Program Files\Microsoft Office\Office10

Microsoft Office Excel 2003

C:\Program Files\Microsoft Office\Office11

Microsoft Office Excel 2007

C:\Program Files\Microsoft Office\Office12

Microsoft Office Excel 2010

C:\Program Files\Microsoft Office\Office14

Tip 1: Store a shortcut of excel.exe on the desktop and double click to start the excel.
Tip 2: If excel.exe is not found in the above locations then follow the below procedure to find the excel.exe:
Click Start - Search - Files or Folders - search for 'Excel.exe' - Look in 'My Computer'.

Method 4

To start excel in safe mode (generally used when excel program is corrupted)

Click Start | Run | "C:\Program Files\Microsoft Office\Office14\Excel.exe" /s

Note: Path changes based on the version of the excel as mentioned in Method 3.

Method 5

To start excel without running automatic macros, hold down SHIFT while you start Excel.

Method 6:

If you want to open Excel using excel macro script, then follow the below procedure:

a) Open a notepad.
b) Paste the below code

Set newExcel = CreateObject("Excel.Application")

newExcel.Visible = True
newExcel.Workbooks.Add

c) Save the file as "ExcelStart.vbs" in a known location or desktop.
d) Double click vbs file to open new excel.

Tips to recover a corrupt or damaged excel file

noreply@blogger.com (Sri Sri) — Fri, 19 Jul 2013 10:47:00 +0000

Sometimes you find excel files which are damaged or in unrecognized format, specially this happens with annual templates which are opened rarely, like once in a year. But if these files are important and if the data needs to be recovered below are some tips that can tried (in the order of priority) for free before using any licensed or expensive repair software.

Try opening the excel file couple of times and see if it opens properly.
Check if there is a back up file from where the data can be restored. Probable place of back up could be: internal or external hard drive, e-mail, cloud storage etc.,.
Open a new excel file - On the File menu, click Open - In the Open dialog box, select the file you want to open and click the arrow next to the Open button - Click Open and Repair, and then choose which method you want to use to recover your workbook - Excel will try repairing the file. This works best with the latest versions of excel.
Disable the VBA and Add-ins by opening excel in safe mode. Simple method would be Press and hold the [Shift] key when you open the file to keep any macros from running when the file opens (or) refer to the link on how to open office files in safe mode.
Try to open it in Textpad, WordPad, Notepad, MS Word, Open Office or Libre Office. See if this works and the data can be recovered. This can also be tried by copying the excel file and changing the extension to *.doc, *.txt etc.,.
If none of the above 5 steps work then delete the contents in c:\windows\temp, restart the computer, run virus scan and try repeating all the above five steps once again.