Coloring the Excel Cell Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Coloring the Excel Cell

14 May 2019, 02:43

I'm getting colors using PixelGetColor, I want to apply to Excel cells. But why did I fail?
I created this code with the help of Pulovers Macro Creator. It works in PMC.

Code: Select all

^q::

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")

Sleep, 100
J = 1
i = 1
Loop, 50
{
	i += 1
	J := 1
	Loop, 50
	{
		J += 1
		;Move, %i%, %J%, 0
		PixelGetColor, color1, %i%, %J%, RGB
		Sleep, 50
		Xl.Cells(%J%,%i%).Interior.color := %color1%
		
	}
}	
return

Escape::
ExitApp
return
Last edited by hasantr on 14 May 2019, 03:49, edited 1 time in total.
Klarion
Posts: 176
Joined: 26 Mar 2019, 10:02

Re: Coloring the Excel Cell

14 May 2019, 03:21

use BGR not RGB
and
use expressions properly

Good Luck
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Coloring the Excel Cell

14 May 2019, 03:33

Klarion wrote:
14 May 2019, 03:21
use BGR not RGB
and
use expressions properly

Good Luck

In this way? That doesn't work.
---------------------------
*
---------------------------
*
---------------------------
Error: 0x80020005 - Type mismatch.
Source: (null)
Description: (null)
HelpFile: (null)
HelpContext: 0

Specifically: Cells

Line#
018: J := 1
019: Loop,50
020: {
021: J += 1
023: PixelGetColor,color1,%i%,%J%,RGB
024: Sleep,50
025: MsgBox,%color1%
---> 026: Xl.ActiveWorkbook.ActiveSheet.Cells(%J%,%i%).Interior.Color := %color1%
028: }
029: }
030: Return
033: ExitApp
034: Return
035: Exit
036: Exit

Continue running the script?
---------------------------
Yes No
---------------------------
Klarion
Posts: 176
Joined: 26 Mar 2019, 10:02

Re: Coloring the Excel Cell

14 May 2019, 04:00

That does work properly
It emits error because YOU coded errors
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Coloring the Excel Cell

14 May 2019, 06:49

I did what I wanted using VBA.

Code: Select all

#If VBA7 Then
    Private Declare PtrSafe Function GetPixel Lib "gdi32" (ByVal hdc As LongPtr, ByVal x As Long, ByVal y As Long) As Long
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (ByRef lpPoint As POINT) As LongPtr
    Private Declare PtrSafe Function GetWindowDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
#Else
    Private Declare Function GetPixel Lib "gdi32" (ByVal hdc As Long, ByVal x As Long, ByVal y As Long) As Long
    Private Declare Function GetCursorPos Lib "user32" (ByRef lpPoint As POINT) As Long
    Private Declare Function GetWindowDC Lib "user32" (ByVal hwnd As Long) As Long
#End If
Private Type POINT
    x As Long
    y As Long
End Type

Sub Picture1_Click()
    Application.ScreenUpdating = False
    Dim pLocation As POINT
    Dim lColour As Long

    Dim lDC As Variant
    lDC = GetWindowDC(0)
    'Call GetCursorPos(pLocation)
    
    
    Do While i < 500
    i = i + 1
    j = 1
        Do While j < 500
            j = j + 1
            lColour = GetPixel(lDC, i, j)
            Cells(j, i).Interior.Color = lColour
        Loop
    Loop
End Sub
User avatar
jeeswg
Posts: 6723
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Coloring the Excel Cell

14 May 2019, 06:57

Code: Select all

;before:
		Xl.Cells(%J%,%i%).Interior.color := %color1%

;after:
		Xl.Cells(J,i).Interior.color := color1
Cheers.
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Coloring the Excel Cell

14 May 2019, 16:34

jeeswg wrote:
14 May 2019, 06:57

Code: Select all

;before:
		Xl.Cells(%J%,%i%).Interior.color := %color1%

;after:
		Xl.Cells(J,i).Interior.color := color1
Cheers.

Actually, I tried that too. There must be trouble on my computer. Because it didn't work that way either.
I'il try again at work in the morning. Even if I solved the problem a lot better with VBA, I'll try again.
User avatar
Datapoint
Posts: 68
Joined: 18 Mar 2018, 17:06

Re: Coloring the Excel Cell  Topic is solved

14 May 2019, 22:00

hasantr wrote:
14 May 2019, 02:43

