booey
(Getting the hang of it)
2006-12-07 06:35 PM
read cell from excel then validate data

Hi.

Some background on the project... We need to create a script that will read a cell from an Excel file, validate it against another data source, and then move it based on the results.

For example, we need a script that will look at cell C6 in Excel and save that value as a variable. Then it will look in another file (Excel, Access, txt, etc) and if that variable is equal to any value in that data source, move it to a certain folder.

I'm not really a programmer, so I'm not sure what the best way to do this is. However, is this something that Kixtart can do? If so, please lead me in the right direction.

Thanks.


Witto
(MM club member)
2006-12-07 07:54 PM
Re: read cell from excel then validate data

Code:
If NOT @LOGONMODE
	Break On
Else
	Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit","On")
$RC = SetOption("NoVarsInStrings","On")
$RC = SetOption("NoMacrosInStrings","On")
$RC = SetOption("WrapAtEOL","On")

Dim $objExcel, $File, $Value
$File = "\\Server\Share\Folder\WorkBook.xls"
$objExcel = CreateObject("Excel.Application")
If @error = 0
	$RC = $objExcel.Workbooks.Open($File)
	$Value = $objExcel.Range("C6").Value
	? $Value
Else
	? "Error creating Excel object"
	? "Error " + @ERROR + ": " + @SERROR
EndIf


booey
(Getting the hang of it)
2006-12-07 10:54 PM
Re: read cell from excel then validate data

Thanks for the reply. However, I'm not quite sure what your script is doing. Can you please comment it out to indicate?
Thanks.


LonkeroAdministrator
(KiX Master Guru)
2006-12-07 11:09 PM
Re: read cell from excel then validate data

it opens excel file and reads a value from a cell.
using range() instead of cell() though.


so, which part you have hard time understanding?


booey
(Getting the hang of it)
2006-12-07 11:27 PM
Re: read cell from excel then validate data

Ok. I thought part of the script was to compare the value of a cell to another data source and then do some action to it.

Say I have another excel file to compare it to. In column A there is a list of names in A1, A2, etc and in column B there is a folder path for each value in column A. Can I compare the value from the first excel file to the second excel files column A and then move the file to the folder path located in column two of the second file?

I hope that makes sense.

thanks.


LonkeroAdministrator
(KiX Master Guru)
2006-12-07 11:50 PM
Re: read cell from excel then validate data

yes, you can.

let us know if we can help.


Witto
(MM club member)
2006-12-08 12:02 AM
Re: read cell from excel then validate data

Originally Posted By: booey
we need a script that will look at cell C6 in Excel and save that value as a variable.

That is all the code does. I hoped the example could help you to extract a value from another kind of document.


Witto
(MM club member)
2006-12-08 11:10 AM
Re: read cell from excel then validate data

Here are more comments
;Script Options
If NOT @LOGONMODE
    Break On
Else
    Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit","On")
$RC = SetOption("NoVarsInStrings","On")
$RC = SetOption("NoMacrosInStrings","On")
$RC = SetOption("WrapAtEOL","On")

;Declare variables
Dim $objExcel, $File, $Value
;Path to Excel sheet
$File = "\\Server\Share\Folder\WorkBook.xls"
;Create Excel object
$objExcel = CreateObject("Excel.Application")
;Check if the object was created
If @error = 0
   
;If it was created, open file
    $RC = $objExcel.Workbooks.Open($File)
   
;Catch the value in cell C6
    $Value = $objExcel.Range("C6").Value
   
;Screen output of variable $Value
    ? $Value
Else
    ;If Excel object was not created, show error message
    ? "Error creating Excel object"
    ? "Error " + @ERROR + ": " + @SERROR
EndIf


Glenn BarnasAdministrator
(KiX Supporter)
2006-12-08 01:53 PM
Re: read cell from excel then validate data

look at this post for a library of Excel functions. Once you load such a library, you can call the functions like any other Kix command.

Glenn


