Task One: Opening the Report File
The orders for the most recent month, November 2007, are in the Nov2007 text file. The first task is to open the file, splitting it into columns as you do, and move the file into the workbook with the macro.
Open a Text File
| Tip | You might want to carry out Steps 3 through 6 as a dry run before recording the macro. |
If the Chapter02 workbook window is maximized, click the Restore Window button (for the workbook, not for the Excel application).
On the status bar, click the Record Macro button, type ImportFile as the macro name, and then click OK.
Click the Microsoft Office Button, and click Open.
Select Nov2007.txt in the list of files, and then click Open.
Step 1 of the Text Import Wizard appears. In Step 1, the first three rows of the file contain the report title and a blank line. You want to skip the first three rows.
Change the Start import at row value to 4.
Accept all the other default options, and click Finish.
The text file opens, with the columns split into Excel columns.
Drag up the bottom of the new window so that you can see the tabs at the bottom of the Chapter02 workbook. Then drag the tab for the Nov2007 worksheet on to the Sheet1 tab of the Chapter02 workbook.
The Nov2007 worksheet moves to the Chapter02 workbook, and the Nov2007.txt workbook disappears (because it lost its only worksheet, and a workbook can’t exist without at least one sheet).
Tip You’ll have several copies of the Nov2007 worksheet after you test this macro several times. Multiple copies will be useful as you develop the macros for later project tasks. Once you have a worksheet named Nov2007 in the workbook, new copies are automatically named Nov2007 (2), Nov2007 (3), and so forth.
Row 2 contains equal signs that you don’t need. Select cell A2. On the Home tab of the Ribbon, in the Cells group, click the Delete arrow, and then click Delete Sheet Rows.
Select cell A1, and click the Stop Recording button to stop the recorder.
Save the Chapter02 workbook.
You should now have the imported file split into columns and stripped of extraneous rows.
Watch a Macro Run by Stepping Through It
Rather than merely read a macro, you can step through it. This allows you to both read and test the macro as you watch it work. As you step through the macro, make notes of minor changes you might want to make to it.
When you step through a macro, the Visual Basic editor window appears over the top of the workbook. The Visual Basic editor window displays the selected macro and allows you to see which statement will execute next.
On the View tab of the Ribbon, click the Macros button, select ImportFile from the Macro Name list, and click Step Into.
The Visual Basic editor window appears on top of the workbook, with your recorded macro visible in the module. The statement that is ready to execute is highlighted in yellow, with a yellow arrow in the left margin.
The highlighted statement, which is the first statement in the macro, contains the macro name.
Sub ImportFile()
Press F8 once or twice as needed to highlight the OpenText statement in the body of the macro.
In your recorded macro, specific details such as line divisions and locations of the file and windows will differ from those in this example.
ChDir "C:\MSP\ExcelVBA07SBS"
Workbooks.OpenText _
Filename:="C:\MSP\ExcelVBA07SBS\Nov2007.txt", _
Origin:=437, _
StartRow:=4, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(23, 1), _
Array(28, 1), Array(43, 1), Array(53, 1)), _
TrailingMinusNumbers:=TrueThe ChDir statement is there because you changed to the folder containing the practice files. (ChDir stands for Change Directory because folders used to be called directories.) Since the OpenText statement contains the full path to the file, the ChDir statement is redundant, but it may come in handy later, so don’t mark it for deletion.
The long OpenText statement opens the text file. You can probably identify the argument that specifies the file name. The Origin and DataType arguments were default values in the first step of the Text Import Wizard. The StartRow argument is where you specified the number of rows to skip. The FieldInfo argument specifies how to split the text file into columns. The TrailingMinusNumbers argument is irrelevant because there are no negative numbers in this file. Be grateful that the macro recorder can create this statement so that you don’t have to!
The macro recorder divides this long statement into several lines by putting a space and an underscore at the end of each partial line. However, it doesn’t divide the statement at the most logical places. When you edit the macro, you should redivide the statement into meaningful lines. You can use the way the statement is divided in the preceding OpenText statement as an example.
For this type of process, you open a different file each month. This month, you opened the Nov2007 text file; next month, you’ll open the Dec2007 text file. Make a note to change the macro statement to let you select the specific file to open. You’ll learn how to prompt for a file name in the next section.
Press F8 to open the file and highlight the next statement, which is the first line of this With structure:
With ActiveWindow
.Width = 452.25
.Height = 254.25 End WithThese four statements were added when you moved the window out of the way. (Your Width and Height properties might have different values, and if you moved the window, there will be statements that change the Top and Left properties as well.) These are just accidental events that happen when you record a macro. It’s a good idea to eliminate them from the finished macro.
Press F8 to step through any statements that move, resize, or activate windows. Make a note to delete all of them. This then highlights the next statement:
Sheets("Nov2007").SelectThis statement makes the Nov2007 worksheet active, even though it was already the active sheet. (Macro recorders can’t be too cautious.) You’ll be able to delete this statement later also.
Tip You can edit many statements while stepping through the macro. For example, you could delete the Select statement. Some changes, however, would force you to restart the macro. For example, you can’t delete a With structure without restarting the macro (although you can delete individual statements inside a With structure). Visual Basic warns you if you try to make a change that would require you to restart the macro.
Press F8 to select the already-selected sheet, and make a note to delete the statement. This highlights the next statement:
Sheets("Nov2007").Move _
Before:=Workbooks("Chapter02.xlsm").Sheets(1)This statement moves the new sheet into the Chapter02 workbook. But when you run this macro next month, the sheet won’t be named Nov2007. It will be named Dec2007.
Press F8 to move the worksheet, and make a note to modify the macro so that it will work every month. This highlights the next statement:
Range("A2").SelectThis statement selects cell A2 of the worksheet.
Press F8 to select cell A2. This highlights the next statement:
Selection.EntireRow.Delete
Because the selected range of cells consists of cell A2 and this statement deletes the entire row of the selected range of cells, this statement ultimately just deletes row 2.
Press F8 to delete the row. This highlights the final statement in the body of the macro:
Range("A1").SelectThis statement selects cell A1. This is just a nice touch to leave the macro in the "default” location.
Press F5 to run the remainder of the macro.
In summary, here is your list of the changes you need to make to the recorded macro:
Delete unnecessary statements.
Allow the user to decide which file to open.
Make the macro work with any month’s file.
In the next section, you’ll learn how to make these changes.
Select a File While Running a Macro
Excel provides a method that prompts the user to open a file, but Excel doesn’t actually open the file. Instead it returns the name of the file, which you can turn over to the OpenText method.
Make the statement that begins with Workbooks.Open easier to read by dividing it into meaningful lines. Put a space and an underscore at the end of each partial line.
Follow the ImportFile macro example in the section titled “Watch a Macro Run by Stepping Through It,” earlier in this chapter.
Insert a new line immediately before the Workbooks.OpenText statement, and enter this statement:
myFile = Application.GetOpenFilename("Text Files,*.txt")As soon as you type the period after Application, Visual Basic displays a list ofall the methods and properties that can be used with an Application object. This feature is called Auto List Members. (The word Members refers to both methods and properties.) When you type the letter G, the list scrolls to show methods and properties that begin with that letter. At that point, you can press the Down Arrow key to select GetOpenFilename, and then press the Tab key to enter the method name into the macro.
When you type the opening parenthesis, Visual Basic displays the possible arguments for the GetOpenFilename method. This feature is called Auto Quick Info. You can ignore it for now. Just type the words in parentheses as they appear at the beginning of this step.
Tip If you want to show all the files in the folder-not just the files with the .txt extension, just leave the parentheses after GetOpenFilename empty.
The Application.GetOpenFilename method displays the Open dialog box, just as if you had clicked the Open command on the Microsoft Office Button list The words in parentheses tell the method to display only text files-files ending with the .txt extension. (Be careful to type the quotation marks just as they appear at the beginning of this step.) The word myFile at the beginning of the statement is a variable, or placeholder, for storing the selected file name.
In the Workbooks.OpenText statement, select the entire file name, including the quotation marks, and delete it. In its place, type myFile.
The first part of the statement should look like this when you finish:
Workbooks.OpenText _
Filename:=myFile, _
Origin:=xlWindows, _
StartRow:=4, _By the time this statement executes, the variable myFile will contain the name of the file. Now that you have eliminated the full path from the OpenText method, the ChDir statement at the beginning becomes very handy-it automatically puts the GetOpenFilename function into the appropriate directory (assuming that the incoming text files always go to the same place).
Delete the statements that resize the window and the statement that selects the Nov2007 sheet.
Change the words Sheets(“Nov2007”).Move to ActiveSheet.Move.
ActiveSheet is a special word that refers to whatever sheet happens to be active. This allows your macro to work with a new sheet from any month.When you’re finished, the macro should look essentially like this:
Sub ImportFile()
ChDir "C:\MSP\ExcelVBA07SBS"
myFile = Application.GetOpenFilename("Text Files,*.txt")
Workbooks.OpenText _
Filename:=myFile, _
Origin:=437, _
StartRow:=4, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(23, 1), _
Array(28, 1), Array(43, 1), Array(53, 1)), _
TrailingMinusNumbers:=True
ActiveSheet.Move Before:=Workbooks("Chapter02.xlsm").Sheets(1)
Range("A2").Select
Selection.EntireRow.Delete
Range("A1").Select End SubSave the Chapter02 workbook.
Troubleshooting If Option Explicit appears at the top of your module sheet, delete it before continuing.
Press F8 to step through the macro, and watch each statement work.
The macro should display the Open dialog box (displaying only text files), and then it should open the file that you select, delete the unwanted Row 2, and move the worksheet to the Chapter02 workbook.
Press F5 to run through the macro in normal mode.
It should behave the same.
That concludes the macro for the first task of your month-end processing project. By now, you should have two or three copies of the Nov2007 worksheet in the Chapter02 workbook. You’re ready to move on to the next task.
No comments:
Post a Comment