Page 1 of 2
Coloring the Excel Cell
Posted: 14 May 2019, 02:43
by hasantr
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
Re: Coloring the Excel Cell
Posted: 14 May 2019, 03:21
by Klarion
use BGR not RGB
and
use expressions properly
Good Luck
Re: Coloring the Excel Cell
Posted: 14 May 2019, 03:33
by hasantr
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
---------------------------
Re: Coloring the Excel Cell
Posted: 14 May 2019, 04:00
by Klarion
That does work properly
It emits error because YOU coded errors
Re: Coloring the Excel Cell
Posted: 14 May 2019, 06:49
by hasantr
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
Re: Coloring the Excel Cell
Posted: 14 May 2019, 06:57
by jeeswg
Code: Select all
;before:
Xl.Cells(%J%,%i%).Interior.color := %color1%
;after:
Xl.Cells(J,i).Interior.color := color1
Cheers.
Re: Coloring the Excel Cell
Posted: 14 May 2019, 16:34
by hasantr
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.
Re: Coloring the Excel Cell Topic is solved
Posted: 14 May 2019, 22:00
by Datapoint
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)
Re: Coloring the Excel Cell
Posted: 14 May 2019, 22:58
by Klarion
this kind of people who do not know how to use the basic keyword Thanks
I do not like them
Re: Coloring the Excel Cell
Posted: 15 May 2019, 01:05
by hasantr
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.
Re: Coloring the Excel Cell
Posted: 15 May 2019, 01:07
by hasantr
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.
Re: Coloring the Excel Cell
Posted: 15 May 2019, 01:11
by hasantr
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.
Re: Coloring the Excel Cell
Posted: 15 May 2019, 01:27
by Klarion
Good
Re: Coloring the Excel Cell
Posted: 15 May 2019, 01:34
by hasantr
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
Re: Coloring the Excel Cell
Posted: 15 May 2019, 01:37
by hasantr
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
Re: Coloring the Excel Cell
Posted: 15 May 2019, 03:11
by hasantr
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 (532.12 KiB) Viewed 4730 times
Re: Coloring the Excel Cell
Posted: 15 May 2019, 03:39
by Klarion
you are having a good time
Re: Coloring the Excel Cell
Posted: 15 May 2019, 04:09
by hasantr
Klarion wrote: ↑15 May 2019, 03:39
you are having a good time
Exactly. And I learn a lot.
Re: Coloring the Excel Cell
Posted: 15 May 2019, 09:58
by awel20
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.
Re: Coloring the Excel Cell
Posted: 15 May 2019, 10:46
by Klarion
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 (17.82 KiB) Viewed 4676 times