Filter LOAD from Excel based on Color on field

[#1]

Hiya,

I have an excel file - autogenerated (So i can not make changes in Excel file).

I only want to select Amount from an excel file and only the fields that are yellow in the excel file.

Is this possible?

Regards,

Erik

[tresesco]

Try like(to give just an idea):

Create a macro function to get the color of excel cell; call the function in the script before load statement; check the output and use load statement conditionally.

Function proto-type:

Function HexCode(Cell As Range) As String
    HexCode = Right("000000" & Hex(Cell.Interior.Color), 6)
End Function

[#3]

ooh, that is complex. I am new to qlikview But i acutally found out that there is a collum showing these value so i do not need the color.

Could you please help me

LOAD 

  F1,

     Amount as Claim,

     Date(Date,'YYYY-MM-DD') as Date,

     '201406' as ClaimSheet 

FROM

[xxxxxxx]

(ooxml, embedded labels, header is 5 lines, table is [Inv - 4113604248])

WHERE

;

What shall i put in the Where statement to get only the amount where F1 has a value (string)?

Thanks

[tresesco · Accepted solution]

WHERE Len(trim(F1))>0;

[dena_reavis]

Hi,

I am trying to apply the answer to the original post, which is the exact problem I have, and I am lost after I get to the Edit script to make the function. I need make this function so I can load only rows (from a bunch of Excel spreadsheets that ARE consistently formatted) where the cell background color blue. I only need to load the rows that have  blue background colors in column D. I know the blue cell background in Excel is 99CCFF. So here is what I have in the Script Editor:

Function HexCode(Cell As Range) As String

    HexCode = Right("99CCFF" & Hex(Cell.Interior.Color), 6)

End Function

I get a message that "Macro Parse Failed, Fuctionality Lost", but I am not surprised because I have no idea what I am doing.

I was hoping to use this function in a where statement in the load script to specifiy something like: where hexcode(column D,99CCFF). And I do at least understand that the function must be created in the QVW file, before using it in the Load script.

Thank you in advance to anyone who can help me.