Monday, October 11, 2010

Saving an Excel file using Powershell

I encountered a situation where I want to open an Excel sheet and write some information using Powershell commands and then close the file without my knowledge. I tried out several options and finally came up with the following code.

Before executing the script, you need to create an Excel file in root of the D drive with the name of “TestSave”. You may use any other location if so change the script accordingly.

$Exl = new-object -comobject Excel.Application
$Exl.visible=$false
$Exl.displayalerts=$False
$wb = $Exl.workbooks.Open("D:\TestSave.xlsx")
$ws = $wb.worksheets.item("Sheet1")
$ws.Cells.Item(1,3)='2009'
$wb.SaveAs("D:\TestSave.xlsx")
$Exl.Quit()

Thursday, March 25, 2010

How to get logical drive information

As a DBA you might want to get storage information of a server. It may be a local server, remote server or multiple servers. 


Lets assume you want to create a report with the format given below;


[DeviceID, VolumeName, Size, FreeSpace]


If it is single server you may be able to prepare the report easily by going though the "My Computer". What if the report needs to prepared for 20 servers. 


You can create a powershell script for this task. I've created the below script to extract logical drive information for a given server and put the output in an Excel file.