Macro To Add Daily Report
June 11, 2002 - by Bill Jelen
Judith posted this week's Excel question.
I need to import a file daily. I'd like to add the file each day to an existing Excel workbook and have the worksheet named for the d/m/y.
A short visual basic macro will make this a simple task. One problem - the tab name of a sheet can not include a slash. So, the solution will rename each day's sheet to have a name like "Oct 03 1999".
The solution involves adding a top worksheet to your existing book. This sheet, called "Menu" provides a simple user interface so that you can hand this task off to another staff member, if necessary. The top sheet contains cells where the user can specify the path name and file name of the file to be imported. If you are importing a file created by another system, then these fields will not change from day to day. The Menu sheet also contains a cell with the current date formatted without slashes. I will include an auto-open macro that will automatically fill in this cell for the user, but they are always free to change it before clicking the button to import the file. Finally, the Menu sheet has a big button that the user will click. This button will invoke the macro to import the file named on the Menu into the current book with the tab name specified.
Open a new workbook with just a single sheet. Double click the sheet tab and type a name for the sheet called "Menu". Add a nice title in cell A1. In Cells C3:C5, add the text as shown at left. In Cell D3, enter the pathname for the directory where the file is found each day. In cell D4, add the name of the file. Leave D5 blank. It is easier to set up the button once the macro is created, so we will leave that for later.
- Start the Macro Editor with Alt + F11
- Choose Insert - Module
- Copy the following text into the macro editor
Sub Auto_Open() ' This macro will put today's date as the default new tab name Sheets("Menu").Select Range("D5").Select Selection.Formula = "=text(now(),""mmm dd yyyy"")" Selection.Copy Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False Selection.Columns.AutoFit Range("D8").Value = "" End Sub Sub GetFile() ' This macro will import a file into this workbook ' Copyright 1999 www.MrExcel.com Sheets("Menu").Select PathName = Range("D3").Value Filename = Range("D4").Value TabName = Range("D5").Value ControlFile = ActiveWorkbook.Name Workbooks.Open Filename:=PathName & Filename ActiveSheet.Name = TabName Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1) Windows(Filename).Activate ActiveWorkbook.Close SaveChanges:=False Windows(ControlFile).Activate Sheets("Menu").Select Range("D8").Select ActiveCell.Value = "Completed" Range("D9").Select End Sub
Now, go back to Excel and add a button to the Menu sheet.
How to Add a Button to a Worksheet
- Show the Forms Toolbar by selecting View - Toolbars - Forms
- Select the Button tool from the toolbar
- Hold down the alt key, drag from the top corner of B8 into cell C9
- From the Assign Macro dialog, pick our macro called GetFile and click OK
- While the button is still selected, you can click and drag to select the text on the button. Replace this text with "Process!" or any text of your own choosing
You now have the complete application. Save the file in a convenient place. Once you set up your file with the correct information in cells D3:D4, the daily procedure will be as follows:
- Open the file we just created
- Verify that the date shown in cell D5 is appropriate.
- Click the big button.
- Save the file
Here are some ways that you can change the macro:
- If you want the tab to be named with the previous day's date, add a "-1" after "NOW()" in the macro.
- If the file to be imported has multiple sheets and the target sheet may not be on top, then add Sheets("sheetname").Select after the File.Open line above.