Working backwards: lines from a text file

You may remember this chart from my previous post:

Mass totally staged. A fabrication.

Well, PowerPoint templates include the original files so that it was not completely false. But all text added back into the folder in order to create the image.

As I worked on the previous post, I realized that I have to include an image which shows visually what I needed to do. Yet, by the time I wrote the post, I had already handed out those models and all text files (now useless) were deleted.

So how did I create this image, you may ask? Simple: I wrote the code to reverse the process of the previous post! The program below takes the rows in an Excel workbook and generates a text file from data lines.

The programming is pretty simple, once you understand how to create and use StreamWriter objects. When you open the workbook, for each row of data, the code gets the name of the file from column b in the first worksheet of the workbook, which replaces the file extension of the PowerPoint template (.potx) with the text file (.txt)Opens a StreamWriter object that creates a new text file, transfer the value from column c text file and close the text file.

Here's the code I used. Is a project-level workbook by using Visual Studio 2010, Visual Basic.NET 4 and Excel 2010. (If you want to know what the workbook looks like, please refer to my previous post.)

Imports System

Imports System. I

Public Class ThisWorkbook

Private Sub ThisWorkbook_Open () handles Me Open.

Dim Myfolder as Excel. Workbook = _

Me.Application.ActiveWorkbook

Dim MySheet as Excel. Worksheet = MyWorkbook. Worksheets (1)

Dim numRow As Integer = 6

Dim MyTextFileName As String

Dim currCell as Excel. Range = MySheet. Range ("B" & numRow)

While not currCell .value = ""

MyTextFileName = Value currCell.

MyTextFileName = MyTextFileName. Replace (".potx", ". txt ")

