Can I get excel file to qlikview with exact colours formats ???

[jozisvk11]

I have table, which is corrected according to situation by different users. Users add different colours to table to improve lucidity (there is no logic !!!). Can I get this excel table to qlikview with colours formats.

Table is for example:

Workroom	Customer ID	Item number	Text item
D10	MENZ00	T10170195V230419	Schubstange S.M-Serie 258722
D10	MENZ00	T10170195V230417	Schere L.M-Serie 258721
D10	MENZ00	T10170195V230420	Schere Stiel M-Serie 258723
D10	RONA01	T10170193V238831	H7800, Foot R_452.1064-001
D09	RONA01	T10170603V278844	Bearing cover 695-15-706ED
D10	RONA01	T10170505V216365	Armtraeger/1006335
D10	RONA01	T10170646V245589	Base 4705993
D10	RONA01	T10170627V245591	Pelare 4706114
D08	KION01	T10171214V283570	Gabelhaken 3318698
D10	ANDR03	T10170637V279330	Bearing cover 300854256
D10	ANDR03	T10170637V279332	Bearing cover 300854330
D10	ANDR03	T10170637V279333	Bearing cover 300854332
D10	ANDR03	T10170637V279331	Bearing cover 300854257

Thank you.

[Anil_Babu_Samineni]

Yes, This is possible. But, We required to use multiple Conditions to achievve this

[jozisvk11]

How can I do that ?

[Anil_Babu_Samineni]

I am sorry, I can't help y due to traveling till Tuesday.. May be someone offer you

and, my intention is this

If(match(field, '','') and match(field 1, '',''), colorcode)

[vinieme12]

The Answer is NO

You simply CANNOT get the formatting into Qlikview.

You will need create a custom function in excel (refer link below)

https://www.thespreadsheetguru.com/the-code-vault/2014/11/5/retrieve-excel-cells-font-fill-rgb-color...

Now create a new column with the function which will then have the RGB values

[vinieme12 · Accepted solution]

You can't get colorcode while loading!

getting that colorcode is the objective here!!

[effinty2112]

Hi Josef,

I have an answer but it's a bit clunky because it requires some work on your excel sheet.

This VBA function can be used to add a new column to your sheet. If saved to your personal workbook you can call it as  the user defined function  PERSONAL.XLSB!RGBcomp(C2)

Function RGBcomp(ByVal Target As Range) As String

Dim HEXcolor As String

HEXcolor = Right("000000" & Hex(Target.Font.Color), 6)

RGBcomp = CInt("&H" & Right(HEXcolor, 2)) & _

", " & CInt("&H" & Mid(HEXcolor, 3, 2)) & _

", " & CInt("&H" & Left(HEXcolor, 2))

End Function

This is a paste of the excel spreadsheet with the PGBcomp function acting on the Item number cell. The colours of the cells have been lost on the copy and paste. 

Workroom	Customer ID	Item number	Text item	RGBcomp
D10	MENZ00	T10170195V230419	Schubstange S.M-Serie 258722	51, 51, 153
D10	MENZ00	T10170195V230417	Schere L.M-Serie 258721	0, 0, 255
D10	MENZ00	T10170195V230420	Schere Stiel M-Serie 258723	0, 0, 0
D10	RONA01	T10170193V238831	H7800, Foot R_452.1064-001	0, 0, 0
D09	RONA01	T10170603V278844	Bearing cover 695-15-706ED	0, 0, 0
D10	RONA01	T10170505V216365	Armtraeger/1006335	128, 0, 128
D10	RONA01	T10170646V245589	Base 4705993	128, 0, 128
D10	RONA01	T10170627V245591	Pelare 4706114	128, 0, 128
D08	KION01	T10171214V283570	Gabelhaken 3318698	0, 0, 0
D10	ANDR03	T10170637V279330	Bearing cover 300854256	0, 255, 255
D10	ANDR03	T10170637V279332	Bearing cover 300854330	0, 255, 255
D10	ANDR03	T10170637V279333	Bearing cover 300854332	0, 255, 255
D10	ANDR03	T10170637V279331	Bearing cover 300854257	0, 255, 255

Save the workbook , you may need to save it as an xlsm file.

Load it into QV:

Customer ID	Item number	Text item	Workroom
ANDR03	T10170637V279330	Bearing cover 300854256	D10
ANDR03	T10170637V279331	Bearing cover 300854257	D10
ANDR03	T10170637V279332	Bearing cover 300854330	D10
ANDR03	T10170637V279333	Bearing cover 300854332	D10
KION01	T10171214V283570	Gabelhaken 3318698	D08
MENZ00	T10170195V230417	Schere L.M-Serie 258721	D10
MENZ00	T10170195V230419	Schubstange S.M-Serie 258722	D10
MENZ00	T10170195V230420	Schere Stiel M-Serie 258723	D10
RONA01	T10170193V238831	H7800, Foot R_452.1064-001	D10
RONA01	T10170505V216365	Armtraeger/1006335	D10
RONA01	T10170603V278844	Bearing cover 695-15-706ED	D09
RONA01	T10170627V245591	Pelare 4706114	D10
RONA01	T10170646V245589	Base 4705993	D10

Text item text color is defined by the formula:

=RGB(subfield(RGBcomp,',',1),subfield(RGBcomp,',',2),subfield(RGBcomp,',',3))

Good luck

Andrew

[vinieme12]

Attaching a sample workbook for reference

Paste this in the Module

Function TextColor(rng As Range, Optional formatType As Integer = 0) As Variant

Application.Volatile True

    Dim colorVal As Variant

    colorVal = Cells(rng.Row, rng.Column).Font.Color

            Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)

End Function

Function FillColor(rng As Range, Optional formatType As Integer = 0) As Variant

Application.Volatile True

    Dim colorVal As Variant

    colorVal = Cells(rng.Row, rng.Column).Interior.Color

            Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)

End Function