Copy Data From One Spreadsheet to Another

Copies data from one Excel spreadsheet to another.

PowerShell

$comments = @'  
Script name: Copy-ExcelData.ps1  
Created on: Wednesday, August 22, 2007  
Author: Kent Finkle  
Purpose: How can I use Windows Powershell to  
Copy Data From One Spreadsheet to Another?  
'@  
# -----------------------------------------------------  
function Release-Ref ($ref) {  
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(  
[System.__ComObject]$ref) -gt 0)  
[System.GC]::Collect()  
[System.GC]::WaitForPendingFinalizers()  
}  
# -----------------------------------------------------  
$xl = new-object -comobject excel.application  
$xl.Visible = $True  
$wb = $xl.Workbooks.Add()  
$ws = $wb.Worksheets.Item("Sheet1")  
  
$2d = new-object 'object[,]' 20,1  
 
for ($i = 0; $i -le 19; $i++) {  
    for ($j = 0; $j -le 0; $j++) {  
        $2d[$i,$j] = $i  
    }  
}  
  
$r = $ws.Range("A1:A20")  
$r.Value() = $2d  
$a = $r.Copy()  
  
$xl2 = new-object -comobject excel.application  
$xl2.Visible = $True  
$wb2 = $xl2.Workbooks.Add()  
$ws2 = $wb2.Worksheets.Item("Sheet1")  
$a = $ws2.Paste()  
  
$a = Release-Ref($ws2)  
$a = Release-Ref($wb2)  
$a = Release-Ref($xl2)  
$a = Release-Ref($ws)  
$a = Release-Ref($wb)  
$a = Release-Ref($xl) 

Verified on the following platforms

 

Windows Server 2008 R2	No
Windows Server 2008	No
Windows Server 2003	No
Windows 7	No
Windows Vista	No
Windows XP	No
Windows 2000	No

This script is tested on these platforms by the author. It is likely to work on other platforms as well. If you try it and find that it works on another platform, please add a note to the script discussion to let others know.

Delete duplicate rows in excel using advanced filter

Tags: delete, Excel, duplicate, rows, filter, advanced filter

Visual Basic

'Delete duplicate rows in excel using advanced filter 
'Francis de la Cerna 
' 
'I've been doing this the long way for, well, a long time. 
'Sorry, excel, I didn't know you could do that. 
 
 
 
set xl  = createobject("excel.application") 
set wb  = xl.workbooks.add 
set ws1 = wb.worksheets("sheet1") 
set ws2 = wb.worksheets("sheet2") 
 
xl.visible = true 
 
 
 
'add some data to sheet1 
 
ws1.range("a1:c1").value = array("a", "b", "c") 
ws1.range("a2:c2").value = array( 1  , 2  , 3 ) 
ws1.range("a3:c3").value = array( 4  , 5  , 6 ) 
ws1.range("a4:c4").value = array( 7  , 8  , 9 ) 
ws1.range("a5:c5").value = array( 4  , 5  , 6 ) 
ws1.range("a6:c6").value = array( 1  , 2  , 3 ) 
ws1.range("a7:c7").value = array("a", "b", "c") 
 
 
 
'filter unique records and copy result in sheet2 starting in cell a1 
'i think the 1st row is considered as a header row 
 
xlFilterInPlace = 1 
xlFilterCopy    = 2 
uniqueRecords   = true 
 
ws1.range("a1:c7").advancedFilter xlFilterCopy, , ws2.range("a1"), uniqueRecords 

Verified on the following platforms

 

Windows Server 2008 R2	No
Windows Server 2008	No
Windows Server 2003	No
Windows 7	No
Windows Vista	No
Windows XP	Yes
Windows 2000	No

This script is tested on these platforms by the author. It is likely to work on other platforms as well. If you try it and find that it works on another platform, please add a note to the script discussion to let others know.

Computer Hardware Scripts: Retrieving System Information

Retrieving System Information

INVENTORY OF A COMPUTER WITH WINDOWS

Pripared by Laxman,

Uses WMI to retrieve the same data found in the System Information applet.

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

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSettings = objWMIService.ExecQuery _
    ("Select * from Win32_OperatingSystem")
