Match values in Array with values in Excel

[drbyte]

I fill an array with values from pdf file's found in a directory.

Now i need to find de corrsponding values in the array and in the excel sheet, and place "X" in the
cell at the left of the corresponding value.

How to do this?

See below the code i made so far to fill the 2D array with the value's

(a lot of code plagarized from Kylomas and soonyee91   )

expandcollapse popup

; ------------------------------------------------------------------------------------
; create pdf files in @scriptdir \test\ for testing
; ------------------------------------------------------------------------------------
Local $result[16]

$result[0] = "Could be any name - ABCD001.pdf"
$result[1] = "Could be any name - ABCD003.pdf"
$result[2] = "Could be any name - ABCD005.pdf"
$result[3] = "Could be any name - ABCD007.pdf"
$result[4] = "Could be any name - ABCD009.pdf"
$result[5] = "material-1a_124567.pdf"
$result[6] = "material-1b_12345_589752467.pdf"
$result[7] = "material-1c_12345_nesting.pdf"
$result[8] = "material-1d_us_04030-2010-09-03.pdf"
$result[9] = "material-1e_us_Certificaat.pdf"
$result[10] = "material-1f_z35_SL1020_01.pdf"
$result[11] = "material-1g_harden_W1305050.pdf"
$result[12] = "material-1h_veredelen_SL1025_02.pdf"
$result[13] = "material-1k_painting.pdf"
$result[14] = "material-1i_ndt_RM0199.pdf"
$result[15] = "material-1j_ndt_Certificaat.pdf"


