Macro Enabled Purchase Order Template

It’s been two years since I uploaded my Macro Enabled Purchase Order Template, and it’s been one of the most popular pages & downloads on my site to date…. I’ve recently updated this to fit some new requirements & I thought I’d share this with you all as it’s a bit shinier than before (oooh)

Purchase Requisition Template 63.4 KB

It has the same requirements as before:

  • Unique purchase order number on each request
  • Reduce the number of manual steps required to get approval from line manager
  • Provide centralised logging & archive of purchase orders raised

The updated template now also does the following:

  • Macro added to add & remove rows as required
  • Removed rows are deleted to prevent contribution to the total when non-visible
  • Protects cell formatting against pasted formatting
  • Conditional formatting to make the form look a bit neater
  • Only emails the range as it displays within Excel, not the entire worksheet

Important Steps:

  • You will need to add a reference to the Microsoft Office Object Library from Excel
  • Macros must be enabled (make the XLSX file a trusted document)
  • You must have Outlook & a valid MAPI profile for Outlook to be called
  • The email macro does not look up against exchange – The names you use must match the display name or exchange alias of the users as they appear in your GAL. As long as the name matches, Outlook will add the address and the mail will send. You could also use email addresses instead of names
  • You must update the paths to Purchase Order Log and the Archive PDF output, relative to your system. If you try and use the root of C:\ or any protected system folders the code will not run
  • The worksheet is protected except for editable cells, but there is no password. If unprotected, the worksheet will re-protect itself during various actions performed by the user

The Process Explained:

  • The user opens Purchase Requisition Form.xlsx. The PO number automatically increments by one & the file saves
  • User enters the details as required to the form. Once completed, the user his the Generate button
  • Email is generated & a PDF copy attached. Email is addressed To: {Approval field}; Cc: {Requested By field}
  • If there is a value inserted to the Service Request field, then our servicedesk@company.com address is CC’d to the email, and the SR# is added to the subject line for email integration
  • The PDF is written to disk, using the variables from the spreadsheet & today’s date/time for the filename to ensure no accidental file overwrites
  • The summary on the ‘Data’ worksheet is copied & pasted to the Purchase Order Log.xlsx sheet. You can hide the ‘Data’ worksheet once you’ve finished modifying anything
  • The visible portion of the purchase order is copied as a range & pasted to the email ready to send
  • User closes the workbook without saving changes (leaving the blank template ready for other staff)
  • In order to raise two consecutive PO’s, simply hit the ‘Reset Fields’ button to wipe input data, and increment to the next PO number

The Code:

I have added comments throughout the code which will assist you in editing this to fit your requirements. There code is broken down on the prior version of this post here, it’s not changed enough to warrant me reposting the same  information. To edit the VBA, press ALT+F11 in Excel. The different parts of the code can be found in the following locations:

Microsoft Excel Objects > ThisWorkbook
Autorun macros

Modules > Module1
Required fields validation
Create email
Save archive PDF
Update log

Modules > Module2
Reset form button

Modules > Module 4:
Add Row / Delete Row

The empty form:

PReq1a

Once completed, the generated email:

PReq2

 

 

 

 

Posted in Macro, MS Excel, VBA | 2 Comments

Scripting Automatic Uninstall of Internet Explorer 10 and 11

I had to find a way of rolling back a bunch of Windows 7 workstations to Internet Explorer 9, due to a vendor’s specified supported platform. I thought scripting  an automatic uninstall via Group Policy to remove the Internet Explorer 10 and 11 KB updates on logon would be the best way to do this.

The requirements for this were:

  • Automatic silent un-installation
  • If a user manually upgrades Internet Explorer, automatically running again to remove the update
  • Some basic output for troubleshooting
  • Ignores 64bit OS-es

Below is the batch script I wrote, which checks the registry keys to find the installed version of Internet Explorer, and then if found, issues the relevant uninstall command. It writes a couple of files locally to check if it’s been run before, and keeps a time stamped log on a central file share of each action taken against each machine.

Also, used the Microsoft ADM templates as part of the same policy to prevent automatic redeployment of IE 10 and IE 11 via Windows Updates:

Toolkit to Disable Automatic Delivery of Internet Explorer 11
http://www.microsoft.com/en-au/download/details.aspx?id=40722

