GTP> in Excell.

[Jmac2501]

Hey I have a simple script that i am using with excel and I can't get the GTP> to work. I use the arrow keys to change fields to where i want to go and all that works great but I need to know where my text type icon is that way i can use GPC> to see if its highlighted or not.

Basically what my scrip does is go from field to field updating dates and such. But I want it to skip the ones that are highlighted. But in order to do that i need to know how to get MS to determent where in my excel spreadsheet is the field i am working on and determent the x,y coordinates so that i can use the x,y to GPC>. I don't know if GTP> is the best thing to use here so if there is a better way let me know.

Hope that makes sense.

Thanks

[Jmac2501]

Any Ideas???

[JRL]

Not sure I understand the question.

You're not in excel randomly bouncing from cell to cell. Your script should always know which cell its looking at because its the cell the script went to. If you want to be moving from cell to cell like a user try using CTRL+G then send a column and row position. A better method would be to use DDEPoke and DDERequest, both well documented in the forum.

GetCaretPosition> a.k.a. GTP> will only tell you the position of the cursor within a given cell.

Also, When I arrow around in excel all highlighting disappears. Perhaps you have cells with a background color and you're trying to determine if there is a background color or not? Maybe FindImagePos> could be used.

You asked for ideas... A few shots in the dark.

[Jmac2501]

Thanks for the reply-

I am using DDERequest and I know what cell i am working in but, like you said i need to find out if that cell that i am working has the background filled. So i was going to use Get pixel color GPC> to determine that, BUT I don't know how to get my script to figure out what the x,y is of the cell that i am working in so that i can use GPC>. So thats where i was going to use the GTP> because the cell that i am working in has the text caret, but no matter what field i am in it always gives me the same x,y. And its wrong.

I figured out a LONG way to find it but that would be way to much work for what seams like a simple task.

My long way would be to find out what what the x,y's are for all cells and do if> statments for all of them like this:

Code: Select all

if>%cell%=A1
     let>x=100
	 let>y=85
	 GPC>x,y,pc
	 if>%pc%=1024551,end
else
endif
if>%cell%=A2
     let>x=120
	 let>y=85
	 GPC>x,y,pc
	 if>%pc%=1024551,end
else
endif
...
...

But like I said way to much work...

[JRL]

Now I see what you mean.

I don't know any way to get the screen position of an Excel cell. However, a little research on the Microsoft site turned up this bit of VBA code for sorting cells by color. I'm guessing you don't want to sort but the tools seem to be there for getting the background color information from a specified cell. I'm not a VBA programmer but perhaps you can come up with something or maybe some other kind soul will write and post a sample script that provides the excel color number for the background color of a specified cell. It appears the color 2 is the default color so you will probably be looking for "not 2".

Hope this is helpful,
Dick

[Marcus Tettmar]

Just had a quick look at the Excel reference and I see that cells have a Left and Top property which provide the x,y pos inside the sheet of the cell. Then there's the PointsToScreenPixelsX function. So it's probably all doable with VBScript, but you'd need VBscript to create the Excel instance to have a reference to it. You could have VBScript open Excel, continue to use your DDE and then use VBScript to get the position of the active cell. You can get the background of the active cell with interior.color. So, try this ...

Code: Select all

VBSTART

Dim xlApp

Sub OpenExcel
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Workbooks.add
  xlApp.visible = true
End Sub

Sub CloseExcel
  xlApp.close
End SUb

Function GetCellX
  GetCellX = xlApp.ActiveWindow.PointsToScreenPixelsX(xlApp.ActiveCell.Left)
End Function

Function GetCellY
  GetCellY = xlApp.ActiveWindow.PointsToScreenPixelsY(xlApp.ActiveCell.Top)
End Function

Function GetCellBGColor
  GetCellBGColor = xlApp.ActiveCell.Interior.Color
End Function

VBEND

VBRun>OpenExcel

MessageModal>Now Click on a Cell in Excel.  Then press OK here.

VBEval>GetCellX,Xpos
VBEval>GetCellY,Ypos

VBEval>GetCellBGColor,color

MessageModal>Screen pos of cell: %Xpos%,%Ypos%
MessageModal>BG Color: %color%

[Jmac2501]

I will try this and see how it goes. Im not to good with VB but ill give it a shot. Thanks will post back.

[Jmac2501]

ok so i tested it and it works and does what i want. But how can i change it so that instead of opening a new excel window it just sets focus to the excel window already open?

but thanks for the VB script it was a big help.

[JRL]

But how can i change it so that instead of opening a new excel window it just sets focus to the excel window already open?

I'm not a VB whiz either but in this case I think that your answer involves the Macro Scheduler VBRun> function. Do you get what you need if you replace the VBRun>OpenExcel line with SetFocus>Excel*

[Jmac2501]

No, I get an error. But I was thinking that the part that needs to be changed is this:

Code: Select all

Sub OpenExcel
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Workbooks.add
  xlApp.visible = true
End Sub

Sub CloseExcel
  xlApp.close
End SUb

but i don't know Vb.

[Jmac2501]

ok i have got it working.

here is the script:
What i did was took out the xlapp.workbook.add and added SetFocus>Excel*

Code: Select all

VBSTART

Dim xlApp

Sub OpenExcel
Set XLApp = GetObject(, "Excel.Application")
    xlApp.visible = true
End Sub

Sub CloseExcel
  xlApp.close
End SUb

Function GetCellX
  GetCellX = xlApp.ActiveWindow.PointsToScreenPixelsX(xlApp.ActiveCell.Left)
End Function

Function GetCellY
  GetCellY = xlApp.ActiveWindow.PointsToScreenPixelsY(xlApp.ActiveCell.Top)
End Function

Function GetCellBGColor
  GetCellBGColor = xlApp.ActiveCell.Interior.Color
End Function

VBEND

VBRun>OpenExcel

setfocus>Excel*

VBEval>GetCellX,Xpos
VBEval>GetCellY,Ypos

VBEval>GetCellBGColor,color

MessageModal>Screen pos of cell: %Xpos%,%Ypos%
MessageModal>BG Color: %color%