Monday, July 4, 2011

Import a Large Text File


Imports a text file into Excel even if the number of lines in that file exceeds Excel's total number of rows limitation.



Visual Basic
'       This script was written for folks trying to import a text file into  
'       Excel 2003 that exceed the row limitations.  
'       This version works on Windows XP and has not been tested on any other OS.  
 
Const ForReading = 1  
Const ForAppending = 2  
 
Set objDialog = CreateObject("UserAccounts.CommonDialog")  
 
objDialog.Filter = "All Files|*.*"  
objDialog.InitialDir = "C:\"  
intResult = objDialog.ShowOpen  
   
If intResult = 0 Then  
    Wscript.Quit  
Else  
    BreakFile =  objDialog.FileName  
End If  
 
Set objFSO = CreateObject("Scripting.FileSystemObject")  
Set objFile = objFSO.OpenTextFile(BreakFile, ForReading)  
 
FiletoSplit = objFSO.GetFileName(BreakFile)  
FolderDest = Mid(objFSO.GetAbsolutePathName(BreakFile),1, _ 
    Len(objFSO.GetAbsolutePathName(BreakFile))-(Len(FiletoSplit)))  
FileSplitName = objFSO.GetBaseName(BreakFile)  
 
 
 
dtmStart = Now()  
strContents = objFile.ReadAll  
FileNum = 1  
fname =  FolderDest & FileSplitName & "Split_" & FileNum & ".txt"  
Set objFile1 = objFSO.OpenTextFile(fname, ForAppending, True)  
 
 
 
CountLines = 0  
arrLines = Split(strContents, vbCrLf)  
 
If ubound(arrLines) < 64500 Then  
        msgbox "This file will fit into Excel already.  No split is necessary.",48,"SplitFile"  
        Wscript.Quit  
End If  
 
        HeaderText = arrLines(0)  
                For i = 0 to ubound(arrlines)                    
                        strLine = arrLines(i) & vbCrLf                   
                        objFile1.Write strLine                   
                        If  (Countlines) < 64500  Then                           
                                countlines = countlines + 1                      
                        ElseIf Countlines >= 64500 Then  
                                objFile1.Close  
                                Countlines = 0                           
                                FileNum = FileNum + 1  
                                fname = FolderDest & FileSplitName & "Split_" & FileNum & ".txt"  
                                Set objFile1 = objFSO.OpenTextFile(fname, ForAppending, True)  
                                objFile1.Write HeaderText & vbCrLf                               
                        End If           
        Next  
            
objFile.Close  
dtmEnd = Now()  
If MsgBox("There were " & FileNum & " files created." & vbcrlf & _  
        "The files were put into this folder:  " & FolderDest & _  
        vbCrLf & "The script took " & DateDiff("s", dtmStart, dtmEnd) & " seconds " & _  
        "to break the " &  FiletoSplit & " file." & vbcrlf & vbcrLF & _  
        "Click OK to open destination folder or CANCEL to quit.",  _  
        1,"SplitFile") = vbOK Then  
        Set objShell = CreateObject("Shell.Application")  
        strPath = FolderDest  
 
        objShell.Explore strPath  
End If 
 
Verified on the following platforms
Windows Server 2008 R2No
Windows Server 2008No
Windows Server 2003No
Windows 7No
Windows VistaNo
Windows XPNo
Windows 2000No


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.

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 R2No
Windows Server 2008No
Windows Server 2003No
Windows 7No
Windows VistaNo
Windows XPNo
Windows 2000No
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 R2No
Windows Server 2008No
Windows Server 2003No
Windows 7No
Windows VistaNo
Windows XPYes
Windows 2000No
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