Toolkit to Disable Automatic Delivery of Internet Explorer 10
http://www.microsoft.com/en-us/download/details.aspx?id=36512

The script will check through the registry for the value of two registry keys which contain the current version number of Internet Explorer:
HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer\Version
HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer\SvcVersion

Once the version number is determined, the appropriate action is taken:

  • Version 11: Silently issue uninstall of KB2841134​, rolling the computer back to IE9
  • Version 10: Silently issue uninstall of KB2718695, rolling the computer back to IE9
  • Version 9: No action taken, script logs an exit & the values matched
  • Version 8: As above. WSUS will push out IE9 to this machine if it hasn’t done already

The script also writes some text files to a local directory, under C:\TEMP\. The script uses these files as flags to determine if it needs to run again or not.

If a previously uninstalled V10 or V11 is upgraded again, the script should detect this and reset these flags.

Because of the way Internet Explorer updates apply, expected behaviour of this is as follows (assuming a user has just installed IE11 manually):

  • User logs on, already has V9 installed. Script runs & detects no action is required
  • User installs IE10 or IE11. Reboot is required to finalise installation (if user does not reboot, they will have IE10 / IE11 for the duration of this session)
  • User reboots computer
  • Windows presents ‘Configuring updates’ on shutdown & again on start-up to apply IE10 / IE11
  • Computer presents CTRL+ALT+DEL. Script runs, detects & uninstalls upgraded IE10 / IE11
  • When the user logs back in, they will have IE10 or IE11 available to them, however it has already been uninstalled (just not applied via Windows Updates yet)
  • User reboots
  • Windows presents ‘Configuring updates’ on shutdown & again on start-up to apply IE9
  • Computer presents CTRL+ALT+DEL. Script runs and detects IE9 installed and no action required
  • User logs in, and is presented with IE9
setlocal ENABLEEXTENSIONS

REM Set current date & time
for /f "tokens=1-3 delims=/- " %%a in ('date /t') do set XDate=%%a-%%b-%%c 
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a.%%b 

IF NOT EXIST "C:\TEMP" (MKDIR C:\TEMP\)

Echo Beginning script @ %XDate% %XTime% on machine %COMPUTERNAME% >> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt


:OSTest
IF EXIST "%PROGRAMFILES(X86)%" (GOTO 64BIT) ELSE (GOTO 32BIT)


:64BIT
Echo 64Bit OS detected, exiting.. >> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt
exit


:32BIT
Echo 32Bit OS detected, continuing... >> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt
 

:CHECKVER
REM Checking HKLM\Software\Microsoft\Internet Explorer\Version key value and setting versionNumber value

set "KEY_NAME=HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer"
set "VALUE_NAME=Version"
For /F "tokens=2*" %%A IN ('REG QUERY "%KEY_NAME%" /v "%VALUE_NAME%"') Do (
 set "versionNumber=%%B"
)
for /f "delims=." %%a in ("%versionNumber%") do set "versionNumber=%%a"
Echo Internet Explorer v%versionNumber% detected in reg key 'VERSION'>> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt


:CHECKVER1
REM Checking HKLM\Software\Microsoft\Internet Explorer\SvcVersion key value and setting versionNumber1 value

set "KEY_NAME=HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer"
set "VALUE_NAME=svcVersion"
For /F "tokens=2*" %%A IN ('REG QUERY "%KEY_NAME%" /v "%VALUE_NAME%"') Do (
 set "versionNumber1=%%B"
)
for /f "delims=." %%a in ("%versionNumber1%") do set "versionNumber1=%%a"
Echo Internet Explorer v%versionNumber1% detected in reg key 'SVCVERSION' >> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt

:CHECKER
REM Checks if previous uninstallation has been reverted. If so, deletes .txt flags from computer to allow script to run again. If registry returns 8 or 9 for either value & the .txt file exists, file will exit.
if %versionNumber1% GEQ 11 del C:\Temp\IE*.txt
if %versionNumber% GEQ 11 del C:\Temp\IE*.txt
if %versionNumber1% GEQ 10 del C:\Temp\IE*.txt
if %versionNumber% GEQ 10 del C:\Temp\IE*.txt
IF EXIST "C:\TEMP\IE8.txt" GOTO EOF
IF EXIST "C:\TEMP\IE9.txt" GOTO EOF
IF EXIST "C:\TEMP\IE10.txt" GOTO V9
IF EXIST "C:\TEMP\IE11.txt" GOTO V10