Witto
(MM club member)
2006-12-08 02:26 PM
Re: read cell from excel then validate data

If NOT @LOGONMODE
    Break On
Else
    Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit","On")
$RC = SetOption("NoVarsInStrings","On")
$RC = SetOption("NoMacrosInStrings","On")
$RC = SetOption("WrapAtEOL","On")

DIM $objExcel, $File, $Value
$File = "\\Server\Share\Dir\WorkBook1.xls"
$objExcel = CreateObject("Excel.Application")
If @ERROR = 0
   
$RC = $objExcel.Workbooks.Open($File)
   
$Value = $objExcel.Range("C6").Value
   
$objExcel.Close
Else
    ? "Error creating first Excel object"
    ? "Error " + @ERROR + ": " + @SERROR
    Quit @ERROR
EndIf

Dim $Values[1,0], $i
$i = 0
$File = "\\Server\Share\Dir\WorkBook2.xls"
$objExcel = CreateObject("Excel.Application")
If @ERROR = 0
   
$RC = $objExcel.Workbooks.Open($File)
   
While NOT $objExcel.Range("A"+($i+1)).Value = ""
        ReDim preserve $Values[1,$i]
       
$Values[0,$i] = $objExcel.Range("A"+($i+1)).Value
       
$Values[1,$i] = $objExcel.Range("B"+($i+1)).Value
       
$i = $i + 1
   
Loop
    $i = $i - 1
   
$objExcel.Close
Else
    ? "Error creating second Excel object"
    ? "Error " + @ERROR + ": " + @SERROR
    Quit @ERROR
EndIf

DIM $j
For $j = 0 To $i Step 1
   
If $Values[0,$j] = $Value
        ? $Values[1,$j] ;Here is the value you were looking for
        $j = $i
    EndIf
Next


booey
(Getting the hang of it)
2006-12-11 07:10 PM
Re: read cell from excel then validate data

Thanks for the scripts. After looking through them, I have a couple questions.

First, Can you please explain what the following lines do? Also, can you please explain what the $i variable is for throughout the script?

If @ERROR = 0
$RC = $objExcel.Workbooks.Open($File)
While NOT $objExcel.Range("A"+($i+1)).Value = ""
ReDim preserve $Values[1,$i]
$Values[0,$i] = $objExcel.Range("A"+($i+1)).Value
$Values[1,$i] = $objExcel.Range("B"+($i+1)).Value
$i = $i + 1
Loop
$i = $i - 1
$objExcel.Close

Also, please explain what this part does:

DIM $j
For $j = 0 To $i Step 1
If $Values[0,$j] = $Value
? $Values[1,$j] ;Here is the value you were looking for
$j = $i
EndIf

Finally, how do I save/move the file to the location in column B or the second excel file. The file will need to be moved based on the corresponding value in column B. For example, the script looks in C6 in file 1 and finds that value in column A of file 2. In the row of file 2 where the data was found, column B contains the path where the file 1 should be moved.


Witto
(MM club member)
2006-12-12 12:11 AM
Re: read cell from excel then validate data

Originally Posted By: booey
we need a script that will look at cell C6 in Excel and save that value as a variable.

Originally Posted By: booey
Say I have another excel file to compare it to. In column A there is a list of names in A1, A2, etc and in column B there is a folder path for each value in column A. Can I compare the value from the first excel file to the second excel files column A and then move the file to the folder path located in column two of the second file?

Just create 2 Excel workbooks.
In your first workbook, on the first page, you put in cell C6 a value.
In your second workbook, you put on the first page in column A, starting at A1, the values you want to compare to. in column B, starting at B1, you write the file paths where the file should be copied to.
Now replace the lines
$File1 = "D:\scripts\test\Workbook1.xls"
and
$File2 = "D:\scripts\test\WorkBook2.xls"
in the example script and run it.
$Values[1,$j]
should return the value in column B of the second file that you were looking for.
I changed my script because Excel was not properly closed after ending the script!!!
;Set script options
If NOT @LOGONMODE
    Break On
