1. Go to the Automation window and create a new task
2. Click on 'Select to File' and in the parameters section, enter the SQL script, and in the Advanced options for the export, choose a Worksheet name. Press OK and enter an Excel filename to export to. (Also have 'Overwrite' ticked for the first file)
3. Back in the Automation diagram, click on 'Select to File' a second time to add a new entry to the Automation task, and enter the second statement. Then again go to the Advanced options, but this time choose a different Worksheet name. Make sure to use the same filename as the first 'Select to File' entry, and also this time, ensure that 'Overwrite' is Unticked. This way it appends the data to the first file, but in a new worksheet.
4. Save the Automation .tas file, and then run it and the Excel file will be created with multiple worksheets for each statement.