:V11
REM Set date & Time, then issue uninstall commands if VERSION or SVCVERSION values are 11

for /f "tokens=1-3 delims=/- " %%a in ('date /t') do set XDate=%%a-%%b-%%c 
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a.%%b

if %versionNumber1% GEQ 11 wusa /uninstall /KB:2841134 /quiet /norestart
if %versionNumber1% GEQ 11 Echo %XDate% %XTime% Issued uninstall of KB2841134 (IE v11) due to VALUE_NAME=SVCVERSION=%versionNumber1% >> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt

if %versionNumber% GEQ 11 wusa /uninstall /KB:2841134 /quiet /norestart
if %versionNumber% GEQ 11 Echo %XDate% %XTime% Issued uninstall of KB2841134 (IE v11) due to VALUE_NAME=Version=%versionNumber% >> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt

if %versionNumber1% GEQ 11 Echo %XDate% %XTime% Issued uninstall of IE v11. Please check \\SERVERNAME\iescripts\%computername%.txt for verbose logging >> C:\TEMP\IE11.txt
if %versionNumber% GEQ 11 Echo %XDate% %XTime% Issued uninstall of IE v11. Please check \\SERVERNAME\iescripts\%computername%.txt for verbose logging >> C:\TEMP\IE11.txt

if %versionNumber1% GEQ 11 GOTO EOF
if %versionNumber% GEQ 11 GOTO EOF


:V10
REM Set date & Time, then issue uninstall commands if VERSION or SVCVERSION values are 10

for /f "tokens=1-3 delims=/- " %%a in ('date /t') do set XDate=%%a-%%b-%%c 
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a.%%b

if %versionNumber1% GEQ 10 wusa /uninstall /KB:2718695 /quiet /norestart
if %versionNumber1% GEQ 10 Echo %XDate% %XTime% Issued uninstall of KB2718695 (IE v10) due to VALUE_NAME=SVCVERSION=%versionNumber1% >> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt

if %versionNumber% GEQ 10 wusa /uninstall /KB:2718695 /quiet /norestart
if %versionNumber% GEQ 10 Echo %XDate% %XTime% Issued uninstall of KB2718695 (IE v10) due to VALUE_NAME=Version=%versionNumber% >> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt

if %versionNumber1% GEQ 10 Echo %XDate% %XTime% Issued uninstall of IE v10. Please check \\SERVERNAME\iescripts\%computername%.txt for verbose logging >> C:\TEMP\IE10.txt
if %versionNumber% GEQ 10 Echo %XDate% %XTime% Issued uninstall of IE v10. Please check \\SERVERNAME\iescripts\%computername%.txt for verbose logging >> C:\TEMP\IE10.txt

if %versionNumber1% GEQ 10 GOTO EOF
if %versionNumber% GEQ 10 GOTO EOF


:V9
REM If version 9 is detected by this point, log and exit
if %versionNumber1% GEQ 9 Echo v%versionNumber1% detected - exiting without changes. VALUE_NAME=SvcVersion=%versionNumber1%. Please check \\SERVERNAME\iescripts\%computername%.txt for verbose logging >> C:\TEMP\IE9.txt
if %versionNumber% GEQ 9 GOTO EOF

:V8

REM If version 8 is detected by this point, log and exit
Please check \\SERVERNAME\iescripts\%computername%.txt for verbose logging >> C:\TEMP\IE8.txt
if %versionNumber% GEQ 8 GOTO EOF

:EOF
for /f "tokens=1-3 delims=/- " %%a in ('date /t') do set XDate=%%a-%%b-%%c
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a.%%b
Echo Finished script @ %XDate% %XTime% >> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt
Echo ====================================================================================== >> \\SERVERNAME\IEScripts\%COMPUTERNAME%.txt
exit

 

Posted in Batch, Group Policy | Tagged , , | Leave a comment

Rename Profile Batch Script – Citrix / Terminal Server