Else
    Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit","On")
$RC = SetOption("NoVarsInStrings","On")
$RC = SetOption("NoMacrosInStrings","On")
$RC = SetOption("WrapAtEOL","On")

Dim $objExcel, $File1, $File2, $Value
;$Values is a 2-dimensional array
Dim $Values[1,0], $i
;$i is a counter
$i = -1
;(Path and) filename of first Excel workbook
$File1 = "D:\scripts\test\Workbook1.xls"
;(Path and) filename of second Excel workbook
$File2 = "D:\scripts\test\Workbook2.xls"

;Create Excel object
$objExcel = CreateObject("Excel.Application")
;If creation object failed, an @ERROR <> 0 is returned
If @ERROR = 0

   
;Open first Excel workbook
    $RC = $objExcel.Workbooks.Open($File1)
   
;Catch value in cell C6
    $Value = $objExcel.Range("C6").Value
   
;Close workbook
    $objExcel.Application.Quit
   
   
;Open second Excel workbook
    $RC = $objExcel.Workbooks.Open($File2)
   
;Loop as long as values are found in column A
    While NOT $objExcel.Range("A"+($i+2)).Value = ""
        ;Add 1 to counter $i
        $i = $i + 1
       
;Resize 2-dimensional array $Values, preserving existing data
        ReDim preserve $Values[1,$i]
       
;Put value from column A in first dimension of array
        $Values[0,$i] = $objExcel.Range("A"+($i+1)).Value
       
;Put value from column B in second dimension of array
        $Values[1,$i] = $objExcel.Range("B"+($i+1)).Value
   
Loop
    ;Close workbook
    $objExcel.Application.Quit
   
Else

    ;Error handling
    ? "Error creating Excel object"
    ? "Error " + @ERROR + ": " + @SERROR
    ;End the KiXtart script
    Quit @ERROR

EndIf

;$j is a counter
Dim $j
For $j = 0 To $i Step 1
   
;compare $Value with every value in the first dimension
    ;of the 2-dimensional array
    ;If values match, go into the if statement
    If $Values[0,$j] = $Value
        ;Here you can write your MOVE command
        Move $File1 $Values[1,$j]
       
;Error handling if move fails
        If @ERROR
            ? "Error moving file " + $File1
            ? "Error " + @ERROR + ": " + @SERROR
        EndIf
        ;The value was found, end the for/next by making $j equal to $i
        $j = $i
    EndIf
Next


booey
(Getting the hang of it)
2006-12-12 05:46 PM
Re: read cell from excel then validate data

Thank you very much for the help. This helps out a lot.

Is is possible to set the $File1 variable to be any excel file (*.xls)? The way I envision this script working is that it is set to run every 5 minutes or so. Someone will drop an Excel file in the folder with any filename and the script will pull it and process it. The filenames will vary from file to file. Is it possible to do this?

Thanks.


Mart
(KiX Supporter)
2006-12-12 05:52 PM
Re: read cell from excel then validate data

Something like this might do the trick.
You need to include or call the DirList UDF in the script.

Code:
Break on

$xlsfiles = DIRLIST("c:\*.xls",2)

For Each $xlsfile in $xlsfiles
	;do some stuff here.
Next


DirList() - Returns an array with a list of files in a given directory


booey
(Getting the hang of it)
2006-12-12 06:05 PM
Re: read cell from excel then validate data

Thanks for the info. What do I need to do to put a UDF in a script. Do I just have to type DirList and it will recognize it or do I have to install the UDF somewhere? This is probably a stupid question, but I'm new to UDF's.

Thanks.


Gargoyle
(MM club member)
2006-12-12 06:20 PM
Re: read cell from excel then validate data

Search the UDF library for the DirList() udf. Copy all of the code and paste it AS IS into your script at the very end.

Follow the directions given with the UDF to pass the proper parameters to it. (Use Marts code as an example)


booey
(Getting the hang of it)
2006-12-12 10:07 PM
Re: read cell from excel then validate data

