Extract data from one Excel and save in another

[SaeidN]

Hi,

Let's say we have an excel file like below (each space means next cell):

number county capital states color population

no1 USA Washington 50 red 328

no2 Canada Ottawa 7 orange 38

no3 Germany Berlin 15 white 40

no4 Norway Oslo 5 black 10

I already have a 2D array which includes country and color in its 2 columns, like this: $dataArray = [["Germany","pink"],["USA", "gray"],["Norway","yellow"]]

I want the code to update the colors in each cell for each country.

I honestly don't know where to start. It's so confusing to match the countries and update the related cell.

I appreciate your help.

Edited September 4, 2022 by SaeidN

[Subz]

Basic example:

#include <Array.au3>
#include <Excel.au3>
Local $iBlack = 1
Local $iWhite = 2
Local $iRed = 3
Local $iOrange = 45
Local $aTeamColors = [["USA",$iRed],["Canada",$iOrange],["Germany",$iWhite],["Norway",$iBlack]]
Local $oExcel = _Excel_Open()
If @error Then Exit
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Teams.xlsx")
If @error Then Exit
For $i = 0 To UBound($aTeamColors) - 1
    $aFindAll = _Excel_RangeFind($oWorkbook, $aTeamColors[$i][0])
    If @error Then ContinueLoop
    For $j = 0 To UBound($aFindAll) - 1
        $iRow = Number(StringReplace($aFindAll[$j][2], "$B$", ""))
        $oWorkbook.ActiveSheet.Range("E" & $iRow).Select
            With $oExcel.Selection.Interior
            .Pattern = 1
            .PatternColorIndex = -4105
            .ColorIndex = $aTeamColors[$i][1]
            .TintAndShade = 0
            .PatternTintAndShade = 0
        EndWith
    Next
Next

 

[SaeidN]

What does this do? "$B$"

and also everything after "Selection.Interior"

This code change the colors of each cell, how would I update the values?

[Subz]

$B$ is the "Address of the cell" see _Excel_RangeFind for results.  You can see the results by adding _ArrayDisplay($aFindAll) below _Excel_RangeFind(...) function.

For "Selection.Interior" you can compose this within Excel by using the following:

• Open Excel » Developer tab
• Click: Record Macro
• Name: Macro1
• Perform tasks you want to perform, change font color etc..
• Click: Stop Recording
• Click: Macros
• Select: Macro1
• Click: Edit

You then need to copy the info into AutoIt and adjust accordingly.

For example if you change font color to red, you would see a result like:

With Selection.Font
    .Color = -16776961
    .TintAndShade = 0
End With

However I prefer using ColorIndex for simple colors (see What are the 56 ColorIndex colors in Excel - ExcelSuperSite)

You then change the code in AutoIt to something like:

With $oExcel.Selection.Font
    .ColorIndex = $aTeamColors[$i][1]
    .TintAndShade = 0
EndWith