For $1 = 0 To UBound($result) - 1
    FileOpen(@ScriptDir & '\test\' & $result[$1], 16)
Next
; end creation pdf file's

; list all .pdf type files from a specific directory
$atemp_Files = _FileListToArray(@ScriptDir & '\test\', '*.pdf', 1)
_ArrayDisplay($atemp_Files, "Directory loaded")

; Remove all file names with "material-" in the pdf file
For $i = UBound($atemp_Files) - 1 To 0 Step -1
    If StringInStr($atemp_Files[$i], "material-", 0) Then
        _ArrayDelete($atemp_Files, $i)
    EndIf
Next
_ArrayDisplay($atemp_Files, "Material pdf's removed")

; create final result array with 2 dimensions
Local $aFiles[UBound($atemp_Files)][2]

; populate 1ST dimension of final result array with complete file name (not path)
For $1 = 0 To UBound($atemp_Files) - 1
    $aFiles[$1][0] = $atemp_Files[$1]
Next

; populate 2ND dimension of final result array with stripped out file name
Local $aTmp
For $1 = 1 To UBound($aFiles) - 1
    $aTmp = StringRegExp($aFiles[$1][0], '[a-zA-Z]{1,4}[0-9]{1,3}', 3)
    If IsArray($aTmp) Then $aFiles[$1][1] = $aTmp[0]
Next
_ArrayDisplay($aFiles, "2nd Array filled")
_ArrayDelete($aFiles, 0)

; ------------------------------------------------------------------------------------
; iterate array looking for element with "paint" in the name
; ------------------------------------------------------------------------------------
;~ For $1 = 0 To UBound($aFiles) - 1
;~  If StringInStr($aFiles[$1][1], 'ndt') > 0 Then
;~      ConsoleWrite('Found "ndt" in element ' & $1 & ' of array' & @LF)
;~  EndIf
;~ Next

Thnx in advance!

Edited August 26, 2013 by drbyte

[water]

To proces Excel I would suggest to have a look at the Excel UDF that comes with AutoIt.

[JCEF]

I do not know if I have this right ...
See if this is what you want ...

expandcollapse popup

#include <File.au3>
#include <Array.au3>
#include <Excel.au3>

; ------------------------------------------------------------------------------------
; create pdf files in @scriptdir \test\ for testing
; ------------------------------------------------------------------------------------
Local $result[16]

$result[0] = "Could be any name - ABCD001.pdf"
$result[1] = "Could be any name - ABCD003.pdf"
$result[2] = "Could be any name - ABCD005.pdf"
$result[3] = "Could be any name - ABCD007.pdf"
$result[4] = "Could be any name - ABCD009.pdf"
$result[5] = "material-1a_124567.pdf"
$result[6] = "material-1b_12345_589752467.pdf"
$result[7] = "material-1c_12345_nesting.pdf"
$result[8] = "material-1d_us_04030-2010-09-03.pdf"
$result[9] = "material-1e_us_Certificaat.pdf"
$result[10] = "material-1f_z35_SL1020_01.pdf"
$result[11] = "material-1g_harden_W1305050.pdf"
$result[12] = "material-1h_veredelen_SL1025_02.pdf"
$result[13] = "material-1k_painting.pdf"
$result[14] = "material-1i_ndt_RM0199.pdf"
$result[15] = "material-1j_ndt_Certificaat.pdf"


For $1 = 0 To UBound($result) - 1
    FileOpen(@ScriptDir & '\test\' & $result[$1], 16)
Next
; end creation pdf file's

; list all .pdf type files from a specific directory
$atemp_Files = _FileListToArray(@ScriptDir & '\test\', '*.pdf', 1)
_ArrayDisplay($atemp_Files, "Directory loaded")

; Remove all file names with "material-" in the pdf file
For $i = UBound($atemp_Files) - 1 To 0 Step -1
    If StringInStr($atemp_Files[$i], "material-", 0) Then
        _ArrayDelete($atemp_Files, $i)
    EndIf
Next
_ArrayDisplay($atemp_Files, "Material pdf's removed")

; create final result array with 2 dimensions
Local $aFiles[UBound($atemp_Files)][2]

; populate 1ST dimension of final result array with complete file name (not path)
For $1 = 0 To UBound($atemp_Files) - 1
    $aFiles[$1][0] = $atemp_Files[$1]
Next

; populate 2ND dimension of final result array with stripped out file name
Local $aTmp
For $1 = 1 To UBound($aFiles) - 1
    $aTmp = StringRegExp($aFiles[$1][0], '[a-zA-Z]{1,4}[0-9]{1,3}', 3)
    If IsArray($aTmp) Then $aFiles[$1][1] = $aTmp[0]
Next
_ArrayDisplay($aFiles, "2nd Array filled")




; ------------------------------------------------------------------------------------
; iterate array looking for element with "paint" in the name
; ------------------------------------------------------------------------------------
$oExcel = _ExcelBookOpen(@ScriptDir & "\test\FileExcel.xlsx", 0)

For $1 = 1 To UBound($aFiles) - 1
    For $i = 2 To 21
        If $aFiles[$1][1] = $oExcel.Sheets("Folha1").Range("A" & $i).Value Then
            $oExcel.Sheets("Folha1").Range("B" & $i).Value = StringUpper("x")
        EndIf
    Next
Next

_ExcelBookClose($oExcel)
_ArrayDelete($aFiles, 0)

MsgBox(0, "", "Concluído", 2)
Exit

[soonyee91]

drbyte, 

The concept of the solution for your problem is the same like what we discuss before. Use _arraysearch to check whether there is a match. If there is mark X on the corresponding column.

You can refer to my code below:

expandcollapse popup

#include <File.au3>
#include <Array.au3>
#include <Excel.au3>

; ------------------------------------------------------------------------------------
; create pdf files in @scriptdir \test\ for testing
; ------------------------------------------------------------------------------------
Local $result[16]

$result[0] = "Could be any name - ABCD001.pdf"
$result[1] = "Could be any name - ABCD003.pdf"
$result[2] = "Could be any name - ABCD005.pdf"
$result[3] = "Could be any name - ABCD007.pdf"
$result[4] = "Could be any name - ABCD009.pdf"
$result[5] = "material-1a_124567.pdf"
$result[6] = "material-1b_12345_589752467.pdf"
$result[7] = "material-1c_12345_nesting.pdf"
$result[8] = "material-1d_us_04030-2010-09-03.pdf"
$result[9] = "material-1e_us_Certificaat.pdf"
$result[10] = "material-1f_z35_SL1020_01.pdf"
$result[11] = "material-1g_harden_W1305050.pdf"
$result[12] = "material-1h_veredelen_SL1025_02.pdf"
$result[13] = "material-1k_painting.pdf"
$result[14] = "material-1i_ndt_RM0199.pdf"
$result[15] = "material-1j_ndt_Certificaat.pdf"


For $1 = 0 To UBound($result) - 1
    FileOpen(@ScriptDir & '\test\' & $result[$1], 10)
Next
; end creation pdf file's

; list all .pdf type files from a specific directory
$atemp_Files = _FileListToArray(@ScriptDir & '\test\', '*.pdf', 1)
_ArrayDisplay($atemp_Files, "Directory loaded")

; Remove all file names with "material-" in the pdf file
For $i = UBound($atemp_Files) - 1 To 0 Step -1
    If StringInStr($atemp_Files[$i], "material-", 0) Then
        _ArrayDelete($atemp_Files, $i)
    EndIf
Next
_ArrayDisplay($atemp_Files, "Material pdf's removed")

; create final result array with 2 dimensions
Local $aFiles[UBound($atemp_Files)][2]

; populate 1ST dimension of final result array with complete file name (not path)
For $1 = 0 To UBound($atemp_Files) - 1
    $aFiles[$1][0] = $atemp_Files[$1]
Next

; populate 2ND dimension of final result array with stripped out file name
Local $aTmp
For $1 = 1 To UBound($aFiles) - 1
    $aTmp = StringRegExp($aFiles[$1][0], '[a-zA-Z]{1,4}[0-9]{1,3}', 3)
    If IsArray($aTmp) Then $aFiles[$1][1] = $aTmp[0]
Next
_ArrayDisplay($aFiles, "2nd Array filled")
_ArrayDelete($aFiles, 0)



;this is just to let the workbook looks like your snapshot example
Local $oExcel=_ExcelBookNew()
_ExcelWriteCell($oExcel,"Number",1,1)
_ExcelWriteCell($oExcel,"Report",1,2)
_ExcelWriteCell($oExcel,"ABCD001",2,1)
$oExcel.Range("A2").Select
 $oExcel.Selection.AutoFill($oExcel.Range("A2:A21"), 0)

 ; this is the code for matching with the column A with the array you got.
For $i=0 to 19; based on the example you give: ABCD001 ~ ABCD020
    if _arraysearch($aFiles,$oExcel.Cells($i+2,1).value,0,0,1,2,1,1) <>-1 Then

        _ExcelWriteCell($oExcel,"X",$i+2,2)
    EndIf
Next

Edited August 27, 2013 by soonyee91

[drbyte]

@soonyee91

Perfect solution!

I'am learning more and more on how to handle this.

Thnx a lot.

[drbyte]

@JCEF

I'am also testing your code at the moment.

Wat does "$oExcel.Sheets("Folha1")" mean is this the name of the sheet name?

Did some experimenting it is indeed the name of the worksheet.

There is a small mistake in your code, corrected code below.

For $1 = 0 To UBound($aFiles) - 1

Is has to be from 0 to Ubound, otherwise i will miss the first record of the array.

Edited August 27, 2013 by drbyte

[JCEF]

@JCEF

I'am also testing your code at the moment.

Wat does "$oExcel.Sheets("Folha1")" mean is this the name of the sheet name?

Did some experimenting it is indeed the name of the worksheet.

There is a small mistake in your code, corrected code below.

For $1 = 0 To UBound($aFiles) - 1

Is has to be from 0 to Ubound, otherwise i will miss the first record of the array.

 

Good night!

In fact this sentence "$ oExcel.Sheets (" Folha1 ")" refers to the name of the first sheet (in portuguese). I found it interesting to identify the sheet because in future situations may be useful to you if you have to pass data to more than one sheet.

I confess I have not looked very closely at your code. I focused on the end and just took in the values ​​of array '$aFiles' and put them in Excel.

For this potential error 'For $1 = 1 To UBound ($ aFiles) - 1' I do not agree with you. If you analizares the values ​​passed by the arrays, certainly you will see that the value of 'Index 0' is just the number of files contained in the folder analyzed at the beginning of the script. I think that number is not necessary to transpose into Excel sheet, correct? May even bring you problems in the future, I think. Even you are not being consistent with what you wrote in your script. If you look closely near the end when you "; populate 2ND end result of dimensional array with stripped October file name" you use my discretion.

 

thus:

Your line:

     'For $ i = UBound ($ atemp_Files) - 1 To 0 Step -1'

I would change to: 

    'For $ i = UBound ($ atemp_Files) - 1 To 1 Step -1'

and ...

Your line:

     'For $ 1 = 0 To UBound ($ atemp_Files) - 1'

I would change to:

     'For $ 1 = 1 To UBound ($ atemp_Files) - 1'

But all this is just my opinion.

Hope this helps.

[water]

In fact

$oExcel.Sheets("Folha1")"

doesn't do anything. No method is being called, no property is being assigned or read.

If it would - as an example - activate the named sheet it wouldn't work if the user decides to rename the sheet. Some kind of error checking is needed.

Side note: My rewrite of the Excel UDF allows to work with multiple worksheets, worksbooks and Excel instances at a time.

[JCEF]

In fact

$oExcel.Sheets("Folha1")"

doesn't do anything. No method is being called, no property is being assigned or read.

If it would - as an example - activate the named sheet it wouldn't work if the user decides to rename the sheet. Some kind of error checking is needed.

Side note: My rewrite of the Excel UDF allows to work with multiple worksheets, worksbooks and Excel instances at a time.

 

Good night!

I'm just a beginner in Excel and Autoit and liked that you confirm if this is wrong, because I want to learn more every day.

Effectively, I could / should have created a variable for the name of the sheet ("Folha1").

The phrase was inserted in this context:

;.......
$oExcel = _ExcelBookOpen(@ScriptDir & "\test\FileExcel.xlsx", 0)

For $1 = 1 To UBound($aFiles) - 1
    For $i = 2 To 21
        If $aFiles[$1][1] = $oExcel.Sheets("Folha1").Range("A" & $i).Value Then
            $oExcel.Sheets("Folha1").Range("B" & $i).Value = StringUpper("x")
        EndIf
    Next
Next

_ExcelBookClose($oExcel)
_ArrayDelete($aFiles, 0)

;.......

Thank you in advance for your attention.



Edited August 30, 2013 by JCEF