Page 2 of 3 <123>
Topic Options
#180929 - 2007-09-28 12:45 AM Re: Need a script that read/collect and creates... [Re: Witto]
1984 Offline
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 Offline
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
#180961 - 2007-09-28 03:19 PM Re: Need a script that read/collect and creates... [Re: Witto]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
The original scenario was based on Excel file and your code seems best matching my need.

Can you please explain what you mean by; 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.

Do you mean to filter out managers in Excel to get a list of all managers?

How do I gather all the manager names in an array in execl?
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"

Top
#180965 - 2007-09-28 04:17 PM Re: Need a script that read/collect and creates... [Re: 1984]
Glenn Barnas Administrator Offline
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! \:D

Top
#180966 - 2007-09-28 04:38 PM Re: Need a script that read/collect and creates... [Re: 1984]
Witto Offline
MM club member
*****

Registered: 2004-09-29
Posts: 1828
Loc: Belgium
 Code:
$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
 Code:
...
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
#180969 - 2007-09-28 06:01 PM Re: Need a script that read/collect and creates... [Re: Witto]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
What can I say! Just beautiful! :-)

Witto your code is exactly what I looked for. I am very very grateful for your help and feedback. You have my 5 star!

It does exactly what I wanted and tried to state with this post.

A last wondering to make the hole process complete:

How do we filter out (remove) the managers form each new created list, and name each new created list after the manager the list belong to, instead of book1, book2 book3.....

That would make the hole process full automated!
:-)

Top
#180970 - 2007-09-28 06:56 PM Re: Need a script that read/collect and creates... [Re: 1984]
Witto Offline
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
#180974 - 2007-09-29 09:38 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
Can you please explain what you mean by; 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.
Do you mean to filter out managers in Excel to get a list of all managers?
Recursion From Wikipedia, the free encyclopedia
 Originally Posted By: Wikipedia
Recursion is the process a procedure goes through when one of the steps of the procedure involves rerunning the entire same procedure.
I wanted to say look at every manager name in te D column, see if it is the first time you read the name and if yes, put it in a collection or array

 Originally Posted By: 1984
How do I gather all the manager names in an array in execl?
Array From Wikipedia, the free encyclopedia
 Originally Posted By: Wikipedia
In computer science an array is a data structure consisting of a group of elements that are accessed by indexing.
I just wanted to say an array in KiXtart, not in Excel VBA.

Top
#180980 - 2007-09-29 08:45 PM Re: Need a script that read/collect and creates... [Re: Witto]
1984 Offline
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:

 Code:
$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:

 Code:
$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
#180983 - 2007-09-30 05:06 PM 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
You are using variables which I cannot figure out what they really represent and do, and how you get their values
 Code:
$xlDown = -4121 
$xlToLeft = -4159 
$xlToRight = -4161 
$xlUp = -4162

Nothing to understand about, just take it because they are like that. In fact those numbers are constants. See the link in my first post.
Microsoft Excel Constants (Excel 2003 VBA Language Reference)

Top
#180984 - 2007-09-30 05:36 PM Re: Need a script that read/collect and creates... [Re: Witto]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
1984:

I'm about to post my xlLib UDF, which will make working with Excel a breeze. I'm also going to post a script that exercises / demonstrates how to use each of the functions in the library. You might want to hold off for a few hours... It's Sunday - supposedly a day of rest and relaxation for most of us. ;\)

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#180985 - 2007-10-01 12:46 AM Re: Need a script that read/collect and creates... [Re: Glenn Barnas]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
rest? what rest... heheh... no rest for me -:) almost forgot there is relaxing days ... "have" to keep damn deadlines...

For me, you are wonderful people helping others around the globe freely... sometimes makes one believe in mankind..

Wish you happy relaxing day, and all the credits to you and kix!

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

Top
#180993 - 2007-10-01 12:52 PM Re: Need a script that read/collect and creates... [Re: 1984]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
Witto, can you explain this row:

$RC = $objExcel.Selection.AutoFilter(4, $Manager)

What does the figure "4" doing?
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"

Top
#180994 - 2007-10-01 01:18 PM Re: Need a script that read/collect and creates... [Re: 1984]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
 Quote:
You are using variables which I cannot figure out what they really represent and do, and how you get their values:


Go to msdn.microsoft.com and search for "excel constants" - this should return a list of pages - the first one of them will bring you to a page that defines each of the constants and their values. Each value has a specific meaning, as you will see. The page I'm looking at is http://msdn2.microsoft.com/en-us/library/aa221100(office.11).aspx.

When programming in VB, there are include files that define readonly vars with these values, thus they are "constants". The only way to simulate this in Kix is to declare them as globals, but they won't be read-only. This way, you use the constant name instead of the number, which makes reading the source code much easier. I thought about adding some commonly used values as Globals in my xlLib UDFs, but wasn't sure of the impact of so many vars. Maybe after more testing.

Anyway, that's where these weird numbers come from... (and how you can figure out what they mean and where to use them)

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#180995 - 2007-10-01 01:38 PM Re: Need a script that read/collect and creates... [Re: Glenn Barnas]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
Ok thanx for the wired number explanation Glenn,

BTW,
To make it easier for me to work with the big list, I limited the list to 5 column A-E. The last column "E" contains empty cells, with a data formula where I can chose "Active" or "Inactive" text as "Status" for each user in column "E". Note the column cells are empty.

 Code:
A	B	C	D	E
ID:	Unit:	Site:	Manager:	Status:



Im still using Wittos code, would be happy to see how your xlib codes could help me out in my case. :-)
_________________________
"... 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 Administrator Offline
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:

 Code:
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! \:D

Top
#180999 - 2007-10-01 03:39 PM Re: Need a script that read/collect and creates... [Re: Glenn Barnas]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
Great Glenn,
I got the zip, I play around, see what can achive.

Thanx a lot
/CY
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"

Top
#181004 - 2007-10-01 06:16 PM 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
Witto, can you explain this row:
$RC = $objExcel.Selection.AutoFilter(4, $Manager)
What does the figure "4" doing?

You should use the Macro recorder and edit the code you recorded.
If you would do so, you would notice after adding an autofilter, starting the recorder and recording the selection of "ManA" in column D, the recorder recorded this code for you:
 Code:
    Selection.AutoFilter Field:=4, Criteria1:="ManA"

4 points to the fourth column in your autofilter

Top
#181006 - 2007-10-01 08:29 PM Re: Need a script that read/collect and creates... [Re: Witto]
1984 Offline
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.


 Code:
;*************************************************************************
;  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 Offline
MM club member
*****

Registered: 2004-09-29
Posts: 1828
Loc: Belgium
Just some questions at first glance
 Code:
;Select data range (cell at bottom right of data range)
$RC = $objExcel.Range("A1:P1285")
Is your excel sheet always 1285 rows deep?

 Code:
    ;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
Page 2 of 3 <123>


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

Who's Online
0 registered and 507 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.078 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