Thanks. I added the DirList code to the bottom of the script and edited the $File1 variable, but when ran nothing is happening. The script finishes without errors, but nothing happens. It just goes back to a command prompt. Do you have any idea what's going on? The complete script is below.

The script ran fine until I added the UDF and changed the $file1 variable, but I'm not sure what else I need to edit to get it to work. Any help would be appreciated. Thanks.

;Set script options
If Not @LOGONMODE
Break On
Else
Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit","On")
$RC = SetOption("NoVarsInStrings","On")
$RC = SetOption("NoMacrosInStrings","On")
$RC = SetOption("WrapAtEOL","On")

Dim $objExcel, $File1, $File2, $Value
;$Values is a 2-dimensional array
Dim $Values[1,0], $i
;$i is a counter
$i = -1
;(Path and) filename of first Excel workbook
$File1 = DIRLIST("C:\kix\verify\*.xls",2)
;(Path and) filename of second Excel workbook
$File2 = "C:\kix\verify\SIT.xls"

;Create Excel object
$objExcel = CreateObject("Excel.Application")
;If creation object failed, an @ERROR <> 0 is returned
If @ERROR = 0

;Open first Excel workbook
$RC = $objExcel.Workbooks.Open($File1)
;Catch value in cell C6
$Value = $objExcel.Range("C6").Value
;Close workbook
$objExcel.Application.Quit

;Open second Excel workbook
$RC = $objExcel.Workbooks.Open($File2)
;Loop as long as values are found in column A
While Not $objExcel.Range("A"+($i+2)).Value = ""
;Add 1 to counter $i
$i = $i + 1
;Resize 2-dimensional array $Values, preserving existing data
ReDim preserve $Values[1,$i]
;Put value from column A in first dimension of array
$Values[0,$i] = $objExcel.Range("A"+($i+1)).Value
;Put value from column B in second dimension of array
$Values[1,$i] = $objExcel.Range("B"+($i+1)).Value
Loop
;Close workbook
$objExcel.Application.Quit

Else

;Error handling
? "Error creating Excel object"
? "Error " + @ERROR + ": " + @SERROR
;End the KiXtart script
Quit @ERROR

EndIf

;$j is a counter
Dim $j
For $j = 0 to $i Step 1
;compare $Value with every value in the first dimension
;of the 2-dimensional array
;If values match, go into the if statement
If $Values[0,$j] = $Value
;Here you can write your MOVE command
Move $File1 $Values[1,$j]
;Error handling if move fails
If @ERROR
? "Error moving file " + $File1
? "Error " + @ERROR + ": " + @SERROR
EndIf
;The value was found, end the for/next by making $j equal to $i
$j = $i
EndIf
Next

;FUNCTION DirList
;
;AUTHOR Jens Meyer (sealeopard@usa.net)
;
;ACTION Returns an array with a list of files in a given directory
;
;VERSION 1.2
;
;KIXTART 4.12
;
;SYNTAX DIRLIST(DIRNAME [,OPTIONS])
;
;PARAMETERS DIRNAME
; Required string containing the directory name
;
; OPTIONS
; Optional value for additional options, options are set bitwise
; 1 = include directories (denoted by a backslash) that match the search mask
; 2 = include full path
; 4 = search all subdirectories
;
;RETURNS array with a list of files, otherwise an empty string
;
;REMARKS none
;
;DEPENDENCIES none
;
;EXAMPLE $dirlist = DIRLIST("c:\*.*",1+2+4)
;
;KIXTART BBS http://www.kixtart.org/cgi-bin/ultimatebb.cgi?ubb=get_topic&f=12&t=000090
;
Function dirlist($dirname, optional $options)
Dim $filename, $counter, $filepath, $mask
Dim $list, $sublist, $subcounter

$counter=-1

