Now where on the network did I save my workbook?

I ask myself that question a lot, especially since I typically save my workbooks to SharePoint team sites on our corporate servers. If you, too, save and share files on your network, you can make your life easier by adding the Document Location box to the Quick Access Toolbar.  

The Document Location box shows you exactly where your file is saved. If you need your coworkers to review or edit your workbook, just paste the link from this box into an email message and send it to them. Trust me—sending a link is way better than sending a file attachment!

Here's how to add this box to the ribbon:

Click the arrow next to the Quick Access Toolbar, and then click More Commands. 

In the Excel Options dialog box, in the Choose commands from box, click Commands Not in the Ribbon.
Scroll down to the Document Location command and double-click it to add it to the Quick Access Toolbar.

Click OK. At this point, you should see the Document Location box on your Quick Access Toolbar. (If you ever want to get rid of it, right-click the border of the box, and then click Remove from Quick Access Toolbar.)

This procedure applies to Excel 2010, but it works pretty much the same way in Excel 2007. To quickly open the Excel Options dialog box in Excel 2010 or earlier, press ALT, and then press T, O.

-- Anneliese Wirth

View the original article here

Microsoft Excel 2007 tutorial - workbook security

Provides many ways to protect your job security and the Microsoft Office Excel 2007. For optimal security, a strong password to protect entire workbook file. Excel passwords can be up to 255 letters, numbers, spaces, and symbols, is case-sensitive. Additional protection for data in a workbook protects password whether or not a specific worksheet or workbook elements. Protect worksheet or workbook elements, and accidental or can help prevent users from deliberately changing, moving, or deleting important data.

Are listed in this Microsoft Excel 2007 tutorial, I to create a password to protect the workbook and how some workbook elements to protect. To the inside of Excel tips and tricks, expert Excel user's Guide. I me we're good you have another Excel tip as part of their Microsoft Excel 2007 tutorial series.

Securing workbooks

To provide security for the entire workbook, you can specify two passwords.

Show / hide the workbook opens the. This is the encrypted password, preventing unauthorized access to the workbook. To display the data they read-only mode that can also allow users the option open. You can prevent accidental changes and this is stored. Modify the workbook. Is the password is not encrypted, so this is to allow certain users edit a workbook.

Don't have the same password to these password applies to the entire workbook. Safer passwords are actually different. Available for both functions to provide strong security. Speak of the combination of strong passwords strong capitalization, numbers, and symbols. Sw33tH3ArT0nE, sweetheartone, strong but strong.

To password-protect a workbook.

Save the selected Excel workbook, and then click the Microsoft Office button, and click. Yes, this is an existing workbook. Click lower left corner of the Save as dialog box, click the Tools button, and then select General options. To require users to enter a password when you open a file in the Open box, enter the password in the password. To change the box to require users to enter a password you can save the changes, the password, enter your password. To protect against users accidentally modifying files, read-only check box. Prompts you whether you want to open, they read-only files. Be careful! If you create a password to modify a user to enter this password message appears, read-only and open options. Therefore, if you use a password to change this option. Click the OK button. Prompted retype the password to confirm. After you confirm, click ok. Click the Save button. If you are using the existing workbook, the same file name, replace the existing workbook, click Yes to prompts.

Provides options to protect Microsoft Office Excel 2007 is changed or security element security worksheet or workbook elements removed from using the data. Microsoft Office Excel and other tips and tricks, see Microsoft Excel 2007 tutorial series on my other article.

Security worksheet elements

Unlock other users able to change any cell or range on the worksheet to protect. Select each cell to select unlock or whole range locks. Home, click, and then on the Format tab, in the cells with cell formatting] to click. Click the protection tab, click to select the locked check box clear. Users want to show or hide not formulas. Select to hide cells that contain formulas. Home, click on the cell formatting button click, and then on the Format tab, in the cells. Click the protection tab, select the hidden check box, and click OK. To unlock the object like a picture, clip art, or shape: Click each object you unlock while pressing the [CTRL]. Displays the additional picture tools or drawing tools, Format tab note. Do not select objects of different types and does not initialize the dialog box launcher. Click size on the Format tab click the size dialog box launcher next to. Click the Properties tab to clear the locked check box. If the lock text check box to clear. Click the close button. Reviews, click Protect sheet changes tab. Select the element to allow the user to change the list of all users on this worksheet. Unprotect sheet box, enter the password for the sheet password, and then click OK and confirm the password. This is an optional password. If you don't use it, and all users, can seat unprotect protected elements change.