Code: Select all

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")
Looks like you are creating instances of Excel without ever making them visible. And your script doesn't show quitting Excel or clearing the variables that contain COM objects. Both of these things can create hard-to-find problems for beginners. (especially if this is part of a larger script.) I suggest you use XL.visible := true until you are confident in your ability to quit Excel using only the script. And clear any variables that contain COM objects when you are done with them. XL := ""... do the same for any other COM vars. This allows the Excel process to close after you close the workbooks and quit Excel. Try restarting your computer before you test your script again.
hasantr wrote:Actually, I tried that too. There must be trouble on my computer. Because it didn't work that way either.
It would be helpful if you describe the exact error.

jeeswg's correction to your syntax appears to be correct, but I didn't test it in Excel. The code you had before would not work.

Klarion was correct about needing BGR. In VBA you have the RGB function, but in AHK you just assign a number., ex: ... := 0x1122FF - where in this case 11 is the blue part, 22 is the green part, FF is the red part. (decimal numbers work too, in addition to hex number, as long as it has the same value)
Klarion
Posts: 176
Joined: 26 Mar 2019, 10:02

Re: Coloring the Excel Cell

14 May 2019, 22:58

this kind of people who do not know how to use the basic keyword Thanks
I do not like them
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Coloring the Excel Cell

15 May 2019, 01:05

Klarion wrote:
14 May 2019, 22:58
this kind of people who do not know how to use the basic keyword Thanks
I do not like them
Thank you for your attention. I just didn't want to thank one by one after every message, and I know how to thank you with my recent report.
I really thank you for warning me in good faith.
You're good people who help us without a reward.
I'il help other people when I get the time. Then I'il be happy. :)
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Coloring the Excel Cell

15 May 2019, 01:07

jeeswg wrote:
14 May 2019, 06:57

Code: Select all

;before:
		Xl.Cells(%J%,%i%).Interior.color := %color1%

;after:
		Xl.Cells(J,i).Interior.color := color1
Cheers.
I had to understand this with a lot of trial and error. But now I'm sure it's true. The reason the code didn't work was deeper.
Thank you for your attention.
Last edited by hasantr on 15 May 2019, 01:12, edited 1 time in total.
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Coloring the Excel Cell

15 May 2019, 01:11

Datapoint wrote:
14 May 2019, 22:00
hasantr wrote:
14 May 2019, 02:43

Code: Select all

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")
Looks like you are creating instances of Excel without ever making them visible. And your script doesn't show quitting Excel or clearing the variables that contain COM objects. Both of these things can create hard-to-find problems for beginners. (especially if this is part of a larger script.) I suggest you use XL.visible := true until you are confident in your ability to quit Excel using only the script. And clear any variables that contain COM objects when you are done with them. XL := ""... do the same for any other COM vars. This allows the Excel process to close after you close the workbooks and quit Excel. Try restarting your computer before you test your script again.
hasantr wrote:Actually, I tried that too. There must be trouble on my computer. Because it didn't work that way either.
It would be helpful if you describe the exact error.

jeeswg's correction to your syntax appears to be correct, but I didn't test it in Excel. The code you had before would not work.

Klarion was correct about needing BGR. In VBA you have the RGB function, but in AHK you just assign a number., ex: ... := 0x1122FF - where in this case 11 is the blue part, 22 is the green part, FF is the red part. (decimal numbers work too, in addition to hex number, as long as it has the same value)
Thank you so much for telling me everything so well. I will apply them one by one. I would never want your labor to be wasted.

It had been previously worked after restarting the computer of Com objects. He was making me cancer. It was really hard to understand. Now I know what to do. Thanks.
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Coloring the Excel Cell

15 May 2019, 01:34

Code: Select all

---------------------------
ExceleResim.ahk
---------------------------
Error:  0x80020005 - Type mismatch
Source:		(null)
Description:	(null)
HelpFile:		(null)
HelpContext:	0

Specifically: Color

	Line#
	021: J := 1
	022: Loop,50
	023: {
	024: J += 1
	026: PixelGetColor,color1,%i%,%J%,RGB
	027: Sleep,50
	029: XL.ActiveWorkbook.ActiveSheet.Cells(J,i).value := color1  
--->	030: XL.ActiveWorkbook.ActiveSheet.Cells(J,i).Interior.Color := color1  
	033: }
	034: }
	035: XL := ""
	036: Return
	039: XL := ""
	040: ExitApp
	041: Return