$dirname=Trim($dirname)
If $dirname=''
$dirname=@CURDIR
EndIf
If Right($dirname,1)='\'
$dirname=Left($dirname,Len($dirname)-1)
EndIf
If GetFileAttr($dirname) & 16
$mask='*.*'
Else
$mask=SubStr($dirname,InStrRev($dirname,'\')+1)
$dirname=Left($dirname,Len($dirname)-Len($mask)-1)
EndIf

ReDim $list[10]
$filename=Dir($dirname+'\'+$mask)
While $filename<>'' And @ERROR=0
If $filename<>'.' And $filename<>'..'
Select
Case (GetFileAttr($dirname+'\'+$filename) & 16)
If $options & 1
$counter=$counter+1
If $options & 2
$list[$counter]=$dirname+'\'+$filename+'\'
Else
$list[$counter]=$filename+'\'
EndIf
EndIf
If ($options & 4)
$sublist=dirlist($dirname+'\'+$filename+'\'+$mask,4)
If UBound($sublist)+1
ReDim preserve $list[UBound($list)+UBound($sublist)+1]
For $subcounter=0 to UBound($sublist)
$counter=$counter+1
If $options & 2
$list[$counter]=$dirname+'\'+$filename+'\'+$sublist[$subcounter]
Else
$list[$counter]=$filename+'\'+$sublist[$subcounter]
EndIf
Next
EndIf
EndIf
Case ($options & 2)
$counter=$counter+1
$list[$counter]=$dirname+'\'+$filename
Case 1
$counter=$counter+1
$list[$counter]=$filename
EndSelect
If $counter mod 10
ReDim preserve $list[$counter+10]
EndIf
EndIf
$filename = Dir('')
Loop

If $counter+1
ReDim preserve $list[$counter]
Else
$list=''
EndIf

If $mask<>'*.*' And ($options & 4)
$filename=Dir($dirname+'\*.*')
While $filename<>'' And @ERROR=0
If $filename<>'.' And $filename<>'..'
If (GetFileAttr($dirname+'\'+$filename) & 16)
$sublist=dirlist($dirname+'\'+$filename+'\'+$mask,4)
If UBound($sublist)+1
ReDim preserve $list[UBound($list)+UBound($sublist)+1]
For $subcounter=0 to UBound($sublist)
$counter=$counter+1
If $options & 2
$list[$counter]=$dirname+'\'+$filename+'\'+$sublist[$subcounter]
Else
$list[$counter]=$filename+'\'+$sublist[$subcounter]
EndIf
Next
EndIf
EndIf
EndIf
$filename = Dir('')
Loop
EndIf

If $counter+1
ReDim preserve $list[$counter]
Else
$list=''
EndIf

$dirlist=$list
EndFunction


LonkeroAdministrator
(KiX Master Guru)
2006-12-12 10:14 PM
Re: read cell from excel then validate data

uhm.
guess it's time for you to learn to use the ubb-code tags.
click on the edit box at the bottom of your post and add [code] at the front of your code and [/code] at the end.


booey
(Getting the hang of it)
2006-12-12 10:17 PM
Re: read cell from excel then validate data

Thanks. i was wondering how to do that...

booey
(Getting the hang of it)
2006-12-12 10:19 PM
Re: read cell from excel then validate data

Here you go..
Code:
;Set script options
If Not @LOGONMODE
    Break On
Else
    Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit","On")
$RC = SetOption("NoVarsInStrings","On")
$RC = SetOption("NoMacrosInStrings","On")
$RC = SetOption("WrapAtEOL","On")

Dim $objExcel, $File1, $File2, $Value
;$Values is a 2-dimensional array
Dim $Values[1,0], $i
;$i is a counter
$i = -1
;(Path and) filename of first Excel workbook
$File1 = DIRLIST("C:\kix\verify\*.xls",2)
;(Path and) filename of second Excel workbook
$File2 = "C:\kix\verify\SIT.xls"

;Create Excel object
$objExcel = CreateObject("Excel.Application")
;If creation object failed, an @ERROR <> 0 is returned
If @ERROR = 0

    ;Open first Excel workbook
    $RC = $objExcel.Workbooks.Open($File1)
    ;Catch value in cell C6
    $Value = $objExcel.Range("C6").Value
    ;Close workbook
    $objExcel.Application.Quit
   
    ;Open second Excel workbook
    $RC = $objExcel.Workbooks.Open($File2)
    ;Loop as long as values are found in column A
    While Not $objExcel.Range("A"+($i+2)).Value = ""
        ;Add 1 to counter $i
        $i = $i + 1
        ;Resize 2-dimensional array $Values, preserving existing data
        ReDim preserve $Values[1,$i]
        ;Put value from column A in first dimension of array
        $Values[0,$i] = $objExcel.Range("A"+($i+1)).Value
        ;Put value from column B in second dimension of array
        $Values[1,$i] = $objExcel.Range("B"+($i+1)).Value
    Loop
    ;Close workbook
    $objExcel.Application.Quit
   
Else

    ;Error handling
    ? "Error creating Excel object"
    ? "Error " + @ERROR + ": " + @SERROR
    ;End the KiXtart script
    Quit @ERROR

EndIf

;$j is a counter
Dim $j
For $j = 0 to $i Step 1
    ;compare $Value with every value in the first dimension
    ;of the 2-dimensional array
    ;If values match, go into the if statement
    If $Values[0,$j] = $Value
        ;Here you can write your MOVE command
        Move $File1 $Values[1,$j]
        ;Error handling if move fails
        If @ERROR
            ? "Error moving file " + $File1
            ? "Error " + @ERROR + ": " + @SERROR
        EndIf
        ;The value was found, end the for/next by making $j equal to $i
        $j = $i
    EndIf
Next

;FUNCTION      DirList
;
;AUTHOR        Jens Meyer (sealeopard@usa.net)
;
;ACTION        Returns an array with a list of files in a given directory
;
;VERSION       1.2
;
;KIXTART       4.12
;
;SYNTAX        DIRLIST(DIRNAME [,OPTIONS])
;
;PARAMETERS    DIRNAME
;              Required string containing the directory name
;
;              OPTIONS
;              Optional value for additional options, options are set bitwise
;              1 = include directories (denoted by a backslash) that match the search mask
;              2 = include full path
;              4 = search all subdirectories
;
;RETURNS       array with a list of files, otherwise an empty string
;
;REMARKS       none
;
;DEPENDENCIES  none
;
;EXAMPLE       $dirlist = DIRLIST("c:\*.*",1+2+4)
;
;KIXTART BBS   http://www.kixtart.org/cgi-bin/ultimatebb.cgi?ubb=get_topic&f=12&t=000090
;
Function dirlist($dirname, optional $options)
  Dim $filename, $counter, $filepath, $mask
  Dim $list, $sublist, $subcounter

  $counter=-1

  $dirname=Trim($dirname)
  If $dirname=''
    $dirname=@CURDIR
  EndIf
  If Right($dirname,1)='\'
    $dirname=Left($dirname,Len($dirname)-1)
  EndIf
  If GetFileAttr($dirname) & 16
    $mask='*.*'
  Else
    $mask=SubStr($dirname,InStrRev($dirname,'\')+1)
    $dirname=Left($dirname,Len($dirname)-Len($mask)-1)
  EndIf

  ReDim $list[10]
  $filename=Dir($dirname+'\'+$mask)
  While $filename<>'' And @ERROR=0
    If $filename<>'.' And $filename<>'..'
      Select
      Case (GetFileAttr($dirname+'\'+$filename) & 16)
        If $options & 1
          $counter=$counter+1
          If $options & 2
            $list[$counter]=$dirname+'\'+$filename+'\'
          Else
            $list[$counter]=$filename+'\'
          EndIf
        EndIf
        If ($options & 4)
          $sublist=dirlist($dirname+'\'+$filename+'\'+$mask,4)
          If UBound($sublist)+1
            ReDim preserve $list[UBound($list)+UBound($sublist)+1]
            For $subcounter=0 to UBound($sublist)
              $counter=$counter+1
              If $options & 2
                $list[$counter]=$dirname+'\'+$filename+'\'+$sublist[$subcounter]
              Else
                $list[$counter]=$filename+'\'+$sublist[$subcounter]
              EndIf
            Next
          EndIf
        EndIf
      Case ($options & 2)
        $counter=$counter+1
        $list[$counter]=$dirname+'\'+$filename
      Case 1
        $counter=$counter+1
        $list[$counter]=$filename
      EndSelect
      If $counter mod 10
        ReDim preserve $list[$counter+10]
      EndIf
    EndIf
    $filename = Dir('')
  Loop

  If $counter+1
    ReDim preserve $list[$counter]
  Else
    $list=''
  EndIf

  If $mask<>'*.*' And ($options & 4)
    $filename=Dir($dirname+'\*.*')
    While $filename<>'' And @ERROR=0
      If $filename<>'.' And $filename<>'..'
        If (GetFileAttr($dirname+'\'+$filename) & 16)
          $sublist=dirlist($dirname+'\'+$filename+'\'+$mask,4)
          If UBound($sublist)+1
            ReDim preserve $list[UBound($list)+UBound($sublist)+1]
            For $subcounter=0 to UBound($sublist)
              $counter=$counter+1
              If $options & 2
                $list[$counter]=$dirname+'\'+$filename+'\'+$sublist[$subcounter]
              Else
                $list[$counter]=$filename+'\'+$sublist[$subcounter]
              EndIf
            Next
          EndIf
        EndIf
      EndIf
      $filename = Dir('')
    Loop
  EndIf

  If $counter+1
    ReDim preserve $list[$counter]
  Else
    $list=''
  EndIf

  $dirlist=$list
EndFunction


Witto
(MM club member)
2006-12-13 12:57 AM
Re: read cell from excel then validate data

You made $File1 an array.
You should do something like Mart said
Code:
For Each $F in $File1
	;Do stuff
Next

Here is a modified script. Do not forget to add the DirList() UDF at the end.
If NOT @LOGONMODE
    Break On
Else
    Break Off
EndIf
DIM $RC
$RC = SetOption("Explicit","On")
$RC = SetOption("NoVarsInStrings","On")
$RC = SetOption("NoMacrosInStrings","On")
$RC = SetOption("WrapAtEOL","On")

DIM $objExcel, $Files, $FilesDir, $File, $File2, $Value, $Values[1,0], $i, $j
$FilesDir = "D:\scripts\test\xls\"
$File2 = "D:\scripts\test\Workbook2.xls"

$objExcel = CreateObject("Excel.Application")
If @ERROR
    ? "Error creating Excel object"
    ? "Error " + @ERROR + ": " + @SERROR
    Quit @ERROR
EndIf

$Files = DirList($FilesDir,2)
For Each $File In $Files
   
   
$i = -1
   
ReDIM $Values[1,0]
   
   
$RC = $objExcel.Workbooks.Open($File)
   
$Value = $objExcel.Range("C6").Value
   
$objExcel.Application.Quit
   
   
$RC = $objExcel.Workbooks.Open($File2)
   
While NOT $objExcel.Range("A"+($i+2)).Value = ""
        $i = $i + 1
       
ReDIM preserve $Values[1,$i]
       
$Values[0,$i] = $objExcel.Range("A"+($i+1)).Value
       
$Values[1,$i] = $objExcel.Range("B"+($i+1)).Value
   
Loop
    $objExcel.Application.Quit
   
   
For $j = 0 To $i Step 1
       
If $Values[0,$j] = $Value
            Move $File $Values[1,$j]
           
If @ERROR
                ? "Error moving file " + $File
                ? "Error " + @ERROR + ": " + @SERROR
            EndIf
            $j = $i
        EndIf
    Next

Next


booey
(Getting the hang of it)
2006-12-13 04:46 PM
Re: read cell from excel then validate data

That works great. Thanks for all the help.