Security workbook elements

To prevent user from, among others, by using the workbook elements of security.

Change the name of the worksheet to hide the display moving, deleting, hiding, or worksheet. Copy the worksheet to insert new worksheets or chart sheets move or another workbook. Displays cell data area, or displaying page field pages display the source data in a PivotTable report. Record new macro

Reviews, choose the option protect structure and Windows changes tab, click protect workbook. To protect workbook structure, select the structure check box. Select the Windows check box to keep workbook Windows every time you open the workbook in the same size and position. This workbook, password (optional)] confirm password to prevent users from removing type, click OK. This is an optional password. If you do not specify a password and Unprotect Workbook users, can any protected elements change.

Excel security is the key features for protecting data.

This is just one more Excel tips and tricks that can help you in your job more efficiently. Be able to benefit many more features. Check out this tutorial for the Microsoft Office 2007 article "to know", Excel lists. Also includes links to free sample online tutorial.

Todd ?????? many years of experience and IT professionals are as business analysts, project managers and IT architects.

How to customize the workbook in Microsoft Excel: default

Gets the standard workbook when you create a new Excel workbook. But what if that book do not like or? For example, maybe print page always (or almost) to use has a standard header. Prefer a different default font style or new worksheet created sizes, number formats, change the width of a column layout often.

To do this, the appearance and layout of the Excel worksheet to give quite a bit of control. Very much, is to create a completely customized default workbook is easy. If you create in Microsoft Excel 2010 and Excel 2007 is behind this magic trick template file named book.xltx (book.xltm) on the default workbook contains macros, the file saves to the appropriate location on your hard drive.

To create a new default workbook template.

Open a new blank Excel workbook. Customize exactly to the blank workbook. Save the workbook in the folder specified in the specific file name. Additional ideas and procedures are as follows.

Excel workbook might change some elements:

Font styles and font sizes: highlight part of a worksheet and select the number, alignment, and font formatting settings from the home tab, in the font . Print settings: one or multiple worksheets selected, page layout] tab > page settings group headers and footers, margins and print orientation, such as of specify print settings, and other page layout choices to indicate. Removes thenumber of seats: additional or worksheet name sheet tab to change the color of the worksheet tabs. : Column widths and layout change the width of the column otherwise usually prefer different column widths, select the column, or an entire worksheet.

Note: your custom default workbook to insert the back in a new worksheet, to return to the original formatting and layout. You may book a source workbook, extra worksheet, you can copy on additional demand, extra or master worksheet.

How to apply changes to multiple cells or worksheets

To apply formatting changes to all cells, columns, or rows, first of all to all cells in the selection (press Ctrl + A) highlight. When you are finished, press [Ctrl] + [home] highlight the cell clears.

To print multiple worksheets in the workbook settings apply, such as formatting changes, right-click any sheet tab, select all sheets on the] and click. Complete the change, all sheet tabs in the group the worksheets off click again.

You do not need to create a new workbook by default all (the default is 3), if you want to change the number of worksheets in a new workbook. Select the file in Excel 2010, > options, in the General category to select, and then sets number of sheets needed for this many sheets. In Excel 2007, select the Microsoft Office button, click Excel Options. This many sheets specifies the number of sheets for setting, and then select popular categories.

To save your new default workbook.

If the default new workbook are your favorite files tab or the Microsoft Office button, then choose Save as to save > Excel workbook. Save as] dialog box, select the Excel template (.xltx), and select the file type drop-down list. Name the file as book.xltx. Need to save files in the XLSTART directory on the local c: drive. Location of this directory varies depending on the version of Windows and Microsoft Office; to find the hard drive folder. You can close it after you save the template file. Close Excel. See new books to start Excel.

Each time you start Excel now, a new blank workbook create template based on. In addition to clicking the new toolbar button (or press [CTRL] + N), a new workbook created from the template.

Like usual, this or other default workbook can be customized individually if necessary.

No effect on workbooks, create, and change the default on your computer only save active workbook custom default Excel workbook to use computers on the network by other users. To share the default workbook book.xltx files copy to the appropriate location on another computer, however, can.

