Importing A Text File Into A Worksheet

Category: General | [Item URL]

Excel doesn't provide any obvious way to insert the contents of a text file into a worksheet. Until Excel 2000, all versions of Excel required you to open the text file separately, then copy and paste the data to your worksheet.

Excel 2000 (and later) makes the process much easier. You might expect to see this feature on the File menu, but instead you select Data, Get External Data, Import Text File. This command calls up a dialog box in which you select a text file, whereupon the Text Import Wizard lets you specify how Excel should import the data. Finally, in the Import Data dialog box, you indicate the desired worksheet destination.

Excel imports the text file as a database query, which you can update if the data in the text file changes (select Data, Refresh Data to do so). To import the file as static data, click Properties in the Import Data dialog, and remove the check mark from the "Save query definition" box.


Protecting Cells, Sheets, Workbooks, And Files

Category: General | [Item URL]

Excel offers many ways to protect your data. But understanding the differences between the methods and the limitations of each of them can be daunting. Here's a quick review of four protection options in Excel 97 and later, plus explanations of how to use them.

Note: These protection features aren't foolproof. Password-cracking utilities exist, and anyone who wants to defeat your protection badly enough probably can.

Cell protection

Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when the cell is selected. By default, every cell is locked and not hidden. Locked and hidden attributes have no effect unless the worksheet is protected.

To change these attributes, select the appropriate cell or a range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden. Unlock cells that accept user input, and lock formula cells and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells--the results of the formulas will be visible, but the formulas will not.

To protect a sheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog, and make sure Contents is checked. You can enter a password to prevent others from unprotecting the sheet. Locked cells in a protected sheet cannot be edited, and other worksheet changes are disabled. For example, no one can insert rows or columns, change column width, or create embedded charts.

Note: Excel 2002 offers additional options regarding what the user can and cannot do when a sheet is protected. For example, you can protect a sheet and still allow sorting and autofiltering.

Workbook window protection

The Tools, Protection, Protect Workbook command enables you to protect a workbook so no one can add or delete sheets or resize or move the workbook window.

Workbook file protection

Here's how to save a workbook in such a way that you must supply a password to open or modify it:

  • In Excel 97, select File, Save As to display the Save As dialog box; then click Options in that box to call up the Save Options dialog.
  • In Excel 2000 and later, select Tools, General Options when the Save As dialog box appears. If you enter a password in the "Password to open" field, the user must provide the correct password to open the file. If you enter a password in the "Password to modify" field, the user must give the right password to make changes to the file. Without the correct password, the file opens in read-only mode.

Note: Excel 2002 and later also lets you specify the type of encryption, if any, that will be used. Access this feature by clicking the Advanced button in the Save Options dialog box.

VBA project protection

If your workbook contains VBA macros, you can use these to prohibit others from viewing or modifying them. Press Alt-F11 to activate the VB Editor window, and select your project from the Project window. Choose Tools, VBA Project Properties. Click the Protection tab, place a check mark next to "Lock project for viewing," and enter the password twice. Click OK and save your workbook.



Changing The Number Of Sheets In A New Workbook

Category: General | [Item URL]

By default, each new Excel workbook begins life with three worksheets. You can, of course, add more sheets to the workbook or delete sheets you don't need. The unused sheets don't occupy additional memory or increase file size, but I generally don't like them in my workbooks.

A better approach is to change the default. Select Tools, Options and click General in the Options dialog box. Then change the setting for "Sheets in new workbook". Now all new workbooks will have the number of sheets you specified.

I recommend that you change this setting to 1.


Restrict Cursor Movement To Unprotected Cells

Category: General / General VBA | [Item URL]

Q. The formulas in my worksheet use values in several input cells. I've unlocked the input cells and protected the sheet so the user can't change the formulas. Can I set things up so the cell cursor moves only to the input cells?

Yes. You've already unlocked your input cells and ensured that all other cells are locked. By default all cells are locked, but you can change that by using the Protection tab of the Format Cells dialog box. Select the cells to be changed and choose Format, Cells. In this case, the input cells are unlocked and all other cells are locked.

Protect the worksheet in Excel 97 by using Worksheet, Protect or Tools, Protection, Protect Sheet (you can specify a password to keep others from "unprotecting" the sheet). Once the sheet is protected, press Tab to move the cell pointer to the next unlocked cell.

