#180929 - 2007-09-28 12:45 AM
Re: Need a script that read/collect and creates...
[Re: Witto]
|
1984
Starting to like KiXtart
Registered: 2003-08-14
Posts: 150
|
Now we talking :-)
Performed quick test.
Glenn your code works now, although it miss delimiter ";" in created hash file...
Richard your code now works perfectly, dont know if you changed anything..
Gargoyle unfortunately I cannot make your code work, it still give same error; array reference out of bounds! even when changed to your latest suggestion
Wittos your code is actually the one which matches my need best, the only problem is that you manually enter "managers", which in my case means manually entering more the 50 managers in the code. If we could have a step before that filter out the managers and then populate the lists as in your code, then the circle would be closed and my need fulfilled!
To all, I also found that when I saved the the original Excel file as csv with comma delimited, it actually saves the file with semicolon ";" and not comma ",". I saw that when I opened it with notepad ++.
Now back to more testing/learning :-)
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"
|
Top
|
|
|
|
#180937 - 2007-09-28 09:24 AM
Re: Need a script that read/collect and creates...
[Re: 1984]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
What is your original file?
- Excel
- Text with semicolumns
If it is Excel, I would try to recurse the D column from the last cell up to the top (or the first cell with a manager name) and gather all the manager names in an array. If it is Semicolumn separated text, I would look to the code Richard provided.
|
Top
|
|
|
|
#180965 - 2007-09-28 04:17 PM
Re: Need a script that read/collect and creates...
[Re: 1984]
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
|
If you look at the script I posted, you'll see that the INI file creates a section for each manager ([MGRA]) and then lists each user's information as a record in that section. I chose to use an INI file because it is VISUAL - you can see how your data is organized. This has many benefits, especially during development of complex processes - you can see how the data is organized, how your code is interpreting results, and how the logic is handling issues like missing fields.
Of course there are alternatives - one is to enumerate your list and find all the manager IDs, collected into an array. You can then use Sort() and Uniq() to change that to an array of unique manager names. Then create a 2-eimension array - the first dimension size is the number of managers, the second is the maximum number of users assigned to a manager.
Then, you can enumerate your data again. Get the MgrID, use AScan to change it to an ID # (the record in the list of managers) and use that as the first pointer to the data array...
Whew!! My point is that you can write complex code and do it all in memory, or you can use the INI file as a disk based array that will permit access by name. Much easier, especially for someone new to coding, usnig arrays, etc.
The second code example that I posted earlier will generate CSV files for each manager, with that manager's users as the data. I used Chr(31) as a delimiter, which is unlikely to conflict with any text in your data. The example will work no matter how you generate the INI file - excel or text file source.
Glenn
_________________________
Actually I am a Rocket Scientist!
|
Top
|
|
|
|
#180966 - 2007-09-28 04:38 PM
Re: Need a script that read/collect and creates...
[Re: 1984]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
$RC = $objExcel.Range("A1").End($xlDown).End($xlToRight).Select this leads you to the last cell in your range I presume the column is always D You can get the row like this
...
Dim $Row
...
$Row = $objExcel.ActiveCell.Row
... I presume the first cell with a manager in it is always 2 So I would go from the last cell in column D to the second Anyway, here is some code
;************************************************************************* ; Script Name: ; 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 $objExcel.Visible = -1 $RC = $objExcel.Workbooks.Open($MyTable) $RC = $objExcel.Selection.Autofilter $RC = $objExcel.Range("A1").End($xlDown).End($xlToRight).Select $Row = $objExcel.ActiveCell.Row For $i = $row to 2 step -1 $Manager = $objExcel.Range("D"+$i).Text If AScan($Managers, $Manager) = -1 ReDim Preserve $Managers[$j] $Managers[$j] = $Manager $j = $j + 1 EndIf Next $RC = $objExcel.Range($objExcel.Selection, $objExcel.Cells(1)).Select For Each $Manager in $Managers $RC = $objExcel.Selection.AutoFilter(4, $Manager) $RC = $objExcel.Selection.Copy $RC = $objExcel.Workbooks.Add $RC = $objExcel.ActiveSheet.Paste $RC = $objExcel.ActiveWindow.ActivateNext Next $RC = $objExcel.Selection.AutoFilter(4)
;Personal UDF Section
;UDF Section |
|
Top
|
|
|
|
#180970 - 2007-09-28 06:56 PM
Re: Need a script that read/collect and creates...
[Re: 1984]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
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 |
Edited by Witto (2007-09-28 07:33 PM) Edit Reason: Solved SaveAs not overwriting and added deletion of column D in new workbooks
|
Top
|
|
|
|
#180980 - 2007-09-29 08:45 PM
Re: Need a script that read/collect and creates...
[Re: Witto]
|
1984
Starting to like KiXtart
Registered: 2003-08-14
Posts: 150
|
Aha thats what "recursion" means!... slowly beginning to understand how it works on high level. Also the "macro recording" didn't know such function even exist.
Gotta admit my knowledge is very limited when it comes to coding and understanding all definitions surround it. its comparable to my English .-)
Anyway, your silver platter code is just beautiful :-) and ofcurse Kix is the king.
I realize that those few times I make codes I have problem with understanding the definition and use of "array". Thats something I always need to refresh, before I can make an script work... It gets much more difficult when other language gets involved in the case scenario, like VBs, as I know nothing about it.
In our case here, I still have some wondering that I couldn't figure out.
You are using variables which I cannot figure out what they really represent and do, and how you get their values:
$xlDown = -4121
$xlToLeft = -4159
$xlToRight = -4161
$xlUp = -4162
I have played with macros in Excel. I of course need now to adapt your code to the Excel list I have (the big one), but found its not that easy as it looked in first place:
For adapting the code to the actual Excel list I change the following in the script:
$Manager = $objExcel.Range("D"+$i).Text
$RC = $objExcel.Columns("D:D").Select
I changed "D" to "K" in these line, that is the column of managers
I changed "D" to "K", The original Excel list I have contains 14 columns (the last one is "N"). The first column "A" presents the User IDs, followed by lots of user contact information, and then column "K" represents the managers, followed by managers contact data.
its about 1000 users, and 50 managers. some few also don't have a managers assigned on them so for those the managers column is empty.
I'm going now to switch to excel 2003 instead of 2007, see if its easier to understand how i can benefit from macro recorder, maybe I succeeded adapt your code to the big Excel file.
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"
|
Top
|
|
|
|
#180996 - 2007-10-01 01:46 PM
Re: Need a script that read/collect and creates...
[Re: 1984]
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
|
I've got a full load of work this morning, so can't get into the details for a while, but simply:
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)
All Excel interaction is handled with just 6 xlLib calls...
This is untested, and needs to be adapted to your file names, data, data ranges, etc.. but should work with a little effort. It will create separate .CSV files for each manager, with the manager's data in each file.
When you define range for the sort and value calls, only specify the range with data, do not include the headers!
Download the Zip file and play with the test script to see how everything works.
Glenn
_________________________
Actually I am a Rocket Scientist!
|
Top
|
|
|
|
#181006 - 2007-10-01 08:29 PM
Re: Need a script that read/collect and creates...
[Re: Witto]
|
1984
Starting to like KiXtart
Registered: 2003-08-14
Posts: 150
|
Ok dudes, here we go:
At last, thanx to all the feedbacks and help, I managed to adapt Wittos code to get what I needed. Its tested with Excel 2007 and works based on my needs as earlier stated in this topic.
As Witto suggested Im using Macro recorder in Excel to obtain the VB codes.
;*************************************************************************
; 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
5 star to Witto and Glenn for all their help and patience.
/CY
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"
|
Top
|
|
|
|
#181010 - 2007-10-02 12:02 AM
Re: Need a script that read/collect and creates...
[Re: 1984]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
Just some questions at first glance
;Select data range (cell at bottom right of data range)
$RC = $objExcel.Range("A1:P1285")
Is your excel sheet always 1285 rows deep?
;Save the workbook with as name the manager
$RC = $objExcel.ActiveWorkbook.SaveAs@SCRIPTDIR+"\"+$Manager+".xls",,,,,False)
I think you missed a "(" here: $RC = $objExcel.ActiveWorkbook.SaveAs(@SCRIPTDIR+"\"+$Manager+".xls",,,,,False)
Have you tested your code while editing your script? As far as I can see and as far as I could test your script, you did not select anything so there wasn't anything to copy to the clipboard.
Maybe KiXtarter can help you to run your script from time to time while you are writing it.
|
Top
|
|
|
|
Moderator: Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart
|
0 registered
and 507 anonymous users online.
|
|
|