#180839 - 2007-09-26 12:42 AM
Re: Need a script that read/collect and creates...
[Re: 1984]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
You can always use your macro recorder to catch some code and use it in a script Here is maybe an idea for your first scenario have an Excel file which contains hundreds of user IDs, contact information, location, department, and manager IDs for each of listed users.
;************************************************************************* ; 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
;Initialize variables $MyTable = "C:\Test\Tabel.xls" $objExcel = CreateObject("Excel.Application") If @ERROR Exit @ERROR EndIf $xlDown = -4121 $xlToLeft = -4159 $xlToRight = -4161 $xlUp = -4162
;Code $objExcel.Visible = -1 $RC = $objExcel.Workbooks.Open($MyTable) $RC = $objExcel.Selection.Autofilter $RC = $objExcel.Range("A1").End($xlDown).End($xlToRight).Select $RC = $objExcel.Range($objExcel.Selection, $objExcel.Cells(1)).Select $RC = $objExcel.Selection.AutoFilter(4, "ManA") $RC = $objExcel.Selection.Copy $RC = $objExcel.Workbooks.Add $RC = $objExcel.ActiveSheet.Paste $RC = $objExcel.ActiveWindow.ActivateNext $RC = $objExcel.Selection.AutoFilter(4, "ManC") $RC = $objExcel.Selection.Copy $RC = $objExcel.Workbooks.Add $RC = $objExcel.ActiveSheet.Paste $RC = $objExcel.ActiveWindow.ActivateNext $RC = $objExcel.Selection.AutoFilter(4, "ManD") $RC = $objExcel.Selection.Copy $RC = $objExcel.Workbooks.Add $RC = $objExcel.ActiveSheet.Paste $RC = $objExcel.ActiveWindow.ActivateNext $RC = $objExcel.Selection.AutoFilter(4, "ManF") $RC = $objExcel.Selection.Copy $RC = $objExcel.Workbooks.Add $RC = $objExcel.ActiveSheet.Paste $RC = $objExcel.ActiveWindow.ActivateNext $RC = $objExcel.Selection.AutoFilter(4)
;Personal UDF Section
;UDF Section |
Microsoft Excel Constants (Excel 2003 VBA Language Reference)
|
Top
|
|
|
|
#180841 - 2007-09-26 05:55 AM
Re: Need a script that read/collect and creates...
[Re: 1984]
|
NTDOC
Administrator
Registered: 2000-07-28
Posts: 11623
Loc: CA
|
Well KiXtart is not exactly a match for doing this sort of manipulation of Excel data. I would recommend using the built-in VBA or if you're not familiar with it then this site here is like KiXtart.org only for EXCEL
There are a lot of very smart guys over there that can easily help you whip up something.
http://www.mrexcel.com/
|
Top
|
|
|
|
#180845 - 2007-09-26 08:46 AM
Re: Need a script that read/collect and creates...
[Re: NTDOC]
|
Gargoyle
MM club member
Registered: 2004-03-09
Posts: 1597
Loc: Valley of the Sun (Arizona, US...
|
If your files are CSV files, then Kix can do what you want and output them to a CSV file as well.
Using your example...
;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
This does require the use of the UDF InArray so make sure you include it.
This Code sample is 100% UNTESTED.
This is just an example of one way to accomplish your task.
_________________________
Today is the tomorrow you worried about yesterday.
|
Top
|
|
|
|
#180849 - 2007-09-26 09:56 AM
Re: Need a script that read/collect and creates...
[Re: Gargoyle]
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
No need for arrays, just redirect the output to a file as you read it.
I've kept it very simple and used RedirectOutput() to save strokes.
Input and ouput is assumed to be CSV (comma "," seperated values)
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
This code is 100% tested.
|
Top
|
|
|
|
#180867 - 2007-09-26 05:38 PM
Re: Need a script that read/collect and creates...
[Re: BradV]
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
|
Some ideas based on the original request - read a file (excel or other) and group the data by manager. First a simple example reading data from Excel and gathering it in a temporary file. This could also be done if the source is a CSV-type file.
The second example shows how to take that temporary data and create CSV files for each manager. You could use the xlLib to create new XLS files as well.
Glenn
;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
You now have a file that looks like this, with the data from the spreadsheet:
[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
and so on...
This groups users by manager, with the extracted data. You can then use the EnumINI() UDF to first get an array of manager IDs, and then a list of users by manager.
Of course, you could read a text file, use Split(string, ';') to convert to an array and write to the hash file in pretty much the same way. In the example below, use the CSV() UDF to output properly formatted CSV records.
; 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
_________________________
Actually I am a Rocket Scientist!
|
Top
|
|
|
|
#180885 - 2007-09-26 11:30 PM
Re: Need a script that read/collect and creates...
[Re: 1984]
|
1984
Starting to like KiXtart
Registered: 2003-08-14
Posts: 150
|
Oh sorry Glenn, I had a typo in the function part when pasting code.
However now I get this error :
"invalid method/function call: missing required parameter 3!" referring to the line $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet) in function section xlRangeValue
; 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
|
Top
|
|
|
|
#180893 - 2007-09-27 05:49 AM
Re: Need a script that read/collect and creates...
[Re: Glenn Barnas]
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
|
This should get you started reading your Excel file - make sure you download the latest xlLib from the page referenced by my earlier post near the top of this thread.
This script module creates the HASH.INI file from data in the spreadsheet - part 2 of the code I posted earlier can enumerate this INI file and generate the CSV files, one for each manager.
I've used your data and confirmed that the HASH.INI file is created as expected using this script.
Glenn
;; 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
_________________________
Actually I am a Rocket Scientist!
|
Top
|
|
|
|
#180903 - 2007-09-27 01:56 PM
Re: Need a script that read/collect and creates...
[Re: 1984]
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
Richard: Nothing happens!
Well, I tested it (again) and it works fine.
Are your files comma (",") delimited? Did you run the script from the command line? Are there 4 fields on each line as in your example?
Here is the script again, but with a little more debugging information:
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
|
Top
|
|
|
|
#180915 - 2007-09-27 06:15 PM
Re: Need a script that read/collect and creates...
[Re: Richard H.]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
As far as I can see, 1984 says the files are semicolumn (";") delimited
userX;UnitA;Paris;ManA
user54;Unit34;London;ManF
|
Top
|
|
|
|
Moderator: Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart
|
1 registered
(Allen)
and 466 anonymous users online.
|
|
|