For Each objOperatingSystem in colSettings 
    Wscript.Echo "OS Name: " & objOperatingSystem.Name
    Wscript.Echo "Version: " & objOperatingSystem.Version
    Wscript.Echo "Service Pack: " & _
        objOperatingSystem.ServicePackMajorVersion _
            & "." & objOperatingSystem.ServicePackMinorVersion
    Wscript.Echo "OS Manufacturer: " & objOperatingSystem.Manufacturer
    Wscript.Echo "Windows Directory: " & _
        objOperatingSystem.WindowsDirectory
    Wscript.Echo "Locale: " & objOperatingSystem.Locale
    Wscript.Echo "Available Physical Memory: " & _
        objOperatingSystem.FreePhysicalMemory
    Wscript.Echo "Total Virtual Memory: " & _
        objOperatingSystem.TotalVirtualMemorySize
    Wscript.Echo "Available Virtual Memory: " & _
        objOperatingSystem.FreeVirtualMemory
    Wscript.Echo "OS Name: " & objOperatingSystem.SizeStoredInPagingFiles
Next
Set colSettings = objWMIService.ExecQuery _
    ("Select * from Win32_ComputerSystem")
For Each objComputer in colSettings 
    Wscript.Echo "System Name: " & objComputer.Name
    Wscript.Echo "System Manufacturer: " & objComputer.Manufacturer
    Wscript.Echo "System Model: " & objComputer.Model
    Wscript.Echo "Time Zone: " & objComputer.CurrentTimeZone
    Wscript.Echo "Total Physical Memory: " & _
        objComputer.TotalPhysicalMemory
Next
Set colSettings = objWMIService.ExecQuery _
    ("Select * from Win32_Processor")
For Each objProcessor in colSettings 
    Wscript.Echo "System Type: " & objProcessor.Architecture
    Wscript.Echo "Processor: " & objProcessor.Description
Next
Set colSettings = objWMIService.ExecQuery _
    ("Select * from Win32_BIOS")
For Each objBIOS in colSettings 
    Wscript.Echo "BIOS Version: " & objBIOS.Version
Next

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

Save the script as Filename.vbs, And just double click on that.

Computer Hardware Scripts: Retrieving BIOS Information

INVENTORY OF A COMPUTER WITH WINDOWS
Pripared by Laxman,

Retrieves BIOS information for a computer, including BIOS version number and release date.

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

strComputer = "."

