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.