#171184 - 2006-12-07 06:35 PM
read cell from excel then validate data
|
booey
Getting the hang of it
Registered: 2005-07-25
Posts: 76
Loc: USA
|
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.
|
Top
|
|
|
|
#171187 - 2006-12-07 07:54 PM
Re: read cell from excel then validate data
[Re: booey]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
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
|
Top
|
|
|
|
#171200 - 2006-12-07 10:54 PM
Re: read cell from excel then validate data
[Re: Witto]
|
booey
Getting the hang of it
Registered: 2005-07-25
Posts: 76
Loc: USA
|
Thanks for the reply. However, I'm not quite sure what your script is doing. Can you please comment it out to indicate? Thanks.
|
Top
|
|
|
|
#171203 - 2006-12-07 11:27 PM
Re: read cell from excel then validate data
[Re: Lonkero]
|
booey
Getting the hang of it
Registered: 2005-07-25
Posts: 76
Loc: USA
|
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.
|
Top
|
|
|
|
#171226 - 2006-12-08 11:10 AM
Re: read cell from excel then validate data
[Re: Witto]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
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
|
|
Top
|
|
|
|
#171233 - 2006-12-08 02:26 PM
Re: read cell from excel then validate data
[Re: booey]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
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
|
|
Top
|
|
|
|
#171324 - 2006-12-11 07:10 PM
Re: read cell from excel then validate data
[Re: Witto]
|
booey
Getting the hang of it
Registered: 2005-07-25
Posts: 76
Loc: USA
|
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.
|
Top
|
|
|
|
#171332 - 2006-12-12 12:11 AM
Re: read cell from excel then validate data
[Re: booey]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
we need a script that will look at cell C6 in Excel and save that value as a variable.
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
|
|
Top
|
|
|
|
#171370 - 2006-12-12 05:46 PM
Re: read cell from excel then validate data
[Re: Witto]
|
booey
Getting the hang of it
Registered: 2005-07-25
Posts: 76
Loc: USA
|
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.
|
Top
|
|
|
|
#171377 - 2006-12-12 06:05 PM
Re: read cell from excel then validate data
[Re: Mart]
|
booey
Getting the hang of it
Registered: 2005-07-25
Posts: 76
Loc: USA
|
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.
|
Top
|
|
|
|
#171384 - 2006-12-12 10:07 PM
Re: read cell from excel then validate data
[Re: Gargoyle]
|
booey
Getting the hang of it
Registered: 2005-07-25
Posts: 76
Loc: USA
|
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
|
Top
|
|
|
|
#171388 - 2006-12-12 10:17 PM
Re: read cell from excel then validate data
[Re: Lonkero]
|
booey
Getting the hang of it
Registered: 2005-07-25
Posts: 76
Loc: USA
|
Thanks. i was wondering how to do that...
|
Top
|
|
|
|
#171389 - 2006-12-12 10:19 PM
Re: read cell from excel then validate data
[Re: booey]
|
booey
Getting the hang of it
Registered: 2005-07-25
Posts: 76
Loc: USA
|
Here you go..
;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
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 507 anonymous users online.
|
|
|