Author Posts

July 1, 2014 at 6:34 am

Hi All,

I've got a script that I've written that does some Excel automation. It takes three spreadsheets, does a variety of vlookups/formatting etc and then saves all the data in one sheet. It's working fine.

However, the file paths for the three reference spreadsheets within the script are currently static – i.e.:

C:\users\thickgit\Desktop\PSScript\sheet1.xlsx,
C:\users\thickgit\Desktop\PSScript\sheet2.xlsx,
C:\users\thickgit\Desktop\PSScript\sheet1.xlsx etc

I want to give my script to a friend, have him create a folder in any location, put the script and his three spreadsheets in the same folder and just run the script so that he ends up with the final spreadsheet in the same folder.

How do you essentially put a '.\sheet1.xls' file path into your script so it will locate the files that are in the same directory as the script (and also output the final data to that folder too?)

Cheers.

TG

July 1, 2014 at 6:54 am

The path from which the script is being run is stored in the automatic variable $PSScriptRoot

For more information about automatic variables checkout the help document about_Automatic_Variables.

July 1, 2014 at 7:17 am

Thanks Simon. I tried something similar earlier, but had a problem with it because I was using single quotes instead of double quotes. It's working now. Cheers.