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
1 registered (Allen) and 181 anonymous users online.
Newest Members
skygreen2031, amelindarķe, Nims123, davidscroegh, davidscroe
17626 Registered Users

Generated in 0.06 seconds in which 0.029 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