syntax frustration - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Advanced Scripting » syntax frustration

Page 1 of 1

Topic Options

#110779 - 2003-12-22 06:36 PM syntax frustration
JJscorpio JJscorpio Fresh Scripter Registered: 2002-06-17 Posts: 34 Loc: Houston, Tx	I am trying to read an excel file into an array. Excel sheet is 6 columns wide by about 300 rows deep. The following code seems like it should work... but I keep getting an expected']' error I know I am doing something wrong in my syntax, but not sure what. Code: For $Column=0 to 6 For $cell=0 to 300;ubound($a) $b[$column,$cell]=$oXL.cells($column,$cell) Next Next
Top

#110780 - 2003-12-22 06:37 PM Re: syntax frustration
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	Show us where/how you DIM'd $b
Top

#110781 - 2003-12-22 06:42 PM

Re: syntax frustration

JJscorpio Offline

Fresh Scripter

Registered: 2002-06-17
Posts: 34
Loc: Houston, Tx

This is a modification of the READEXCEL() UDF by Kdyer
I wanted to be able to read and filter the array later in the script without having to read the source file over and over... But I am doing something wrong.

Thank You for your help!
Code:


FUNCTION READEXCEL($cfilename) 
      DIM $Rc,$Column,$Cell,$oXL,$a[300],$b[300]
      ;$a=1,2,3,4,5,6,7;columns and order to be read 
      IF 0=EXIST($cfilename) ;insure the file exists 
            ?'Excel file not found' 
            SLEEP 4 
            RETURN ;leave 
      ENDIF 
      $oXL=Createobject('Excel.application') 
      ;Check to insure that Excel is available 
      IF 0<>@error ?@error ' Excel Application is not found'  
            SLEEP 4  
            RETURN  
      ENDIF 
      $Rc=$oXL.workbooks.open($cfilename) 
      
            For $Column=0 to 6            	
                 For $cell=0 to 300;ubound($a)
                 $b[$column,$cell]=$oXL.cells($column,$cell)
                 Next
            Next
            
$oXL.quit ;quit Excel 
$oXL=0 ;set the object to 0

Top

#110782 - 2003-12-22 06:47 PM Re: syntax frustration
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	Your DIMming is incorrect. First you DIM a one-dimensional array $b[300], then you access the array as a two-dimensional array $b[$column,$cell]. You should DIM the array to it's correct size, which seems to be $b[6,300]. Edited by sealeopard (2003-12-22 06:57 PM) _________________________ There are two types of vessels, submarines and targets.
Top

#110783 - 2003-12-22 06:51 PM Re: syntax frustration
Les Les KiX Master Registered: 2001-06-11 Posts: 12734 Loc: fortfrances.on.ca	This is still not complete code. I don't see the EndFuction nor do I see where and how you call the UDF. Also, your vars ar all DIMmed local so how do you get the data ouside of the function? _________________________ Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
Top

#110784 - 2003-12-22 06:55 PM Re: syntax frustration
JJscorpio JJscorpio Fresh Scripter Registered: 2002-06-17 Posts: 34 Loc: Houston, Tx	Jens, Thank you! That was the problem... I have found lots of information on single dimension arrays, but very little on multidimensional. This is the second "fundamental" array question you have helped me understand Thank you.
Top

#110785 - 2003-12-22 07:02 PM

Re: syntax frustration

JJscorpio Offline

Fresh Scripter

Registered: 2002-06-17
Posts: 34
Loc: Houston, Tx

Les,
Good point!

That was my next challange, I have not been able to call the results outside the udf! I think I just missed the endfunction in my cut and paste. The complete code is as follows (minus kixform)
Code:


$ListViewEx1.Items.Clear
$Populate = Readexcel('C:\Clients\Able\Price.xls')
;Excel file is a hundred or so rows of data 6 columns wide

            For $LColumn=0 to 6 
            $List = $ListViewEx1.items.add
		           	
                 For $Lcell=0 to ubound($a)
                 List.SubItems($Lcell).text = $b[$Lcolumn,$Lcell]
                 Next
            Next


$Form.Show
While $Form.Visible
  $=Execute($Form.DoEvents())
Loop
Exit 1

;*************************************

FUNCTION READEXCEL($cfilename) 
      DIM $Rc,$Column,$Cell,$oXL,$a[300],$b[6,300]
      ;$a=1,2,3,4,5,6,7;columns and order to be read 
      IF 0=EXIST($cfilename) ;insure the file exists 
            ?'Excel file not found' 
            SLEEP 4 
            RETURN ;leave 
      ENDIF 
      $oXL=Createobject('Excel.application') 
      ;Check to insure that Excel is available 
      IF 0<>@error ?@error ' Excel Application is not found'  
            SLEEP 4  
            RETURN  
      ENDIF 
      $Rc=$oXL.workbooks.open($cfilename) 
      
            For $Column=0 to 6            	
                 For $cell=0 to 300;ubound($a)
                 $b[$column,$cell]=$oXL.cells($column,$cell)
                 Next
            Next
            
$oXL.quit ;quit Excel 
$oXL=0 ;set the object to 0 