Set objWMIService = GetObject("winmgmts:" _

    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colBIOS = objWMIService.ExecQuery _

    ("Select * from Win32_BIOS")

For each objBIOS in colBIOS

    Wscript.Echo "Build Number: " & objBIOS.BuildNumber

    Wscript.Echo "Current Language: " & objBIOS.CurrentLanguage

    Wscript.Echo "Installable Languages: " & objBIOS.InstallableLanguages

    Wscript.Echo "Manufacturer: " & objBIOS.Manufacturer

    Wscript.Echo "Name: " & objBIOS.Name

    Wscript.Echo "Primary BIOS: " & objBIOS.PrimaryBIOS

    Wscript.Echo "Release Date: " & objBIOS.ReleaseDate

    Wscript.Echo "Serial Number: " & objBIOS.SerialNumber

    Wscript.Echo "SMBIOS Version: " & objBIOS.SMBIOSBIOSVersion

    Wscript.Echo "SMBIOS Major Version: " & objBIOS.SMBIOSMajorVersion

    Wscript.Echo "SMBIOS Minor Version: " & objBIOS.SMBIOSMinorVersion

    Wscript.Echo "SMBIOS Present: " & objBIOS.SMBIOSPresent

    Wscript.Echo "Status: " & objBIOS.Status

    Wscript.Echo "Version: " & objBIOS.Version

    For i = 0 to Ubound(objBIOS.BiosCharacteristics)

        Wscript.Echo "BIOS Characteristics: " & _

            objBIOS.BiosCharacteristics(i)

    Next

Next

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

Save the script as Filename.vbs, And just double click on that.

Identifying Processor Type & Inventorying Computer Hardware

INVENTORY OF A COMPUTER WITH WINDOWS
Pripared by Laxman,

Identifying Processor Type

Determines the processor architecture (such as x86 or ia64) for a specified computer.
========================


strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set objProcessor = objWMIService.Get("win32_Processor='CPU0'")

If objProcessor.Architecture = 0 Then
    Wscript.Echo "This is an x86 computer."
ElseIf objProcessor.Architecture = 1 Then
    Wscript.Echo "This is a MIPS computer."
ElseIf objProcessor.Architecture = 2 Then
    Wscript.Echo "This is an Alpha computer."
ElseIf objProcessor.Architecture = 3 Then
    Wscript.Echo "This is a PowerPC computer."
ElseIf objProcessor.Architecture = 6 Then
    Wscript.Echo "This is an ia64 computer."
Else
    Wscript.Echo "The computer type could not be determined."
End If
========================
Save the script as Filename.vbs, And just double click on that.

Inventorying Computer Hardware

Inventorying Computer Hardware

Returns information about the pointing devices installed on a computer. Used as an example of how to retrieve hardware information using WMI.
=========================


strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colMice = objWMIService.ExecQuery _
    ("Select * from Win32_PointingDevice")
For Each objMouse in colMice
    Wscript.Echo "Hardware Type: " & objMouse.HardwareType
    Wscript.Echo "Number of Buttons: " & objMouse.NumberOfButtons    
    Wscript.Echo "Status: " & objMouse.Status
    Wscript.Echo "PNP Device ID: " & objMouse.PNPDeviceID
Next
========================

Save the script as Filename.vbs, And just double click on that.

Computer Hardware Scripts: Enumerating Serial Port Properties

INVENTORY OF A COMPUTER WITH WINDOWS
Pripared by Laxman,


Returns information about the serial ports installed on a computer. 
==================


On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_SerialPort",,48)
For Each objItem in colItems
    Wscript.Echo "Binary: " & objItem.Binary
    Wscript.Echo "Description: " & objItem.Description
    Wscript.Echo "Device ID: " & objItem.DeviceID
    Wscript.Echo "Maximum Baud Rate: " & objItem.MaxBaudRate
    Wscript.Echo "Maximum Input Buffer Size: " & objItem.MaximumInputBufferSize
    Wscript.Echo "Maximum Output Buffer Size: " & objItem.MaximumOutputBufferSize
    Wscript.Echo "Name: " & objItem.Name
    Wscript.Echo "OS Auto Discovered: " & objItem.OSAutoDiscovered
    Wscript.Echo "PNP Device ID: " & objItem.PNPDeviceID
    Wscript.Echo "Provider Type: " & objItem.ProviderType
    Wscript.Echo "Settable Baud Rate: " & objItem.SettableBaudRate
    Wscript.Echo "Settable Data Bits: " & objItem.SettableDataBits
    Wscript.Echo "Settable Flow Control: " & objItem.SettableFlowControl
    Wscript.Echo "Settable Parity: " & objItem.SettableParity
    Wscript.Echo "Settable Parity Check: " & objItem.SettableParityCheck
    Wscript.Echo "Settable RLSD: " & objItem.SettableRLSD
    Wscript.Echo "Settable Stop Bits: " & objItem.SettableStopBits
    Wscript.Echo "Supports 16-Bit Mode: " & objItem.Supports16BitMode
    Wscript.Echo "Supports DTRDSR: " & objItem.SupportsDTRDSR
    Wscript.Echo "Supports Elapsed Timeouts: " & objItem.SupportsElapsedTimeouts
    Wscript.Echo "Supports Int Timeouts: " & objItem.SupportsIntTimeouts
    Wscript.Echo "Supports Parity Check: " & objItem.SupportsParityCheck
    Wscript.Echo "Supports RLSD: " & objItem.SupportsRLSD
    Wscript.Echo "Supports RTSCTS: " & objItem.SupportsRTSCTS
    Wscript.Echo "Supports Special Characters: " & objItem.SupportsSpecialCharacters
    Wscript.Echo "Supports XOn XOff: " & objItem.SupportsXOnXOff
    Wscript.Echo "Supports XOn XOff Setting: " & objItem.SupportsXOnXOffSet
Next
===================


Save the script as Filename.vbs, And just double click on that.

Computer Hardware Scripts: Enumerating Processor Information

INVENTORY OF A COMPUTER WITH WINDOWS
Pripared by Laxman,

Returns information about the processors installed on a computer. 




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

On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor")
For Each objItem in colItems
    Wscript.Echo "Address Width: " & objItem.AddressWidth
    Wscript.Echo "Architecture: " & objItem.Architecture
    Wscript.Echo "Availability: " & objItem.Availability
    Wscript.Echo "CPU Status: " & objItem.CpuStatus
    Wscript.Echo "Current Clock Speed: " & objItem.CurrentClockSpeed
    Wscript.Echo "Data Width: " & objItem.DataWidth
    Wscript.Echo "Description: " & objItem.Description
    Wscript.Echo "Device ID: " & objItem.DeviceID
    Wscript.Echo "Ext Clock: " & objItem.ExtClock
    Wscript.Echo "Family: " & objItem.Family
    Wscript.Echo "L2 Cache Size: " & objItem.L2CacheSize
    Wscript.Echo "L2 Cache Speed: " & objItem.L2CacheSpeed
    Wscript.Echo "Level: " & objItem.Level
    Wscript.Echo "Load Percentage: " & objItem.LoadPercentage
    Wscript.Echo "Manufacturer: " & objItem.Manufacturer
    Wscript.Echo "Maximum Clock Speed: " & objItem.MaxClockSpeed
    Wscript.Echo "Name: " & objItem.Name
    Wscript.Echo "PNP Device ID: " & objItem.PNPDeviceID
    Wscript.Echo "Processor Id: " & objItem.ProcessorId
    Wscript.Echo "Processor Type: " & objItem.ProcessorType
    Wscript.Echo "Revision: " & objItem.Revision
    Wscript.Echo "Role: " & objItem.Role
    Wscript.Echo "Socket Designation: " & objItem.SocketDesignation
    Wscript.Echo "Status Information: " & objItem.StatusInfo
    Wscript.Echo "Stepping: " & objItem.Stepping
    Wscript.Echo "Unique Id: " & objItem.UniqueId
    Wscript.Echo "Upgrade Method: " & objItem.UpgradeMethod
    Wscript.Echo "Version: " & objItem.Version
    Wscript.Echo "Voltage Caps: " & objItem.VoltageCaps
Next
====================

Save the script as Filename.vbs, And just double click on that.

Computer Hardware Scripts: Enumerating the Physical Memory Configuration

Enumerating the Physical Memory Configuration

Returns information about the way physical memory is configured on a computer. 

INVENTORY OF A COMPUTER WITH WINDOWS

Pripared by Laxman,

====================
On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_PhysicalMemory",,48)
For Each objItem in colItems
    Wscript.Echo "Bank Label: " & objItem.BankLabel
    Wscript.Echo "Capacity: " & objItem.Capacity
    Wscript.Echo "Data Width: " & objItem.DataWidth
    Wscript.Echo "Description: " & objItem.Description
    Wscript.Echo "Device Locator: " & objItem.DeviceLocator
    Wscript.Echo "Form Factor: " & objItem.FormFactor
    Wscript.Echo "Hot Swappable: " & objItem.HotSwappable
    Wscript.Echo "Manufacturer: " & objItem.Manufacturer
    Wscript.Echo "Memory Type: " & objItem.MemoryType
    Wscript.Echo "Name: " & objItem.Name
    Wscript.Echo "Part Number: " & objItem.PartNumber
    Wscript.Echo "Position In Row: " & objItem.PositionInRow
    Wscript.Echo "Speed: " & objItem.Speed
    Wscript.Echo "Tag: " & objItem.Tag
    Wscript.Echo "Type Detail: " & objItem.TypeDetail
Next
=====================




Save the script as Filename.vbs, And just double click on that.

Enumerating Parallel Port Properties

Enumerating Parallel Port Properties

INVENTORY OF A COMPUTER WITH WINDOWS

Pripared by Laxman,

Returns information about the parallel ports installed on a computer. 


============================
On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_ParallelPort",,48)
For Each objItem in colItems
    Wscript.Echo "Availability: " & objItem.Availability
    For Each strCapability in objItem.Capabilities
        Wscript.Echo "Capability: " & strCapability
    Next
    Wscript.Echo "Description: " & objItem.Description
    Wscript.Echo "Device ID: " & objItem.DeviceID
    Wscript.Echo "Name: " & objItem.Name
    Wscript.Echo "OS Auto Discovered: " & objItem.OSAutoDiscovered
    Wscript.Echo "PNP Device ID: " & objItem.PNPDeviceID
    Wscript.Echo "Protocol Supported: " & objItem.ProtocolSupported
Next

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

Save the script as Filename.vbs, And just double click on that.

Computer Hardware Scripts: Enumerating IRQ Settings

Enumerating IRQ Settings
Returns information about the IRQ settings on a computer.

INVENTORY OF A COMPUTER WITH WINDOWS
Pripared by Laxman,


============================================================
On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_IRQResource")
For Each objItem in colItems
Wscript.Echo "Availability: " & objItem.Availability
Wscript.Echo "Hardware: " & objItem.Hardware
Wscript.Echo "IRQ Number: " & objItem.IRQNumber
Wscript.Echo "Name: " & objItem.Name
Wscript.Echo "Trigger Level: " & objItem.TriggerLevel
Wscript.Echo "Trigger Type: " & objItem.TriggerType
Wscript.Echo
Next
============================================================


Save the script as Filename.vbs, And just double click on that

Computer Hardware Scripts: Enumerating Computer Bus Properties.

Enumerating Computer Bus Properties.

INVENTORY OF A COMPUTER WITH WINDOWS

Pripared by Laxman,

Returns information about the computer bus.

On Error Resume Next

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colItems = objWMIService.ExecQuery("Select * from Win32_Bus")

For Each objItem in colItems

    Wscript.Echo "Bus Number: " & objItem.BusNum

    Wscript.Echo "Bus Type: " & objItem.BusType

    Wscript.Echo "Description: " & objItem.Description

    Wscript.Echo "Device ID: " & objItem.DeviceID

    Wscript.Echo "Name: " & objItem.Name

    Wscript.Echo "PNP Device ID: " & objItem.PNPDeviceID

Next

Save the script as Filename.vbs, And just double click on that.

Computer Hardware Scripts: Enumerating Computer Baseboard Properties

INVENTORY OF A COMPUTER WITH WINDOWS

Pripared by Laxman,

Returns information about the computer baseboard.




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



On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_BaseBoard")
For Each objItem in colItems
    For Each strOption in objItem.ConfigOptions
        Wscript.Echo "Configuration Option: " & strOption
    Next
    Wscript.Echo "Depth: " & objItem.Depth
    Wscript.Echo "Description: " & objItem.Description
    Wscript.Echo "Height: " & objItem.Height
    Wscript.Echo "Hosting Board: " & objItem.HostingBoard
    Wscript.Echo "Hot Swappable: " & objItem.HotSwappable
    Wscript.Echo "Manufacturer: " & objItem.Manufacturer
    Wscript.Echo "Model: " & objItem.Model
    Wscript.Echo "Name: " & objItem.Name
    Wscript.Echo "Other Identifying Information: " & objItem.OtherIdentifyingInfo
    Wscript.Echo "Part Number: " & objItem.PartNumber
    Wscript.Echo "Powered On: " & objItem.PoweredOn
    Wscript.Echo "Product: " & objItem.Product
    Wscript.Echo "Removable: " & objItem.Removable
    Wscript.Echo "Replaceable: " & objItem.Replaceable
    Wscript.Echo "Requirements Description: " & objItem.RequirementsDescription
    Wscript.Echo "Requires DaughterBoard: " & objItem.RequiresDaughterBoard
    Wscript.Echo "Serial Number: " & objItem.SerialNumber
    Wscript.Echo "SKU: " & objItem.SKU
    Wscript.Echo "Slot Layout: " & objItem.SlotLayout
    Wscript.Echo "Special Requirements: " & objItem.SpecialRequirements
    Wscript.Echo "Tag: " & objItem.Tag
    Wscript.Echo "Version: " & objItem.Version
    Wscript.Echo "Weight: " & objItem.Weight
    Wscript.Echo "Width: " & objItem.Width
Next



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


Save the script as Filename.vbs, And just double click on that.