Continue running the script?
---------------------------
Yes No
---------------------------
Still have the type mismatch problem.
Is it from RGB - BGR difference?
 The Pulovers Macro Creator works fine but applying some colors in reverse.

This is the last code:

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

^q::
XL := ""
XL.Quit
If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")
XL.visible := true
XL := ComObjActive("Excel.Application")
;SendInput,^!Delete
Sleep,1000000000000
Sleep, 100
J = 1
i = 1
Loop, 50
{
	i += 1
	J := 1
	Loop, 50
	{
		J += 1
		;Move, %i%, %J%, 0
		PixelGetColor, color1, %i%, %J%, RGB
		Sleep, 50
		;MsgBox %color1%  i  %i%   J    %J%
		XL.ActiveWorkbook.ActiveSheet.Cells(J,i).value := color1
		XL.ActiveWorkbook.ActiveSheet.Cells(J,i).Interior.Color := color1
		
		;Xl.ActiveWorkbook.ActiveSheet.Cells(%J%,%i%).Interior.Color := %color1%
	}
}	
XL := ""
return

Escape::
XL := ""
ExitApp
return
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Coloring the Excel Cell

15 May 2019, 01:37

This works. Everything is black. I'm gonna find out how to solve the color problem. Thanks again.

Code: Select all

XL.ActiveWorkbook.ActiveSheet.Cells(J,i).Interior.Color := 0
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Coloring the Excel Cell

15 May 2019, 03:11

Code: Select all

^q::
XL := ""
XL.Quit
If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")
XL.visible := true
XL := ComObjActive("Excel.Application")
J = 1
i = 1
Loop, 100
{
	i += 1
	J := 1
	Loop, 25
	{
		J += 1
		PixelGetColor, color1, %i%, %J%, RGB
		B := (color1 & 0xFF)
		G := ((color1 & 0xFF00) >> 8)
		R := ((color1 & 0xFF0000) >> 16)
		color2 := RGB2Excel(B, G, R) 
		XL.ActiveWorkbook.ActiveSheet.Cells(J,i).Interior.Color := color2
	}
}	
XL := ""
return

RGB2Excel(R, G, B) {
ExcelFormat :=  (R<<16) + (G<<8) + B
return ExcelFormat
}

Escape::
XL := ""
ExitApp
return
So it worked great. Thank you all. :)
Excel Fill Screen.gif
Excel Fill Screen.gif (532.12 KiB) Viewed 877 times
Klarion
Posts: 176
Joined: 26 Mar 2019, 10:02

Re: Coloring the Excel Cell

15 May 2019, 03:39

you are having a good time
User avatar
hasantr
Posts: 268
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Coloring the Excel Cell

15 May 2019, 04:09

Klarion wrote:
15 May 2019, 03:39
you are having a good time
Exactly. And I learn a lot.
awel20
Posts: 178
Joined: 19 Mar 2018, 14:09

Re: Coloring the Excel Cell

15 May 2019, 09:58

I can see some things that I would change. I added some comments.
hasantr wrote:
15 May 2019, 03:11

Code: Select all

XL := ""
XL.Quit ; The XL variable is blank, so this will never do anything
If !IsObject(XL)
	XL := ComObjCreate("Excel.Application") ; Creates a new instance of excel
XL.visible := true
; The next line gets an already running instance of Excel... possibly a different one than was created with XL := ComObjCreate("Excel.Application")
XL := ComObjActive("Excel.Application")

Code: Select all

If !IsObject(XL := ComObjActive("Excel.Application")) ; If Excel is not running
{
	XL := ComObjCreate("Excel.Application") ; Creates a new instance of excel
	XL.visible := true
}
Maybe something like this. But ComObjActive will only work well if you have just one instance of Excel.
Klarion
Posts: 176
Joined: 26 Mar 2019, 10:02

Re: Coloring the Excel Cell

15 May 2019, 10:46

Just tested

What the heck is color value 0xFFFFFFFF ??

Code: Select all

_ := ComObjActive("Excel.Application")
Loop, 35 {
	c ++
	r := 0
	Loop, 30 {
		r ++
		PixelGetColor, cVal, % c, % r
		If(cVal != 0xFFFFFFFF)
			_.ActiveWorkbook.ActiveSheet.Cells(r, c).Interior.Color := cVal
	}}
캡처.PNG
캡처.PNG (17.82 KiB) Viewed 823 times

Return to “Ask For Help”

Who is online

Users browsing this forum: cadudesun, john_c, Lem2001, LiangShuii and 68 guests