XLSTART directory is on your network if you are storing files, you must access permissions. Instead, this new alternate startup directory book.xltx save file, and then any name that you can create unique system startup directory. Select Directory names are unimportant, but need to tell it to Excel.

In the other directory to save the default workbook.

Create a new folder in book.xltx file containing the C drive. File selection in Excel 2010, > options], click the Advanced category, and click. In Excel 2007, click Microsoft Office button and choose Excel Options, and then select the Advanced category. Enter the full path of the section of general use as the alternate startup folder at startup, open all files in box. Book of the same name opens a file in the XLSTART folder if in both the XLSTART folder and the alternate startup folder.

??: Only be able to try to Excel open all files in the alternate startup folder, open the Excel files and determine whether or not to specify folders that contain only files each time you start Excel.

To save effort and time in Microsoft Excel, creates its own custom books today.

Dawn Bjork Busby Microsoft Office Specialist (MOS) master certified instructor and certified expert certified instructors, Microsoft application professionals (paid), Microsoft Office and as the software Pro ? and Microsoft Certified Trainer (MCT) is. Dawn software speakers share smart ways to use software effectively through her work as a trainer, consultant, and author of six books. Software for more tips, tricks, strategy and technology the found at http://www. SoftwarePro.com.

Save the workbook in Microsoft Excel 2007.

To preserve a book about the way you can create a simple table to Microsoft Excel 2007 1.009 learned from a blank worksheet, follow these steps in. Hard _ must could save it to disk or insert, thumb drives, laptop or desktop.

It to a blank worksheet or workbook, and put it at the same time, file name, location to find. Once again, only that you are trying to view some of the procedures that you can.

First of all, and then click the Office button and select [Save]. To save a file save dialog box appears, the default location as searches. There's no need to save the location your files to find the right place you can navigate to different folder instead.

The side bar drop-down menu, and then select folder. It is recommended to save the file in my documents to usually get files to enable easier. Create a folder if necessary.

End preferred file name in the file name field, for example's namescope "Excel file first", followed by type, and then click the Save button to enter.

Is a shortcut for actually storing files faster. On your keyboard, press the ctrl key and at the same time. Same to the search, in your dialog box that appears, and then just run steps listed above to save a Microsoft Excel file of the first way.

Lewis T is the owner of the http://www.excelexpertuser.com. Locate the way Microsoft Excel master, website.

Paste the text manually in a workbook? Absolutely no!

Recently, I had a lot of cool templates PowerPoint 2010 that I wanted to post. A colleague was going to take each template and a short paragraph describing the model and create the downloadable files. So I saved PowerPoint template (.potx) in a network file share and then creates a text file (.txt) corresponding to each template with a description in the same folder.

Shortly after, my colleague asked me if I had all the descriptions for the models. "Yes," I say, "these are included in that shared folder with templates. Each description is saved as a text file. "

Puzzled, my colleague asks, "Um, might put all descriptions in a single Excel workbook?"

Sigh.

So here is my problem. I needed to turn this:

 

... in this:

 

Overall, the folder was about 40 text (.txt) file that I needed to fill in your working folder, where the content would be listed out line by line on a single worksheet. From left to right, each line is necessary to include my name, the name of the PowerPoint template files, template title (the filename cleaned up a bit) and description to 255 characters.

Sure, you may have typed the name of the file in the second column, he wrote a formula in the third column to translate the name of the file in a title and then copied and pasted from the text file within the workbook. Or, if so inclined, I could simply use the command From text in group to get external data data card.

This type of time creating a workbook to spend? Dandeng!

Instead, I wrote some Visual Basic code to extract the data that I need from the folder with the PowerPoint file and text, and then enter in the workbook.

To get the text from .txt file in the Excel workbook, the code uses the directory.GetFiles method and a search template to only open text files in the specified folder. Enter the name of the text file in the next empty row in column b of the active sheet. Then open any text file with a StreamReader object and then adds each line of text from that file into a string variable. Once each line of text in the file is written to the string, the string is added to column d in the same row. Therefore, the line counter is incremented to move the next line and repeats the process until it transcribed each txt. files in the specified folder.

Here's the code I used. Is a project-level workbook by using Visual Basic, Visual Studio 2010. network 4 and Excel 2010.

Imports Microsoft.VisualBasic.FileIO