Using MyStreamWriter as New StreamWriter ("C:\My documents\importtext_test\" & MyTextFileName)

MyStreamWriter-> Write (MySheet. Range ("D" & numRow).Value)

End using

numRow = numRow + 1

currCell = MySheet. Range ("B" & numRow)

End while

Finalize

This program uses the following items:

' http://msdn.microsoft.com/en-us/library/36b93480.aspx

' http://msdn.microsoft.com/en-us/library/6ka1wd3w.aspx

End Sub

End Class '

Here's another version of the project, written in VBA:

Sub ExportToText ()

Dim Myfolder As Excel. Workbook

Dim MyWorksheet As Excel. Worksheet

Dim currCell as Excel. Range

Dim MyTextFileName As String

Dim numRow As Integer

numRow = 6

Set Myfolder = Application. ActiveWorkbook

Set MySheet = MyWorkbook. Sheets (1)

Set currCell = MySheet. Range ("B" & numRow)

Do until currCell. Offsets (numRow, 0) = vbNullString

CurrCell. MyTextFileName = Offset (numRow, 0).Value

MyTextFileName = Replace (MyTextFileName, "", ".potx. txt ")

Call SetFileAndText (MyTextFileName, currCell. Offsets (numRow 2).Text)

numRow = numRow + 1

Loop

This program uses the following article:

' http://msdn.microsoft.com/en-us/library/dd439413 (office). aspx

End Sub

Sub SetFileAndText (fileName, matches)

Add Microsoft Scripting Library to the project references.

Dim fso as new FileSystemObject

Dim stream as a TextStream

Dim myText As String

Set stream = fso.CreateTextFile ("C:\My \My" & fileName)

stream.Write matches

stream.Close

End Sub

To write this code, I consulted the following article:

-Eric Schmidt

Eric Schmidt is a programming writer for Visio.

View the original article here

Working with grouped Microsoft Excel worksheet

It is working with one of the features of Excel is often overlooked group of worksheet. You can group and, at the same time two or more worksheets worksheet in the workbook. You must perform the same tasks this repeat over and over on another worksheet.

In the workbook set next to each other to group worksheets.

Click the sheet tab of the first worksheet. Click the tab for the last sheet in the group while pressing the SHIFT key

Next to one another appropriately, not Group worksheet.

Click the sheet tab of the first worksheet. Click the tab of the sheet for each CTRL key in the group.

After you create the group displays the word "group" in the title bar of the workbook. Can be grouped together to set one worksheet formatting, all worksheets in the same format settings. To insert rows on a worksheet, all worksheets in the same row is inserted. This is great tool when the same of all the worksheets need to format.

The fill command editing, can be used to enter information on multiple worksheets are grouped on the home Ribbon bar when. For example, to do this if you can, and add some worksheets to workbooks to copy part of a worksheet, copy and paste that, however, some operations may take. Grouped worksheet by using the fill command new worksheet in the workbook to insert quickly in the copy of the original worksheet could not.

Create a total of more than one worksheet or is required before you can create a summary worksheet by using a special mathematical functions paste using the integration capabilities of the same worksheet. Make sure that the row or column headings and other formatting operation, change, add, spreadsheet is uniformly first, group the worksheets.

Spreadsheet and want to change, add or worksheet to group the same worksheet for all groups if you forget to note that if you use a group to be the numbers. Click on a sheet of AA is not, to delete a group in the group. If all of the worksheets are grouped, and then right click on any tab group unlock sheet. Will tell you whether group Excel sheet before making changes to, and then keep an eye on the title bar.

Learn about Excel in this way, is a lot. Business training team has developed an extensive online training Excel. What to Excel work of covered by visiting to see that you can. This training is displayed in Microsoft Excel-certified specialist Sue white.

Working with a linked Microsoft Excel worksheet

Provides several methods of link value of a Microsoft Excel worksheet or an entire workbook. Choose which way depends on the desired result. This article make the pros and cons.

Create a formula to link the. In this way, you can create active links between the worksheet or workbook. On the all worksheet cells linked in the same place you do not. You can perform arithmetic operations during the creation of the link. For example, figures from one worksheet to another worksheet multiplies the figure form the third worksheet number subtraction. You can explain how to link a cell in the worksheet or workbook. One drawback of this method of cell is processed at the same time, so time consuming is. Using a named range, this way of link help, and they are created and readable expression.

Creating a total on worksheet. This is also an active link between. Also updated if you change the number one worksheet to the aggregate formula sheet. When you insert a worksheet worksheet, harmony between the total is updated automatically. The disadvantage of this method is the same on all worksheet cells of all links must be in place. (It can be copied to other cells in summary worksheet formulas can have). In addition, only total cell can be at the same time. When you use the sum function to create the same spreadsheet with group mode, multiple worksheets can help. This method is also limited in the workbook between workbooks do not.

Integration features. To create a link aggregation can determine whether or not would be interactive. Provide the maximum level of detail summary worksheet for this method. As well as any function and the sum function to be able to select. For example, can create a summary worksheet average of several departments. The disadvantage of this method is required have the same worksheet. It is difficult to use the integration between different workbooks is not, it's impossible.

Use Paste link the. It also creates an active link between. On the all worksheet cells linked in the same place you do not. You can link to a worksheet or workbook. Providing integration is a great way to link the different totals from one worksheet, but according to your details.

Is several methods in this way, to create interactive links in Excel. If you're not familiar with a variety of techniques covered in the depths of the extensive online training has developed a business training team Excel. What to Excel work of covered by visiting to see that you can. This training is displayed in Microsoft Excel-certified specialist Sue white.

Working with Microsoft Excel formulas

You must be aware the results if you are using Excel formulas. Or they don't have meaning? are getting the results you expect. Can lead to incorrect results you need to know. Some examples can be found here.

Do not use cell references. When creating formulas, cell references and the actual number, but not to use? for example, if the number of cells B6 sales see a 10% increase, B6 uses the formula, or has used actual sales. If you are using the actual number of formulas and B6 change values, the formula remains unchanged. It is recommended that when you create a formula, click the cells on the when creating a formula to select the cell. Excel puts dotted outline is around the visible cells of the selected cell. Visually confirms a formula the right cell.

Processing of the following rules. Is followed by the results of Excel, the formula to calculate mathematical rules. Expression is to the right calculated from the left to many people, however, any multiplication and Division are calculated first and the addition and subtraction and believe. Let's say you want to show the increase in sales revenue cells B6 have shown. The percentage of increase in cell B2. You can create the formula reads the B6 * 1 + B2. Multiplied the additional percentage times in this example, a sales 1 result. To add parentheses formula force Excel to calculate the curly braces and the first (this is next-level operation) and the complete, formulas. B6 * (1 + B2), give a proper result.

When you copy the formula to cell references not paying attention. To copy a formula from one cell to another, and Excel default value is known as relative references. Copy the formula in the cell position, adjusting the formula, row and column reference in other words, rows and columns. Read B7 formulas in the example above, calculates the sales increase formula column in other sales categories to copy * (1 + B3). Each subsequent cell in the column to the specified row changes are reflected. If you have a percentage increase in cell B2, you copy the cell formula results are incorrect. To lock a cell in Excel to add formulas to the dollar sign and "to tell and you copy the formula do not change the reference. This is called absolute references. Will new expression B6 * (1 + 2 $b$). Modify cell B2 percentage it is, and all sales figure is the updated successfully. Mixing that has put a dollar sign reference column or row is both ago you may need to see. This method appropriate for your situation, see please but you can try out.

Learn about Excel in this way, is a lot. You must know exactly where is the formula you're building. Business training team has developed an extensive online training Excel. What to Excel work of covered by visiting to see that you can. This training is displayed in Microsoft Excel-certified specialist Sue white.

Working with the Microsoft Excel toolbar.

Toolbars, Microsoft Excel 2003 application important part, and to improve productivity and application efficiency. You can use a variety of tasks in Microsoft Excel toolbar range is included. Toolbar is a small bar containing buttons contains graphic images called the various icons only. Represents a single toolbar button for each command. Using the button, you need to do everything to put the mouse pointer over the button, and click the left mouse button once.

Keys displayed in the Microsoft Excel application default toolbar, click Save as on the formatting toolbar. That contains these key commands available in Microsoft Excel is the most common toolbar. After the initial installation of the Microsoft Excel application these two toolbars are placed on one line actually find. However, by changing the location toolbar changes position on the screen.

To move a toolbar a little place the mouse pointer at the start of four blue dotted bar toolbar. Press and hold the left mouse button and drag. You can drag from the current position of the lower down the menu bar, you can drag the toolbar by using this technique one or to the left or right.

You can see when changing the size of the toolbar on the left or right, the icon from the toolbar in one of the few no longer is displayed. This is by design. Change the icon based on the fit the size of toolbar applications basically adjusts to the size of the toolbar.

Another great tool that is included with the toolbar, toolbar buttons, mouse over the icon to only push ability to identify what. In this case the tool tip shows the ones shown here-.

Microsoft provides options of the Customize dialog box under the Tools menu by default, the in on the standard toolbar to display both on the formatting toolbar. However, you must display two lines of standard toolbar on one row, deselect the formatting toolbar to.

If you two standard and Formatting toolbars on two rows are command show standard and Formatting toolbars to select command two lines check box.

CFLs Excel toolbar shown, those toolbars or hide first navigate to the Tools menu to select the toolbar you must. It is must be careful when you view the menu of some important information.

Have if there is a check mark next to the toolbar name indicates this one toolbar is already displayed. In addition, it indicates no tick, the toolbar can be hidden.

To enable the toolbar in the name of the toolbar click once with the left mouse button. The name of the toolbar to disable toolbar again, and click.

Most of the toolbar but find you toward the top of the screen, you can relocate to any location toolbar.

Known toolbar docking and floating modes of two modes. Toolbar you can drag to place moves across the screen, move the mouse pointer in the title bar, hold down the left mouse button, move, in floating mode.

Return quickly if you want that originally docked to dock a floating toolbar, double-click the title bar of the toolbar.

Microsoft Excel, in the course of the in Microsoft Excel Help learn. Business website if you are looking for tools to help you learn MS Excel short cuts visits.