A B C D ID: Unit: Site: Manager: userP UnitBX London ManD userX UnitA Paris ManA user54 Unit34 London ManF user23 Unit34 London ManF user01 UnitRR Paris ManC user11 UnitBX Madrid ManA user7 UnitZ Madrid ManD user62 UnitRR London ManD userTX UnitA Paris ManD userLA UnitFM Madrid ManA user05 UnitRV London ManC user12 UnitZ Paris ManC

userP;UnitBX;London;ManD userX;UnitA;Paris;ManA user54;Unit34;London;ManF user23;Unit34;London;ManF user01;UnitRR;Paris;ManC user11;UnitBX;Madrid;ManA user7;UnitZ;Madrid;ManD user62;UnitRR;London;ManD userTX;UnitA;Paris;ManD userLA;UnitFM;Madrid;ManA user05;UnitRV;London;ManC user12;UnitZ;Paris;ManC

;Script Options $SO=SETOPTION("Explicit", "ON") $SO=SETOPTION("NoMacrosInStrings", "ON") $SO=SETOPTION("NoVarsInStrings", "ON") $SO=SETOPTION("WrapAtEOL", "ON") If Not @LogonMode BREAK ON EndIf Dim $FH, $Line, $Managers[], $Count, $Manager, $Count2 $FH = FreeFileHandle() $Count = 0 Open($FH,"C:\Support\MyInputFile.CSV") $Line = Readline($FH) While @Error = 0 $Line = Split($Line,",") If InArray($Managers,$Line[3]) < 0 Redim Preserve $Managers[$Count] $Managers[$Count] = $Line[3] $Count = $Count + 1 EndIf $Line = Readline(1) Loop Close($FH) Dim $Users[Ubound($Managers),0] $Count = 0 For Each $Manager in $Managers $Count2 = 1 $FH = FreeFileHandle() Open($FH,"C:\Support\MyInputFile.CSV") $Users[$Count,0] = $Manager $Line = Readline($FH) While @Error = 0 $Line = Split($Line,",") If $Line[3] = $Manager $Users[$Count,$Count2] = $Line[0] $Count2 = $Count2 + 1 EndIf $Line = Readline($FH) Loop $Count = $Count + 1 Next Close($FH) For $Count = 0 to Ubound($Users,1) $FH = FreeFileHandle() Open($FH,"C:\Output\"+$Users[$Count,0]+".csv",5) For $Count2 = 1 to Ubound($Users,2) Writeline($FH,$Users[$Count,$Count2]+@CRLF) Next Close($FH Next

Break ON $=SetOption("Explicit","ON") Dim $fh,$sFile,$sLine $fh=FreeFileHandle() $sFile=".\data.csv" If Not Open($fh,$sFile) $sLine=ReadLine($fh) While Not @ERROR $sLine=SPlit($sLine,",") If UBound($sLine)=3 $=RedirectOutput($sLine[3]+".csv") Join($sLine,",")+@CRLF $=RedirectOutput("") EndIf $sLine=ReadLine($fh) Loop Else "Cannot open "+$sFile+" for reading"+@CRLF EndIf

;Working with Excel, for example ; Define an Excel object pointer $oXL = xlInstantiateApp() ; Open the file xlFile($oXL, 0, 'MyFile.xls') ; Return an array of 4 values from the first data row ; Start with row 2, since row 1 has titles ; keep reading until a blank row is returned $Row = 2 While $Row > 0 $Range = 'A' + CStr($Row) + ':D' + CStr($Row) $aData = xlRangeValue($oXL, $Range, , 'Sheet1') ; The first element of the array contains the user, fourth has the manager ; Howard Bullok has a great Hash UDF that would work well here, but ; I'm going to use a "poor-man's" hash - an INI file, and reduce the number ; of UDFs required If $aData[0] = '' ; Anti-Golf to make it clear what data goes where $User = $aData[0] $Manager = $aData[3] $Record = Join($aData, Chr(31)) ; Chr(31) is ASCII US - Unit Separator $ = WriteProfileString('.\hash.ini', $Manager, $User, $Record) Else $Row = -1 ; no more data - exit the loop EndIf $Row = $Row + 1 Loop

[MANA] UserX=userX;UnitA;Paris;ManA User11=user11;UnitBX;Madrid;ManA userLA=userLA;UnitFM;Madrid;ManA [MANC] User01=user01;UnitRR;Paris;ManC User05=user05;UnitRV;London;ManC User12=user12;UnitZ;Paris;ManC

; Get list of Managers $aManagers = EnumIni('.\hash.ini') For Each $Manager in $aManagers ; get the list of users for the current manager ID $aUsers = EnumIni('.\hash.ini', $Manager) ; the print statements are only for debugging/monitoring ; Removing them can streamline the process by placing the RedirectOutput commands ; outside of the Users For/Next loop 'Manager is ' $Manager ? For Each $User in $aUsers $User ? $aData = Split(ReadProfileString('.\hash.ini', $Manager, $User), Chr(31)) ; the original data is now in $aData array ; simple result - write a CSV record to a MANAGER.CSV file $ = RedirectOutput('.\' + $Manager + '.csv') CSV($aData) ? $ = RedirectOutput('') Next ; User Next ; Manager

; Define an Excel object pointer $oXL = xlInstantiateApp() ; Open the file xlFile($oXL, 0, 'C:\Bin\Book1.xls') ; Return an array of 4 values from the first data row ; Start with row 2, since row 1 has titles ; keep reading until a blank row is returned $Row = 2 While $Row > 0 $Range = 'A' + CStr($Row) + 'D' + CStr($Row) $aData = xlRangeValue($oXL, $Range, , 'Sheet1') ; The first element of the array contains the user, fourth has the manager ; Howard Bullok has a great Hash UDF that would work well here, but ; I'm going to use a "poor-man's" hash - an INI file, and reduce the number ; of UDFs required If $aData[0] = '' ; Anti-Golf to make it clear what data goes where $User = $aData[0] $Manager = $aData[3] $Record = Join($aData, Chr(31)) ; Chr(31) is ASCII US - Unit Separator $ = WriteProfileString('.\hash.ini', $Manager, $User, $Record) Else $Row = -1 ; no more data - exit the loop EndIf $Row = $Row + 1 Loop Function xlFile($_ID, $_FN, $_File) Dim $_ Select Case $_FN = 0 ; Open If Exist($_File) ; if the file exists, open it ;$_ = $_ID.Open($_File, $_Links, $_RO, $_Fmt) $_ = $_ID.WorkBooks.Open($_File) Exit @ERROR Else Exit 2 ; otherwise complain that is isn't found EndIf Case $_FN = 1 ; Save(as) ; If the file was previously saved (or opened), just re-save it, ; otherwise do a Save As If Not $_ID.ActiveWorkbook.Path $_ = $_ID.ActiveWorkbook.SaveAs($_File) Else $_ = $_ID.ActiveWorkbook.Save EndIf Exit @ERROR Case $_FN = 2 ; SaveAs $_ = $_ID.ActiveWorkbook.SaveAs($_File) Exit @ERROR EndFunction Function xlRangeValue($_ID, $_Range, OPTIONAL $_Value, OPTIONAL $_Sheet) $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet) If VarType($_Value) <> 0 $_ID.WorkSheets($_Sheet).Range($_Range).Value = $_Value Else $xlRangeValue = $_ID.WorkSheets($_Sheet).Range($_Range).Value EndIf Exit @ERROR EndFunction

;; KixGenerated: 2007/09/26 - 19:54:31 Break On ;Working with Excel, for example ; Define an Excel object pointer $oXL = xlInstantiateApp() ; Open the file xlFile($oXL, 0, 'c:\temp\test.xls') 'Open: ' @SERROR ? ; Return an array of 4 values from the first data row ; Start with row 2, since row 1 has titles ; keep reading until a blank row is returned $Row = 2 While $Row > 0 $Range = 'A' + CStr($Row) + ':D' + CStr($Row) $aData = xlRangeValue($oXL, $Range, , 'Sheet1') For Each $ in $aData $ ? Next ; The first element of the array contains the user, fourth has the manager ; Howard Bullok has a great Hash UDF that would work well here, but ; I'm going to use a "poor-man's" hash - an INI file, and reduce the number ; of UDFs required If $aData[0] ; Anti-Golf to make it clear what data goes where $User = $aData[0] $Manager = $aData[3] $Record = Join($aData, Chr(31)) $ = WriteProfileString('.\hash.ini', $Manager, $User, $Record) Else $Row = -1 ; no more data - exit the loop EndIf $Row = $Row + 1 Loop

Break ON $=SetOption("Explicit","ON") Dim $fh,$sFile,$sLine $fh=FreeFileHandle() $sFile=".\data.csv" If Not Open($fh,$sFile) $sLine=ReadLine($fh) While Not @ERROR "Processing line: "+$sLine+@CRLF $sLine=SPlit($sLine,",") If UBound($sLine)=3 $=RedirectOutput($sLine[3]+".csv") Join($sLine,",")+@CRLF $=RedirectOutput("") Else "ERROR: Number of fields on this line <> 4"+@CRLF Exit 1 EndIf $sLine=ReadLine($fh) Loop Else "Cannot open "+$sFile+" for reading"+@CRLF EndIf

A part of the code is made using the macro recorder
I will try to give an example

Start MS Excel
Start the Macro Recorder via Tools --> Macro --> Record new macro...
Do a File --> Save As and give the file a name
Stop the macro recorder
Go to Tools --> Macro --> Macros
Start editing the recorded macro
Now you have to be a littlebit inventive and decide what you really need in this code and how you can paste this in your KiX script. Anyway, you learned you will need ActiveWorkbook.SaveAs

Maybe you also want to get rid of annoying messages telling that you will overwrite files. Google is your friend
http://www.google.be/search?hl=nl&q=ActiveWorkbook.SaveAs+site%3Amicrosoft.com&meta=
First hit
http://support.microsoft.com/kb/213641
Silver platter with some explanation

;*************************************************************************
; Script Name: FindManagers.kix
; Author: Wim Rotty
; Date: 26/09/2007
; Description: Copy and paste data from one Excel book to other books
;*************************************************************************
;Script Options
If Not @LOGONMODE
Break On
Else
Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit", "On")
$RC = SetOption("NoMacrosInStrings", "On")
$RC = SetOption("NoVarsInStrings", "On")
If @SCRIPTEXE = "KIX32.EXE"
$RC = SetOption("WrapAtEOL", "On")
EndIf

;Declare variables
Dim $MyTable
Dim $objExcel
Dim $xlDown, $xlToLeft, $xlToRight, $xlUp
Dim $Row, $i, $Manager, $Managers[0], $j

;Initialize variables
$MyTable = "C:\Test\Tabel.xls"
$objExcel = CreateObject("Excel.Application")
If @ERROR
Exit @ERROR
EndIf
$xlDown = -4121
$xlToLeft = -4159
$xlToRight = -4161
$xlUp = -4162
$j = 0

;Code
;Do not show alerts like messages about overwriting files
$objExcel.DisplayAlerts = False
;Show Excel, not really needed if you quit at the end
;$objExcel.Visible = -1
;Open file
$RC = $objExcel.Workbooks.Open($MyTable)
;Add filter
$RC = $objExcel.Selection.Autofilter
;Select cell at bottom right of data range
$RC = $objExcel.Range("A1").End($xlDown).End($xlToRight).Select
;Get cell rownumber
$Row = $objExcel.ActiveCell.Row
;Recurse cells up to cell 2 and gather all the Manager names in an array
For $i = $row to 2 step -1
;Get the text (manager name) in the cell
$Manager = $objExcel.Range("D"+$i).Text
;Is the manager name NOT in the array?
If AScan($Managers, $Manager) = -1
;If not, add location to array and add it to new location
ReDim Preserve $Managers[$j]
$Managers[$j] = $Manager
$j = $j + 1
EndIf
Next
;Select all data in range
$RC = $objExcel.Range($objExcel.Selection, $objExcel.Cells(1)).Select
;Recurse the Managers array
For Each $Manager in $Managers
;Filter for each manager name
$RC = $objExcel.Selection.AutoFilter(4, $Manager)
;Copy the selection to the clipboard
$RC = $objExcel.Selection.Copy
;Add a new workbook
$RC = $objExcel.Workbooks.Add
;Paste the data from the clipboard
$RC = $objExcel.ActiveSheet.Paste
;Select the D column with the manager name
$RC = $objExcel.Columns("D:D").Select
;Clear all in this column
$RC = $objExcel.Selection.Clear
;Save the workbook with as name the manager
$RC = $objExcel.ActiveWorkbook.SaveAs(@SCRIPTDIR+"\"+$Manager+".xls",,,,,False)
;The next active sheet is the original file
$RC = $objExcel.ActiveWindow.ActivateNext
Next
;Close Excel
$RC = $objExcel.Application.Quit

;Personal UDF Section

;UDF Section

Call '.\xlLib.udf' ; Launch Excel $oXL=xlInit() ; open file xlFile($oXL,'myfile.xls',0) ; sort - define the range containing your data, "D1" is the column with ; the manager's name, "A1" contains the user's name - this sorts ; by manager, then by user xlRangeSort($oXL, 'A1:E6', 'D1',1,'A1',1) ; Return the sorted data in an array - 6 rows of 5 columns in this case $aData = xlRangeValue($oXL, 'A1:E6') ; Enumerate the data - 2 dimension array ; $aData[0][3] is the manager's name of the first row of data $Managers = 'mgra','mgrb','mgrc' For each $Manager in $Managers For $I = 0 to UBound($aData) If $aData[$I][3] = $Manager ; Have data for current manager ; write data to manager file $ = RedirectOutput($Manager + '.csv') ; convert entire row to CSV format - don't forget to include the ; CSV() UDF file. CSV($aData[$I]) ? $ = RedirectOutput('') EndIf Next Next xlQuit($oXL)

;************************************************************************* ; Script Name: FindManagers.kix ; Author: Wim Rotty ; Date: 26/09/2007 ; Description: Copy and paste data from one Excel book to other books ;************************************************************************* ;Script Options If Not @LOGONMODE Break On Else Break Off EndIf Dim $RC $RC = SetOption("Explicit", "On") $RC = SetOption("NoMacrosInStrings", "On") $RC = SetOption("NoVarsInStrings", "On") If @SCRIPTEXE = "KIX32.EXE" $RC = SetOption("WrapAtEOL", "On") EndIf ;Declare variables Dim $MyTable Dim $objExcel Dim $Row, $i, $Manager, $Managers[0], $j ;Initialize variables $MyTable = "C:\Test\Tabel.xls" $objExcel = CreateObject("Excel.Application") If @ERROR Exit @ERROR EndIf $j = 0 ;Code ;Do not show alerts like messages about overwriting files $objExcel.DisplayAlerts = False ;Show Excel, not really needed if you quit at the end ;$objExcel.Visible = -1 ;Open file $RC = $objExcel.Workbooks.Open($MyTable) ;Add filter $RC = $objExcel.Selection.Autofilter ;Select data range (cell at bottom right of data range) $RC = $objExcel.Range("A1:P1285") ;Get cell rownumber $Row = $objExcel.ActiveCell.Row ;Recurse cells up to cell 2 and gather all the Manager names in an array For $i = $row to 2 step -1 ;Get the text (manager name) in the cell $Manager = $objExcel.Range("K"+$i).Text ;Is the manager name in the array? If AScan($Managers, $Manager) = -1 ;If not, add location to array and add it to new location ReDim Preserve $Managers[$j] $Managers[$j] = $Manager $j = $j + 1 EndIf Next ;Select all data in range $RC = $objExcel.Range($objExcel.Selection, $objExcel.Cells(1)).Select ;Recurse the Managers array For Each $Manager in $Managers ;Filter for each manager name (where "11" is the number of manager column) $RC = $objExcel.Selection.AutoFilter(11, $Manager) ;Copy the selection to the clipboard $RC = $objExcel.Selection.Copy ;Add a new workbook $RC = $objExcel.Workbooks.Add ;Paste the data from the clipboard $RC = $objExcel.ActiveSheet.Paste ;Select columns $RC = $objExcel.Columns("D:O").Select ;Hide selected columns $objExcel.Selection.EntireColumn.Hidden = True ;AutoFit columns $RC = $objExcel.Columns("A:C").EntireColumn.AutoFit ;$RC = $objExcel.Selection.EntireColumn.Hidden ;Save the workbook with as name the manager $RC = $objExcel.ActiveWorkbook.SaveAs@SCRIPTDIR+"\"+$Manager+".xls",,,,,False) ;The next active sheet is the original file $RC = $objExcel.ActiveWindow.ActivateNext Next ;Close Excel $RC = $objExcel.Application.Quit ;Personal UDF Section ;UDF Section

;************************************************************************* ; Script Name: FindManagers.kix ; Author: Wim Rotty ; Date: 26/09/2007 ; Update by: 1984 /CY ; Description: Read, sort and filter data from an Excel book, ; then create new book And Copy/paste data to it. ;************************************************************************* ;Script Options If Not @LOGONMODE Break On Else Break Off EndIf Dim $RC $RC = SetOption("Explicit", "On") $RC = SetOption("NoMacrosInStrings", "On") $RC = SetOption("NoVarsInStrings", "On") If @SCRIPTEXE = "KIX32.EXE" $RC = SetOption("WrapAtEOL", "On") EndIf ;Declare variables Dim $MyTable Dim $objExcel Dim $xlDown, $xlToLeft, $xlToRight, $xlUp Dim $Row, $i, $Manager, $Managers[0], $j ;Initialize variables $MyTable = "C:\Test\book1.xls" $objExcel = CreateObject("Excel.Application") If @ERROR Exit @ERROR EndIf $xlDown = -4121 $xlToLeft = -4159 $xlToRight = -4161 $xlUp = -4162 $j = 0 ;Do not show alerts like messages about overwriting files $objExcel.DisplayAlerts = False ;Show Excel, not really needed if you quit at the end ;$objExcel.Visible = -1 ;Open file $RC = $objExcel.Workbooks.Open($MyTable) ;Add filter $RC = $objExcel.Selection.Autofilter ;Select cell at bottom right of data range $RC = $objExcel.Range("A1").End($xlDown).End($xlToRight).Select ;Get cell rownumber $Row = $objExcel.ActiveCell.Row ;Recurse cells up to cell 2 and gather all the Manager names in an array For $i = $row to 2 step -1 ;Get the text (manager name) in the cell (Column "L") $Manager = $objExcel.Range("L"+$i).Text ;Is the manager name NOT in the array? If AScan($Managers, $Manager) = -1 ;If not, add location to array and add it to new location ReDim Preserve $Managers[$j] $Managers[$j] = $Manager $j = $j + 1 EndIf Next ;Recurse the Managers array, Filter for each manager name For Each $Manager in $Managers ;Add AutoFilter for manager on column 12 (= L) $RC = $objExcel.Selection.AutoFilter(12, $Manager) ;Select all data in range Column A to column P $RC = $objExcel.Range($objExcel.Selection, $objExcel.Columns("A:P")).Select ;Copy the selection to the clipboard $RC = $objExcel.Selection.Copy ;Add a new workbook $RC = $objExcel.Workbooks.Add ;Paste the data from the clipboard $RC = $objExcel.ActiveSheet.Paste ;Select Column E to P $RC = $objExcel.Columns("E:P").Select ;Hide column E to P $objExcel.Selection.EntireColumn.Hidden = True ;Autofit visible columns (A to D) $RC = $objExcel.Columns("A:D").EntireColumn.AutoFit ;Save the workbook with as name the manager $RC = $objExcel.ActiveWorkbook.SaveAs(@SCRIPTDIR+"\"+$Manager+".xls",,,,,False) ;The next active sheet is the original file $RC = $objExcel.ActiveWindow.ActivateNext Next ;Close Excel $RC = $objExcel.Application.Quit ;Personal UDF Section