ENDFUNCTION

I have a little cleaning to do... I did not use $a at all and need to global dim the array. I have been trying to learn arrays from others code. Not understaing all of it this code is a bit of a mess!

Top

#110786 - 2003-12-22 07:09 PM Re: syntax frustration
Les Les KiX Master Registered: 2001-06-11 Posts: 12734 Loc: fortfrances.on.ca	Generally, DIMming global inside a UDF is frowned upon. Not sure what your skill level is on UDFs, but it might help if you read Jens' FAQ on the topic. How to write a UDF _________________________ Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
Top

#110787 - 2003-12-22 07:11 PM Re: syntax frustration
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	And the KiXtart Manual does explain a) scope issues with DIM/GLOBAL and b) how to return values out of an FUNCTION-ENDFUNCTION construct. _________________________ There are two types of vessels, submarines and targets.
Top

#110788 - 2003-12-22 07:20 PM Re: syntax frustration
JJscorpio JJscorpio Fresh Scripter Registered: 2002-06-17 Posts: 34 Loc: Houston, Tx	Les, I have not written many udf's and am really just starting to get the more advanced concepts available in Kix. I see if you Globally Dim in a UDF you could wipe out values other have used in the scripts. Jens, I will RTFM again and referance the FAQ's (I did RTFM before posting, but did not know the DIM was my problem, so I was not reading the right information) Thank You for the direction. I will follow-up once I think I have the code corrected.
Top

#110789 - 2003-12-22 08:24 PM Re: syntax frustration
JJscorpio JJscorpio Fresh Scripter Registered: 2002-06-17 Posts: 34 Loc: Houston, Tx	OK, after reading up I found that for this to work I would have to DIM the variable globally, which is bad UDF form (due to the potential to step on other main-script variables. So I cannot do what I want (properly). So it seems to me that the Function in this case should be part of the main script instead of a function...
Top

#110790 - 2003-12-22 08:37 PM Re: syntax frustration
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	If I understand you correctly, you want the array $b be available outside the UDF, right? Then you just assign $b to the return variable which is the UDF name with a preceeding $-sign as explained in both the KiXtart Manual and the FAQ Forum. Code: FUNCTION READEXCEL($cfilename) DIM $Rc,$Column,$Cell,$oXL,$a[300],$b[6,300] ;$a=1,2,3,4,5,6,7 ;columns and order to be read IF 0=EXIST($cfilename) ;insure the file exists ?'Excel file not found' SLEEP 4 RETURN ;leave ENDIF $oXL=Createobject('Excel.application') ;Check to insure that Excel is available IF 0<>@error ?@error ' Excel Application is not found' SLEEP 4 RETURN ENDIF $Rc=$oXL.workbooks.open($cfilename) For $Column=0 to 6 For $cell=0 to 300;ubound($a) $b[$column,$cell]=$oXL.cells($column,$cell) Next Next $oXL.quit ;quit Excel $oXL=0 ;set the object to 0 $ReadExcel=$b ENDFUNCTION BTW, to exit UDFs, one should use EXIT and the appropriate error code as illustated in the FAQ Forum and the UDF Guidelines. It is also bad etiquette to rework an existing UDF to make it fit a specific case and not even rename the UDF. UDFs are supposed to be general-purpose functions with specifics provided through parameters. _________________________ There are two types of vessels, submarines and targets.
Top

#110791 - 2003-12-22 09:01 PM Re: syntax frustration
JJscorpio JJscorpio Fresh Scripter Registered: 2002-06-17 Posts: 34 Loc: Houston, Tx	Jens, Yes... that is what I was after. Thank you for all the constructive guidance. I will frequent the FAQ's and work on the etiquette. I am trying to understand the more advanced functions in Kix and am still missing some of the more basic ones
Top

#110792 - 2003-12-22 09:48 PM Re: syntax frustration
Les Les KiX Master Registered: 2001-06-11 Posts: 12734 Loc: fortfrances.on.ca	Jens, Before you beat up JJ too badly, remember that he has adopted Kent's UDF. While Kent is well-intentioned, he does not always write UDFs to your exacting standards. Often they are merely subroutines with a twist. JJ, Not all posted UDFs are ready-to-wear code. Some are just concepts that need further refinement. Kent, Sorry for the criticism. Perhaps some of your UDFs could stand to have a disclaimer. _________________________ Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
Top

#110793 - 2003-12-22 10:02 PM Re: syntax frustration
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	No problem with adopting other people's UDFs, I'm guilty as well. However, I do rename these modified UDF to keep the uniqueness factor. Consider it version control. _________________________ There are two types of vessels, submarines and targets.
Top

#110794 - 2003-12-23 02:26 PM Re: syntax frustration
JJscorpio JJscorpio Fresh Scripter Registered: 2002-06-17 Posts: 34 Loc: Houston, Tx	Thank You All. I have learned quite a bit here and really enjoy the Board. I keep learning I don't know as much as I think I do Happy Holidays to you All.
Top

Page 1 of 1

Previous Topic View All Topics

Index Next Topic

Moderator: Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org