Imports System.io

Imports System

Public class ThisWorkbook

Private Sub ThisWorkbook_Open () handles me.load ' Open

' Create an array where each element is a text file in the folder

Dim MyFiles As String() = directory.GetFiles ("C:\My documents\importtext_test", "txt")

Dim MyFile As String

Dim MyWorkbook as Microsoft.Office.Tools.Excel.workbook = Me.Application.ActiveWorkbook

Dim MySheet as Excel.worksheet = Me.Application.ActiveSheet

' Set the header rows

With MySheet

.Range ("A5").Value = "Contact Office.com"

.Range ("B5").Value = "Filename"

.Range ("C5").Value = "Title"

.Range ("D5").Value = "Description"

Ends with

«Create a counter for the rows in the workbook

Dim numline As Integer = 6

For each MyFile In MyFiles

«Create a StreamReader

Dim MyReader as StreamReader = New StreamReader (MyFile)

Dim currLine As String

Dim currCellValue As String = ""

' String MyFileName includes directory

«The name of the text file begins with the character 32

' Enter the file name and the name of the template spreadsheet

With MySheet

.Range ("A" & numline).Value = "Eric Schmidt"

.Range ("B" & numline).Value = _

MyFile string.substring (32).Replace (".txt", "potx")

.Range ("C" & numline).Value = _

MyFile string.substring (32).Replace ("_", "").Replace (".txt", "")

Ends with

Do

«Read every line containing text and add to the string currCellValue

currLine = MyReader console.ReadLine ()

currCellValue = currCellValue + "" + currLine

CurrLine Loop Until you nothing

«Close the text file

Myreader.close()

' Enter currCellValue into spreadsheet

With MySheet .range ("D" & numline)

.Value = currCellValue

.WrapText = True

Ends with

numline = numline + 1

Next

«Resize rows and columns of the workbook

With MySheet

.Columns ("D").ColumnWidth = 75

.(Range.Cells (1, 1) _

.(Numline, 4) of cells).AutoFit () .rows.

.Range ("A:D").Columns AutoFit ().

Ends with

End Sub

End Class

To write this code, I consulted the following articles:

--Eric Schmidt

Eric Schmidt is a programming writer for Visio.

View the original article here

Adding a table of content for the workbook – it's easy, I promise!

Sometimes workbooks can be very large and difficult to navigate. Only so many tabs fit at the bottom of the screen, and it is difficult to know how long is each worksheet. Excel does not provide a built-in way to add a table of content in a workbook; However, there is a way! In this post, I'll show you how to add a new worksheet to the workbook called "TOC" (TOC). This sample uses Excel 2010.

On the sheet of TOC, column lists the name of each sheet, and includes a hyperlink to the appropriate worksheet. Column b lists (which is the workbook) the number of the worksheet and the number of pages contained in this worksheet (how many printed pages would be). You must use Visual Basic for Applications (VBA), but do not be frightened by the fact that you need to code — is actually very simple, and I'll walk you through it step-by-step.

First, you must add code to the workbook and to do that you need Developer card. If you do not usually work with code in Excel, you probably will not see the development tab in the Ribbon. To view the development: tab

Click the File tab. The Guide below, click Options on. Click to customize the Ribbon. In Customizes the Ribbon, select the check box Developer.

Now you can create a macro:

development tab, in the code group, click Visual Basic.

In the Visual Basic Editor, Enter menu, click form on. In the code window of the module, type or copy the following macro code:

4. In the Visual Basic Editor, Files menu, click close and return to Microsoft Excel.

5. to save the workbook with macro code, select Save File menu and save the file to a macro-enabled workbook (.xlsm).

You are almost ready to run the code. You may need to change the macro security settings to enable the macros. To set the security level temporarily to enable all macros, do the following:

You are almost ready to run the code. You may need to change the macro security settings to enable the macros. To set the security level temporarily to enable all macros, do the following:

development tab, in the code group, click the Macro Security.
In Macro Settings, click enable all macros (not recommended, potentially dangerous code can run) and then click OK on.
Note    To help prevent, it is recommended that you return to potentially malicious code any of the settings that disable all macros after you finish working with macros.

And now you can run the code that creates the table of contents worksheet!

development tab, in the code group, click macro.
In the Macro window, select the Create_TOC macro and click Run on.

View the original article here