Page 3 of 3 <123
Topic Options
#181011 - 2007-10-02 12:42 AM Re: Need a script that read/collect and creates... [Re: Witto]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
 Originally Posted By: Witto

 Code:
;Select data range (cell at bottom right of data range)
$RC = $objExcel.Range("A1:P1285")
Is your excel sheet always 1285 rows deep?

In this particular case, yes, this Excel list is "always" 1285 rows deep.
Anyway that was the only way I could make it work with the complete data range.

 Originally Posted By: Witto

I think you missed a "(" here:
$RC = $objExcel.ActiveWorkbook.SaveAs(@SCRIPTDIR+"\"+$Manager+".xls",,,,,False)

Correct. Dont know how it was missed.

Note, in this case the Excel list is in 2007 format xlsx. When saving the new workbooks I chose to save them in XP/2003 compatible file format, using FileFormat values "56" (xlExcel8).
 Code:
$RC = $objExcel.ActiveWorkbook.SaveAs(@SCRIPTDIR+"\"+$Manager+".xls",56,,,,False)

The code is working fine for me, anyway I will check the kixtarter too.
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"

Top
#181012 - 2007-10-02 12:58 AM Re: Need a script that read/collect and creates... [Re: 1984]
Witto Offline
MM club member
*****

Registered: 2004-09-29
Posts: 1828
Loc: Belgium
I am using Office 2003.
I think you should use .Select here:
$RC = $objExcel.Range("A1:P1285").Select

Top
#181125 - 2007-10-04 02:32 PM Re: Need a script that read/collect and creates... [Re: Witto]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
Witto could you validate this one?. Works fine for me with Excel 2003.

In this case scenario the source Excel book contains 14 columns (A:P).
Managers column is "L".

 Code:

;*************************************************************************
;  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

_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"

Top
#181133 - 2007-10-05 12:59 AM Re: Need a script that read/collect and creates... [Re: 1984]
Witto Offline
MM club member
*****

Registered: 2004-09-29
Posts: 1828
Loc: Belgium
 Originally Posted By: 1984
;Add AutoFilter for manager on column 12 (= L)
column 12 is the 12th column of the filter. Per example: if the filter starts at column C, the 12th column is N
Looks nice I think. Changed it back to your first example and seems to work.

Top
Page 3 of 3 <123


Moderator:  Shawn, ShaneEP, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
3 registered (Glenn Barnas, Mart, fergieman101) and 188 anonymous users online.
Newest Members
emnipetro, Hirze, thequeen, ameliaryan, shalomsalami
17641 Registered Users

Generated in 0.029 seconds in which 0.012 seconds were spent on a total of 14 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org