Enable Developer Mode
- Click file menu
- Select Options
- Click the customize ribbon
- Click Main Tab in drop down
- Select Developer and move it over
- Click OK
Changing an Excel sheets styles automatically through Macros
In this example we will take the spreadsheet above and apply cosmetic changes to it. This will create for us a macro that can be used anytime.
Under the Developer Tab we want to record our actions. Whenever a user interacts with the excel application code is being executed in the background. We can write Macros to do tasks VS clicking on the user interface. We will change the styles of some of the cells after clicking ‘record macro’ to re-use this styling.
We see that we can also assign this macro to a shortcut so that anyone could perform this task with ease.
After we perform the desired actions we click ‘stop macro’.
Now if you go back and undo your changes, you can run the macro and it re-applies the changes.
If you click Edit you can see the code :
Changing an Excel Sheets layout with Macros
After reading the section above, you can perform the same ‘record macro’ option under the Developers tab to also change the layout.
Let’s say I want to change my columns into rows. I will select Field A and copy it.
You copy your range of cells, and then you can paste with a special row to column option.
Depending on how dynamic your excel sheet is, you may be able to find a space you know data won’t exist like another Column to do the pasting.
Then you can delete the original Data, copy over your new data, and then delete the new data from Column E to Colum K to get this:
The script looks like this:
Automating Macros with powershell
First you might need to do this but I’d recommend only activating it while running your script:
You will need to create a ‘personal.xlsb in your ‘XLSTART’ excel location. Mine was located here:
C:\Program Files (x86)\Microsoft Office\Office15\XLSTART
Drop all of your macros into this spreadsheet that way your powershell code can call your macros and you won’t have to add them to any other sheets.
You should be able to copy and paste this into a powershell command line from the start menu and it would run. If you changed the directory locations.
#Create a new excel Instance
$excel = new-object -comobject excel.application
# For Every .xlsm file in your desired directory…
$excelFiles = Get-ChildItem -Path “C:\Users\Tim\Documents\” -Filter *.xlsm
# Make the Excel file visible to the user
$excel.visible = $true;
# Set your marco book location
$macroBook = $excel.workbooks.open(“C:\Program Files (x86)\Microsoft Office\Office15\XLSTART\PERSONAL.xlsb”)
# For every file in the folder
Foreach($file in $excelFiles)
{
# Open the workbook
$workbook = $excel.workbooks.open($file.fullname)
# Select the first open sheet
$worksheet = $workbook.worksheets.item(1)
# Run your ‘chnge header colors’ macro from your ‘personal.xlsb’
$excel.Run(‘personal.XLSB!ChangeHeaderColors’)
# Save this as a ’52’ which is a .xlsm
$workbook.saveas($file.fullname + “_new.xlsm”,52)
# Close the workbook
$macroBook.close()
$workbook.close()
}
# Close Excel
$excel.quit()