This does not prevent the user from selecting unlocked cells using the cursor keys. To make those cells unselectable, change the worksheet's EnableSelection property. Select View, Toolbars, Control Toolbox to display the Control Toolbox toolbar. Click the Properties button to display the Properties box for the worksheet, then click the cell labeled "xlNoRestrictions" and use the drop-down list to change the EnableSelection property to xlUnlockedCells. Close the Properties box. As long as the worksheet is protected, users cannot select the locked cells on the worksheet.

This procedure does not save the EnableSelection property setting with the workbook. To create a simple macro that turns this setting on when the workbook is opened, press Alt-F11 to activate the Visual Basic Editor. Locate your workbook name in the Project window, and double-click it to expand its listing. Then double-click the item labeled ThisWorkbook and enter the following VBA code:

Private Sub Workbook_Open()
  Worksheets("Sheet1").EnableSelection = xlUnlockedCells
End Sub

This macro executes whenever the workbook is opened, and sets the EnableSelection property of Sheet1 to xlUnlockedCells. The technique can be circumvented by changing the EnableSelection property to its default value (xlNoRestrictions). Few users know about this dodge, however.



Change The Color Of Worksheet Tabs

Category: General | [Item URL]

Q. Can I change the color of the worksheet tabs in my workbook?

Many users find it helpful to color-code the sheet tabs to make it easier to identify specific sheets. If you use Excel 2002 or later, right-click on the sheet tab, choose Tab Color, and then select the color for the tab.

If you're using a previous version of Excel, you cannot change the tab colors.


Making An Exact Copy Of A Range Of Formulas

Category: General / Formulas / General VBA | [Item URL]

Assume that A1:D10 on Sheet1 has a range of cells that contain formulas. Furthermore, assume that you want to make an exact copy of these formulas, beginning in cell A11 on Sheet1.  By "exact," I mean a perfect replica -- the original cell references should not change.

If the formulas contain only absolute cell references, it's a piece of cake. Just use the standard copy/paste commands. But if the formulas contain relative or mixed references, the standard copy/paste technique won't work because the relative and mixed references will be adjusted when the range is pasted.

If you're a VBA programmer, you can simply execute the following code:

With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Following are step-by-step instructions to accomplish this task without using VBA (contributed by Bob Umlas):

  1. Select the source range (A1:D10 in this example).
  2. Group the source sheet with another empty sheet (say Sheet2). To do this, press Ctrl while you click the sheet tab for Sheet2
  3. Select Edit - Fill - Across worksheets (choose the All option in the dialog box).
  4. Ungroup the sheets (click the sheet tab for Sheet2)
  5. In Sheet2, the copied range will be selected. Choose Edit - Cut.
  6. Activate cell A11 (in Sheet2) and press Enter to paste the cut cells. A11.D20 will be selected.
  7. Re-group the sheets. Press Ctl and click the sheet tab for Sheet1
  8. Once again, use Edit - Fill - Across worksheets.
  9. Activate Sheet1, and you'll find that A11:D20 contains an exact replica of the formulas in A1:D10.

Note: For another method of performing this task, see Making An Exact Copy Of A Range Of Formulas, Take 2.



Creating A Database Table From A Summary Table

Category: General | [Item URL]

Many users are familiar with Excel's pivot table feature, which creates a summary table from a database table. But what if you want to perform the opposite operation? This document describes how to create a database table from a simple two-variable summary table.

The worksheet below demonstrates. Range A1:E13 contains the original summary table, and columns G:I shows a 48-row database table derived from the summary table.

How to do it

The solution to creating this "reverse pivot table" is to use a pivot table! The steps below are specific to the example data shown, so you'll need to modify them slightly to work with your data.

Part 1: Creating a pivot table

  1. Activate any cell in your summary table
  2. Choose Data - PivotTable and PivotChart Report (the menu command may vary, depending on the version of Excel).
  3. In the PivotTable dialog box, select the Multiple consolidation ranges option, and click Next.
  4. In Step 2, choose the I will create the page fields option and click Next.
  5. In Step 2b specify your summary table range in the Range field (A1:E13 for the sample data) and click Add. Click Next.
  6. In Step 3, select a location for the pivot table, and click the Layout button.
  7. In the Layout dialog box, you will change the default layout in the diagram. Drag both the Column button and Row button away from the diagram. This will leave the diagram with only a data field: Sum of Value. The dialog box should look like the figure below.
  8. Click OK and then Finish to create the pivot table.