Some time ago, after finding myself manually removing/renaming user profiles from several servers as well as a roaming profile share, I wrote the below batch script to enable me to perform the rename profile function quickly & easily.

The server names are all defined early on, the only input required is typing the domain username & confirming entry is correct, before the profiles are renamed.

The script will rename each found profile, and rename it to %username%.OLD.%date% in order to protect any data contained within.

@echo off
setLocal EnableDelayedExpansion

:menu1    
echo [Enter Username To Delete Profile From Citrix:]

echo.    
set /p pr=    
echo.    


:menu2
setLocal EnableDelayedExpansion
echo [Username to remove: %pr%. Is this correct?]
echo 1 for Yes   
echo 2 for No  
echo.    
set /p rr=    
echo.   
if %rr% neq 1 goto :menu1


:begin 
SET SERVER1=CITRIX1
SET SERVER2=CITRIX2
SET SERVER3=CITRIX3
SET SERVER4=CITRIX4
SET SERVER5=\\server\citrixroaming


:server1
for /f "tokens=1-3 delims=/- " %%a in ('date /t') do set XDate=%%a-%%b-%%c 
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a.%%b 
if exist "\\%SERVER1%\C$\Documents and Settings\" goto exist1 
echo PATH NOT FOUND on %SERVER1%, PROGRESSING....
goto Server2

:exist1 
echo PATH CONFIRMED 
pushd \\%SERVER1%\C$\Documents and Settings\ 
goto skipit1 

:skipit1
if exist "\\%SERVER1%\C$\Documents and Settings\%pr%" goto found1
echo ID NOT FOUND ON %SERVER1%....
goto server2

:found1
Echo modifying... 
rename "%pr%" "%pr%"."%XDate%" 
Echo ***SUCCESS*** 
popd 
Echo Renamed to %pr%.%XDate%ON %SERVER1%....
goto server2


:server2
for /f "tokens=1-3 delims=/- " %%a in ('date /t') do set XDate=%%a-%%b-%%c 
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a.%%b 
if exist "\\%SERVER2%\C$\Documents and Settings\" goto exist2
echo PATH NOT FOUND on %SERVER2%, PROGRESSING....
goto Server3

:exist2
echo PATH CONFIRMED 
pushd \\%SERVER2%\C$\Documents and Settings\ 
goto skipit2

:skipit2
if exist "\\%SERVER2%\C$\Documents and Settings\%pr%" goto found2
echo ID NOT FOUND ON %SERVER2%....
goto server3

:found2
Echo modifying... 
rename "%pr%" "%pr%"."%XDate%" 
Echo ***SUCCESS*** 
popd 
Echo Renamed to %pr%.%XDate%ON %SERVER2%....
goto server3


:server3
for /f "tokens=1-3 delims=/- " %%a in ('date /t') do set XDate=%%a-%%b-%%c 
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a.%%b 
if exist "\\%SERVER3%\C$\Documents and Settings\" goto exist3
echo PATH NOT FOUND on %SERVER3%, PROGRESSING....
goto Server4

:exist3
echo PATH CONFIRMED 
pushd \\%SERVER3%\C$\Documents and Settings\ 
goto skipit3

:skipit3
if exist "\\%SERVER3%\C$\Documents and Settings\%pr%" goto found3
echo ID NOT FOUND ON %SERVER3%....
goto server4

:found3
Echo modifying... 
rename "%pr%" "%pr%"."%XDate%" 
Echo ***SUCCESS*** 
popd 
Echo Renamed to %pr%.%XDate%ON %SERVER3%....
goto server4


:server4
for /f "tokens=1-3 delims=/- " %%a in ('date /t') do set XDate=%%a-%%b-%%c 
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a.%%b 
if exist "\\%SERVER4%\C$\Documents and Settings\" goto exist4
echo PATH NOT FOUND on %SERVER4%, PROGRESSING....
goto Server5

:exist4
echo PATH CONFIRMED 
pushd \\%SERVER4%\C$\Documents and Settings\ 
goto skipit4

:skipit4
if exist "\\%SERVER4%\C$\Documents and Settings\%pr%" goto found4
echo ID NOT FOUND ON %SERVER4%....
goto server5

