|
|
|||||||
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. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
Thanks for the reply. However, I'm not quite sure what your script is doing. Can you please comment it out to indicate? Thanks. |
||||||||
|
|
|||||||
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? |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
yes, you can. let us know if we can help. |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
Here are more comments
|
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
|
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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!!!
|
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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) |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
Thanks. i was wondering how to do that... |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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.
|
||||||||
|
|
|||||||
That works great. Thanks for all the help. |