icon shortcut on desktop

Open Excel Template and save to specific folder

Step 1: Prepare Your Excel Template

Create the Excel template you need for your class. Customize it with all the necessary formulas, formats, and initial data.
Save the template in a convenient location. For example, C:\Templates\MyClassTemplate.xlsx.

Step 2: Write a VBA Macro to Save the File Automatically

Open your template and press ALT + F11 to open the VBA editor.
Insert a new module via Insert > Module and enter the following VBA code:

   Sub SaveWorkbook()
       Dim path As String
       path = "C:\Documents\ClassSheets\" ' Change this to your specific folder path
       ThisWorkbook.SaveAs Filename:=path & "ClassSheet_" & Format(Now, "yyyymmdd_HHMMSS") & ".xlsx"
       Application.Quit
   End Sub

Save your changes and close the VBA editor.

Step 3: Enable Macros

Ensure that macros are enabled in Excel, or adjust the settings to enable macros with notification.

Step 4: Create a Batch File to Open Excel and Run the Macro

Open Notepad and type the following batch script:

   @echo off
   cd C:\Program Files\Microsoft Office\root\Office16  ' Change this path to where your Excel.exe is located
   start excel.exe /e "C:\Templates\MyClassTemplate.xlsx" /mSaveWorkbook

Save this file with a .bat extension, for example, StartClassSheet.bat, on your desktop or another convenient location.

Step 5: Create a Desktop Shortcut

Right-click on the desktop and choose New > Shortcut.
In the location field, browse and select the StartClassSheet.bat file you created.
Name the shortcut something meaningful, like "New Class Sheet".

Now, when you double-click the shortcut, it will open Excel, load your template, run the macro to save a new version of the workbook in the specified directory with a timestamp, and then close Excel. This automates your workflow with minimal interaction, making it a quick and efficient process for managing class-specific Excel files.

Pages