Posts

Showing posts from June, 2014

Excel VBA create UNC Path as HyperLink

A simple code snippet to create hyperlink on excel.

Excel hyperlink to a shared folder using UNC Path or  URL.

=============================

Sub create_path()

'Add UNC Path to a Cell via VBA
Dim xPath As String

xPath = "\\myserver\myshared_folder\"

'or add a URL
'xPath = "http://www.google.com"

    With Worksheets(1)
                   .Hyperlinks.Add Anchor:=.Range("B1"), _
                    Address:=xPath, _
                    ScreenTip:="Click to open the link", _
                    TextToDisplay:="Link to this path:->" & xPath
    End With

End Sub

=============================

How to lock cells in Excel using VBA

There’s a lot of ways in Excel to lock cells using VBA or even without VBA.
But would it be nice to lock a cell that would give the user a warning.
Something like, test the Excel user whether they are able to follow a simple instruction.
Like, please don’t change the value of the next cell.
I’m sure the user will be pretty curious, and will change it.
So the VBA will monitor if the cell has been change, and prompt the user that the cell has been changed.
And of course put back the original value. And display a message to intimidate the user like, “You’ve been warn not to changed. It’s useless, reverting back to original value”.
But of course to lock a cell it would be better to do it using the proper way, but for funny moments this piece of code would be fun.
The call is yours, if you want to implement this code on the production. It works well, anyway. . Try this on a grumpy user and just watch the reaction, whether he/she will smack the computer.
Here’s the code, I won’t give a de…

PowerShell check hard drive space

Image
Check hard drive space to avoid unnecessary downtime just because of space problem.

PowerShell scripts will come handy to check hard drive space and run on task scheduler at a specified time.

Technet articles already have codes on how to do it, but it may not suit the requirements you want to achieve.
So, tweak the code and creativity come in.
Truncate FreeSpace String Windows PowerShell Tip of the Week http://technet.microsoft.com/en-us/library/ee692684.aspx
Get-WMIObject Win32_LogicalDisk | ForEach-Object {[math]::truncate($_.freespace / 1GB)}
For detailed explanation for the above command, check the Technet link. 
Scripting Guy PowerTip: Use PowerShell to Get a List of All Volumes http://blogs.technet.com/b/heyscriptingguy/archive/2013/08/28/powertip-use-powershell-to-get-a-list-of-all-volumes.aspx
Command below list volumes or drives available on the computer taken from above link. This command list the drive letter of network mapped drives, local drives or external USB storage.
  GE…

Learn Linux vi the easy way

Image
Open Linux terminal window.

Type "vi filename.mytxt" to start a new session.
(filename extension is not important but for readability purposes only)

Inside vi window, press "insert" key on the keyboard or press letter "i".

If  insert is not press you cannot start typing on vi editor.

"Enter" key is used to insert new line.

Press "Esc" key once. And use arrow keys (up/down or left/right) to navigate.

Or use any of the keys below:

While pressing specified keys below, you will not be able to see the characters on the screen but it will just execute the process.

What I mean, if you press "dd"; dd will not be type or seen on the terminal screen window.

But once the last character is pressed, the line will be deleted.

Press home or end key to navigate to the beginning or end of the line.

To copy a line, press "yy" and press "p" to paste the line.

Press "cc" to cut a line, "p" to paste the li…

PowerShell Query in Active Directory

Query a user account in Active Directory using PowerShell.

This will need to have Active Directory module loaded on PowerShell.

Code is:

Get-ADUser 'xMenUser' -properties PasswordLastSet,whenCreated | select UserPrincipalName,whenCreated,PasswordLastSet | Format-List

This line select UserPrincipalName,whenCreated,PasswordLastSet  instruct PowerShell that only this object properties are needed on the output.

A convoluted output which is hard to read, will take a lot of time to get what is needed.

So it's good to be specific and just get what is actually needed.

This piece of code below queries Active Directory for the specified user account.

And it will display the properties of the user account being queried and displays its UPN name, when the account was created and when the last time the password was set.

Get-ADUser 'xMenUser' -properties PasswordLastSet,whenCreated | select UserPrincipalName,whenCreated,PasswordLastSet | Format-List

Sample Output below:

UserPrinci…

PowerShell Inventory Script

This piece of code snippet below will use PowerShell cmdlet to get Computer Name,PC Model, Serial Number and the current logged on user.      