Part 2: Finishing up

At this point, you will have a small pivot table that shows only the sum of all values:

  1. Double-click the cell that contains the total (outlined in yellow, above). Excel will create a new sheet that displays the original data in the form of a database table (see the figure below).
  2. The column headings will display generic descriptions (Row, Column, and Value), so you'll probably want to change these headings to make them more descriptive.

A VBA Macro to do it

If you do this sort of thing on a regular basis, you may prefer to use a VBA macro. Just copy the VBA code to a VBA module. Then activate a cell in your summary table and execute the ReversePivotTable macro. This macro uses simple looping -- no fancy pivot table tricks.



Getting A List Of File Names

Category: General | [Item URL]

Many users are surprised to discover that Windows does not provide a direct way to get a list of file names contained in a directory. This tip describes how to create such a list in a text file, which can then be imported into Excel.

To generate a list of file names, you'll need to use a DOS command typed in a DOS command window. To open a DOS command window:

  1. Click the Windows Start button
  2. Click Run
  3. Type "cmd" (no quotes) and press Enter. if "cmd" doesn't work, use "command".

You'll get a window like the one shown below.

Next, you need to type a DOS command to generate the file list. For example, if you would like to generate a list of all files in the root directory of drive D, type the following at the command prompt and press Enter:

dir d:\

To list the files in a particular directory, add the directory name after the drive:

dir d:\my files\

The file names will be listed in the window. Usually, you'll want these files to be sent to a file. To redirect the output to a file, use the > character and specify a file name. For example, to send the file names to a text file named filelist.txt in the root directory of drive C, use this command:

dir d:\ >c:\filelist.txt

If you would like the file list to include the files in all subdirectories of drive D, use the /s switch:

dir d:\ /s >c:\filelist.txt

The directory listing will contain lots of additional information. To get the file names only (bare format), use the /b switch:

dir d:\ /s /b >c:\filelist.txt

To find out about other options available for the DIR command (such as including file dates and times), type this command:

dir /?

After the text file is generated, you can import it into Excel by using the File - Open command.

NOTE: If you need do this on a regular basis, you may be interested in this article from Microsoft, which describes how to create a batch file that lists file names in Notepad.



Spreadsheet Protection FAQ

Category: General / General VBA | [Item URL]

The topic of "protecting" a spreadsheet receives a great deal of attention in the Excel newsgroups. This document provides answers to some common questions.

Excel provides three primary ways to protect information in a spreadsheet:

NOTE: This document was written prior to the release of Excel 2007.

Worksheet Protection

Questions in this section deal with protecting the contents of cells and objects on a worksheet.

How do I protect a worksheet?

Activate the worksheet to be protected, then choose Tools - Protection - Protect Sheet. You will be asked to provide a password (optional). If you do provide a password, that password will be required to unprotect the worksheet.

I tried the procedure outlined above, and it doesn't let me change any cells! I only want to protect some of the cells, not all of them.

Every cell has two key attributes: Locked and Hidden. By default, all cells are locked, but they are not hidden. Furthermore, the Locked and Hidden attributes come into play only when the worksheet is protected. In order to allow a particular cell to be changed when the worksheet is protected, you must unlock that cell.

How do I unlock a cell?

  1. Select the cell or cells that you want to unlock.
  2. Choose Format - Cells
  3. In the Format Cells dialog box, click the Protection tab
  4. Remove the checkmark from the Locked checkbox.

Remember: Locking or unlocking cells has no effect unless the worksheet is protected.

How do I hide a cell?

  1. Select the cell or cells that you want to unlock.
  2. Choose Format - Cells
  3. In the Format Cells dialog box, click the Protection tab
  4. Add a checkmark to the Hidden checkbox.

Remember: Changing the Hidden attribute of a cell has no effect unless the worksheet is protected.

I made some cells hidden and then protected the worksheet. But I can still see them. What's wrong?

When a cell's Hidden attribute is set, the cell is still visible. However, it's contents do not appear in the Formula bar. Making a cell Hidden is usually done for cells that contain formulas. When a formula cell is Hidden and the worksheet is protected, the user cannot view the formula.

I protected my worksheet, but now I can't even do simple things like sorting a range. What's wrong?

Nothing is wrong. That's the way worksheet protection works. Unless you use Excel 2002 or later.