:found4
Echo modifying... 
rename "%pr%" "%pr%"."%XDate%" 
Echo ***SUCCESS*** 
popd 
Echo Renamed to %pr%.%XDate%ON %SERVER4%....
goto server5


:server5
for /f "tokens=1-3 delims=/- " %%a in ('date /t') do set XDate=%%a-%%b-%%c 
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a.%%b 
if exist "%SERVER5%" goto exist5
echo PATH NOT FOUND on %SERVER5%..
goto final

:exist5
echo PATH CONFIRMED 
pushd %SERVER5%\ 
goto skipit5

:skipit5
if exist "%SERVER5%\%pr%.domain" goto found5
echo ID NOT FOUND ON %SERVER5%...
goto final

:found5
Echo modifying... 
rename "%pr%.domain" "%pr%"OLD.OLD."%XDate%" 
Echo ***SUCCESS*** 
popd 
Echo Renamed to %pr%.domain.%XDate%ON %SERVER5%....

goto final

:final
Echo COMPLETED!!
Pause

 

Posted in Batch, Profile, Script | Leave a comment

System Uptime / Availability Calculator Template

I created this System Uptime / Downtime / Availability Calculator Template as an easy way to work out the effect of any outage on our Service Level Agreements. This template is using Australian sites & timezones as this was what I needed, but the logic is pretty clear & you can easily adapt it to suit your requirements. All times are calculated in WST, so please be sure to input your outage start & end times in WST. Leave me a comment if you have found this useful when managing your SLA’s.

Basically, the intention here is to count the number of business hours (ignoring weekends) that any outage affected multiple sites with different opening & closing times.

You will need to load the Analysis Toolpak to Excel to utilise the NetworkDays function listed below: http://office.microsoft.com/en-au/excel-help/load-the-analysis-toolpak-HP001127724.aspx

From this example, you can see that the a two hour outage occurring between 6 and 8am WST has no impact on the Perth office, as the outage occurred before the start of business in that office. Adelaide recorded a lower outage duration, as they are half an hour behind EST and therefore were only affected for 1.5 hours:

image001

The SpreadSheet Explained
When a time & date are entered into the top fields, the times are calculated into three sections underneath: The corresponding times in Adelaide, Brisbane and EST. Logic is then applied to these converted values in order to ensure that any input values fall within the specified 8am to 5pm range. Without this validation a 5pm WST outage will clock in as 7pm Brisbane time (for example) and the formula will calculate a higher Downtime, as it counts the time overnight as part of the Outage

image003

Variables
To keep the formulas modular, the variables are specfied in this section & used as references within the rest of the spreadsheet. This allows us to easily change the time difference to Melbourne / Sydney when the clocks change, as well as the number of hours per working week, and the opening / closing times we are measuring against:

image002

Logic
StartTime = Office’s opening time
EndTime = Office’s closing time
Converted Start Time: If StartTime earlier than OpenTime, then OpenTime. If StartTime later than CloseTime, then CloseTime. Otherwise, the input value is used.
Converted End Time: If EndTime later than CloseTime, then CloseTime. If EndTime earlier than StartTime, then StartTime. Otherwise, the input value is used.
Actual End Time: The WST end time of the incident, plus or minus the time difference specified in the Variables column

Forumula
The main calculation is using Excel’s ‘NetworkDays’ function to ignore weekends, and uses the DayEnd & DayStart times input into the spreadsheet to calculate the actual business hours each outage affected.

image004

Users
The Users section is not used in any formulas above, however it is intended to make it easier to calculate the true impact of any outage. For example, a 2 hour outage will incur a 1.3% downtime (98.7% uptime) for all sites, assuming the outage falls within the open & close times of all sites. However if only one site (29% of users) was affected by the outage the true downtime would be:

image005

Users (29%) x Downtime (1.3%) = 0.38% actual downtime (99.62% uptime) for the entire organization over the course of the month.

The user-specified fields are in orange, and roll up into the total via AutoSum at the top. The AutoSum value is then used as 100% of all users, which is then broken down to the site percentage of the total.

Download Uptime Calculator.xlsx (21kb):
Uptime Calculator

Posted in MS Excel | Tagged , , , , , , | 1 Comment