Below are the one liner codes to get the Computer Name,PC Model, Serial Number and the current logged on user.

#Will select/get the computer name from WMI Object (win32_Computersystem)
get-wmiobject win32_computersystem | Select Name

#Will select/get the PC Model from WMI Object (win32_Computersystem)
get-wmiobject win32_computersystem | select Model

#Will select/get the serial number from WMI Object (win32_Computersystemproduct)
#I just notice that the IdentifyingNumber as it is named by MS is equal to the serial number of the PC
get-wmiobject win32_computersystemproduct  | select IdentifyingNumber

#Will select/get the currently logged on user from WMI Object (win32_Computersystem)
get-wmiobject win32_computersystem -computer . | select username        


Here's the whole PowerShell code, which will get the data and display the output in one line.

PowerShell Set Computer Description in Active Directory

In PowerShell, complexity is hidden in the background.
Administrator or PowerShell users can do more with less coding.
Code example below will set computer description in Active Directory via a text file.
Text file name is “computers.txt”  in D drive location. (Of course this file can be located anywhere and any file name as well.)
Anyway for the sake of this example, the file name is “computers.txt” and file is located on root of D drive.
Text file structure should be something like this:
Comp1A-User1A Comp1B-User1B
In which, Comp1A is the computer name and User1A is the computer description. "-" is jut a delimiter for the two unique value.
PowerShell Script below uses Get-ADComputer cmdlet to get the reference of the Computer object and Set-ADComputer to set the data to computer object property.
Since the Text file is separated by "-". Computer Name-Computer Description
This line below will split the data into array.
$TxtFileVar = $_ -split '-'   Write-Host …

Batch file script lock down a computer

Ever in need to lock your computer via batch file script?
On Windows 7 and Windows 8 this can be done within just a few lines of batch file script.
The script can be set using the task scheduler and run the batch file script at specified time.
The script will give an option to the user to abort or to continue locking down the pc or workstation.
Choice to abort or continue is within 5 seconds. Of course the time can be changed to any desired time.
After 5 seconds, user will need to press Ctrl + C to abort the script and is given 10 seconds.
The script will continue to lock the computer if not aborted.
This could be useful on a scenario where the user always leave the desk, or for unmanaged computer that needs to be lock at a specified time.

Here's the piece of code snippet using batch file script to lock the computer:
================================= 
@echo off echo "This computer will be lock within 15 seconds, Enter your choice within 5 seconds. Press "a" to abort &…

PowerShell get running services

PowerShell provides an easy way to get services on Windows operating system.
The cmdlet to get services on Windows is quite simple, just type: Get-Service

To filter out running or stopped services type this command below:
Get-Service | Where-Object {$_.Status -match 'Running'} Get-Service | Where-Object {$_.Status -match 'Stopped'}

To check the status for a particular service, type this command below:
$xService = Get-Service 'BFE'

write-output $xService

Output of the command above: Status   Name               DisplayName                           ------      ----                 -----------                           Running  BFE                Base Filtering Engine 
To get a specific column from the output, specify the column number.

$outputx = Get-Service 'BFE' | Format-Wide -Property Status -Column 1

Write-Output $outputx
or use this command below: (see comments below from  Jeffrey Snover)

Thanks Jeff!

$outputx = (Get-Service 'BFE').Status 

It will…

PowerShell Help Syntax

If you are new to PowerShell or either you've been working for quite some time on Windows PowerShell one way or another you will still need to look or check the help statement.

Why would you need help on PowerShell if you consider yourself as an expert?

Well, if you can't lower your ego or pride and you have the guts to remember 2,300 or more cmdlets then you really don't to check the help statement.

As for me, Help statement  in PowerShell is always my companion.

Syntax of Help Statement in PowerShell:

Get-Help Name_of_CmdLet 

Like:

Get-Help Get-ChildItem

But to have a more detailed or helpful output.

Type:

Get-Help Get-ChildItem -Example

Above command will show examples of Get-ChildItem cmdlet.

You can also use the -detail parameter which will show examples and some description:

Get-Help Get-ChildItem -Detail

Or type:

Get-Help Get-ChildItem -Full

Basically, the parameters for help are: -Example -Detail and -Full



And another helpful command in PowerShell is Get-Alias

Aliases …