How is worksheet protection different in Excel 2002 and later?

Excel 2002 and later provides you with a great deal more flexibility when protecting worksheets. When you protect a worksheet using Excel 2002 or later, you are given a number of options that let you specify what the user can do when the worksheet is protected:

  • Select locked cells
  • Delete columns
  • Select unlocked cells
  • Delete rows
  • Format cells
  • Sort
  • Format columns
  • Use AutoFilter
  • Format rows
  • Use PivotTable reports
  • Insert columns
  • Edit objects
  • Insert rows
  • Edit scenarios
  • Insert hyperlinks

Why aren't these options available in earlier versions of Excel?

Good question. Only Microsoft knows for sure. The limitations of protected worksheets have been known (and complained about) for a long time. For some reason, Microsoft never got around to addressing this problem until Excel 2002.

Can I lock cells such that only specific users can modify them?

Yes, but it requires Excel 2002 or later.

How can I find out more about the protection options available in Excel 2002 or later?

Start with Excel's Help system. If you're a VBA programmer, you may be interested in this MSDN article that discusses the Protection object.

Can I set things up so my VBA macro can make changes to Locked cells on a protected sheet?

Yes, you can write a macro that protects the worksheet, but still allows changes via macro code. The trick is to protect the sheet with the UserInterfaceOnly parameter. Here's an example:

ActiveSheet.Protect UserInterfaceOnly:=True

After this statement is executed, the worksheet is protected -- but your VBA code will still be able to make changes to locked cells and perform other operation that are not possible on a protected worksheet.

If I protect my worksheet with a password, is it really secure?

No. Don't confuse protection with security. Worksheet protection is not a security feature. Fact is, Excel uses a very simple encryption system for worksheet protection. When you protect a worksheet with a password, that password -- as well as many others -- can be used to unprotect the worksheet. Consequently, it's very easy to "break" a password-protected worksheet.

Worksheet protection is not really intended to prevent people from accessing data in a worksheet. If someone really wants to get your data, they can. If you really need to keep your data secure, Excel is not the best platform to use.

So are you saying that protecting a worksheet is pointless?

Not at all. Protecting a worksheet is useful for preventing accidental erasure of formulas. A common example is a template that contains input cells and formulas that calculate a result. Typically, the formula cells would be Locked (and maybe Hidden) the input cells would be Unlocked, and the worksheet would be protected. This helps ensure that a novice user will not accidentally delete a formula.

Are there any other reasons to protect a worksheet?

Protecting a worksheet can also facilitate data entry. When a worksheet is locked, you can use the Tab key to move among the Unlocked cells. Pressing Tab moves to the next Unlocked cell. Locked cells are skipped over.

OK, I protected my worksheet with a password. Now I can't remember the password I used.

First, keep in mind that password are case-sensitive. If you entered the password as xyzzy, it won't be unprotected if you enter XYZZY.

Here's a link to a VBA procedure that may be able to derive a password to unprotect the worksheet. This procedure has been around for a long time, and is widely available -- so I don't have any qualms about reproducing it here. The original author is not known.

If that fails, you can try one of the commercial password-breaking programs. I haven't tried any of them, so I have no recommendations.

How can I hide a worksheet so it can't be unhidden?

You can designate a sheet as "very hidden." This will keep the average user from viewing the sheet. To make a sheet very hidden, use a VBA statement such as:

Sheets("Sheet1").Visible = xlVeryHidden

A "very hidden" sheet will not appear in the list of hidden sheets, which appears when the user selects Format - Sheet - Unhide. Unhiding this sheet, however, is a trivial task for anyone who knows VBA.

Can I prevent someone from copying the cells in my worksheet and pasting them to a new worksheet?

Probably not. If someone really wants to copy data from your worksheet, they can find a way.

Workbook Protection

Questions in this section deal with protecting workbooks.

What types of workbook protection are available?

Excel provides three ways to protect a workbook:

  • Require a password to open the workbook
  • Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets
  • Prevent users from changing the size or position of windows

How can I save a workbook so a password is required to open it?

Choose File - Save As. In the Save As dialog box, click the Tools button and choose General Options to display the Save Options dialog box, in which you can specify a password to open the file. If you're using Excel 2002, you can click the Advanced button to specify encryption options (for additional security). Note: The exact procedure varies slightly if you're using an older version of Excel. Consult Excel's Help for more information.

