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...

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...

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...

Computer Hardware Scripts: Retrieving System Information

Retrieving System Information INVENTORY OF A COMPUTER WITH WINDOWSPripared 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...

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 _   ...

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...

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  ...

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  ...

Computer Hardware Scripts: Enumerating the Physical Memory Configuration

Enumerating the Physical Memory ConfigurationReturns information about the way physical memory is configured on a computer.  INVENTORY OF A COMPUTER WITH WINDOWSPripared by Laxman, ==================== On Error Resume Next strComputer = "." Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") Set colItems = objWMIService.ExecQuery("Select...

Enumerating Parallel Port Properties

Enumerating Parallel Port PropertiesINVENTORY OF A COMPUTER WITH WINDOWSPripared 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...

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...