The Save Options dialog box (described above) also has a "Password to modify" field. What's that for?

If you enter a password in this field, the user must enter the password in order to overwrite the file after making changes to it. If the password is not provided, the user can save the file, but he/she must provide a different file name.

If I require a password to open my workbook, is it secure?

It depends on the version of Excel. Password-cracking products exist. These products typically work very well with versions prior to Excel 97. But for Excel 97 and later, they typically rely on "brute force" methods. Therefore, you can improve the security of your file by using a long string of random characters as your password.

How can I prevent a user for adding or deleting sheets?

You need to protect the workbook's structure. Select Tools - Protection - Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked. If you specify a password, that password will be required to unprotect the workbook.

When a workbook's structure is protected, the user may not:

  • Add a sheet
  • Delete a sheet
  • Hide a sheet
  • Unhide a sheet
  • Rename a sheet
  • Move a sheet

How can I distribute a workbook such that it can't be copied?

You can't.

VB Project Protection

How can I prevent others from viewing or changing my VBA code?

If you use Excel 97 or later... Activate the VB Editor and select your project in the Projects window. Then choose Tools - xxxx Properties (where xxxx corresponds to your Project name). In the Project Properties dialog box, click the Protection tab. Place a checkmark next to Lock project for viewing, and enter a password (twice). Click OK, then save your file. When the file is closed and then re-opened, a password will be required to view or modify the code.

Is my add-in secure?

The type of VB Project protection used in Excel 97 and later is much more secure than in previous versions. However, several commercial password-cracking programs are available. These products seem to use "brute force" methods that rely on dictionaries of common passwords. Therefore, you can improve the security of your file by using a long string of random characters as your password.

Can I write VBA code to protect or unprotect my VB Project?

No. The VBE object model has no provisions for this -- presumably an attempt to thwart password-cracking programs. It may be possible to use the SendKeys statement, but it's not completely reliable.



Extended Date Functions

Category: General / VBA Functions | [Item URL]

A companion file is available: Click here to download

Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. I create an add-in that addresses this deficiency. The Extended Date Functions add-in (XDate) allows you to work with dates in the years 0100 through 9999.

When the XDate add-in is installed, you can use any of the following new worksheet functions in your formulas:

  • XDATE(y,m,d,fmt): Returns a date for a given year, month, and day. As an option, you can provide a date formatting string.
  • XDATEADD(xdate1,days,fmt): Adds a specified number of days to a date. As an option, you can provide a date formatting string.
  • XDATEDIF(xdate1,xdate2): Returns the number of days between two dates.
  • XDATEYEARDIF(xdate1,xdate2): Returns the number of full years between two dates (useful for calculating ages).
  • XDATEYEAR(xdate1): Returns the year of a date.
  • XDATEMONTH(xdate1): Returns the  month of a date.
  • XDATEDAY(xdate1): returns the day of a date.
  • XDATEDOW(xdate1): Returns the day of the week of a date (as an integer between 1 and 7).

These are all VBA functions.

Applications:

The XDate add-in is particularly useful for genealogists and others who need to perform simple calculations using pre-1900 dates. The figure below, for example, shows the XDATEYEARDIF function being used to calculate ages.

Requirements:

The XDate add-in requires Excel 97 or later.

Limitations:

Be careful if you use dates prior to 1752. Differences between the historical American, British, Gregorian, and Julian calendars can result in inaccurate computations.

Note:

My Power Utility Pak also includes the XDATE functions. However, they are not packaged in an add-in. Rather, you can add the functions directly to the VBA project for your workbook. As a result, you can distribute the workbook without a dependent add-in.

Documentation:

Complete context-sensitive online help is included.

Installation:

Installation is a two-step process:

  1. Extract the files
    Download and execute the xdate.zip file. Extract the files into any directory.
  2. Install the add-in
    Start Excel and select the Tools - Add-Ins command. In the Add-Ins dialog box, click the Browse button and locate xdate.xla (the file you extracted in Step #2). Click OK.

You can type the functions manually, or use Excel's Paste Function dialog box. To access the Paste Function dialog, click the Paste Function button, or select Insert - Function.  The XDate Functions are listed in the 'Date & Time' Category. When a function is selected in the Paste Function dialog, press F1 to read the online help.



Page 2 of 2 pages
[Previous page]  

Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy