54 replies to this topic

[jethrow]

I would stay away from the AHK_L/COM_L version of the Office functions as I've been finding some "bugs" as I've been working with them this week.

Ok, but if you do use AHKL/COM_L, w/o the Office functions, it's almost exactly the same as VBScript - which is very well documented online ( or you could even use the Macro recorder in Excel ).

pxl := COM_GetActiveObject( [color=grey]"Excel.Application"[/color] )

cell := pxl.ActiveCell

[color=darkred]MsgBox[/color], % [color=grey]"Value of "[/color] cell.Address [color=grey]" is "[/color] cell.value

[sinkfaze]

I would suggest you create this task using VBA in Excel. This will allow you to use the native Excel application Object model.

You're describing exactly what AHK is doing, and AHK has an additional advantage over VBA proper in that the macro is more portable.

[jethrow]

MyArray() = Range("A1:A25")

Yes, as sinkfaze eluded to, this is pretty much the same with AHKL:

MyArray := oSheet.Range([color=grey]"A1:A25"[/color])

However, AHKL doesn't quite yet have an "easy" way to enumerate this array, though AutoHotkey_COM does:

oSheet := ComObjGet([color=grey]"Book1"[/color]).ActiveSheet

MyArray := oSheet.range([color=grey]"A1:A25"[/color])



enum := MyArray._NewEnum

[color=#C60000]while[/color] enum[ cell ]

	t .= cell.value [color=grey]","[/color]

[color=#C60000]MsgBox[/color], %t%

[lagomorph]

I would suggest you create this task using VBA in Excel. This will allow you to use the native Excel application Object model. It will allow you to add the data to an array in one single line:

MyArray() = Range("A1:A25")

Excel will allow you to send keys to the other application.

If it's possible to send info from various cells in Excel to a 2nd application's input fields, that would be great! Unfortunately I know absolutely nothing about VBA and it took me a week to get where I am now with AHK

Also, I was looking forward to sharing my script with other accounts in the company to help them automate their repetitive tasks - would that be possible using the built-in Excel VBA?

[edit] Actually, before finding AHK, I was trying to create a WSH version of my script and it just WOULD NOT let me switch between excel and my other application because of some security measure that prevents applications from 'stealing focus from the user' - so I'm not sure how to transport the data from my spreadsheet to my 2nd application

[lagomorph]

Hmm...try this code and see what you get, make sure you have an active cell in the spreadsheet (with the black border):

COM_CoInitialize()
xl := COM_GetActiveObject("Excel.Application")
r := COM_Invoke(xl,"ActiveCell.Row"),c := COM_Invoke(xl,"ActiveCell.Column")
MsgBox % r "`n" c
cl := COM_Invoke(xl,"Cells",r,c)
MsgBox % "Cell Data: " COM_Invoke(cl,"Value")
COM_Release(cl),COM_Release(xl),COM_CoUninitialize()
return

This also worked like a charm - it told me the cell column and row and then the cell value! This is getting good... Now, what syntax do I use to Send the data contained in various cells of my spreadsheet to another program's input field?


...and again, thank you so much for helping me!

[lagomorph]

I see how to access a cell and send data from a cell...but what about when I'm dealing with hundreds of cells in consecutive order?

I tried this code and it didn't work

COM_Init() ; Initialize COM
Loop 10
{
MsgBox, % Excel_GetCell("A",%a_index%) ; <- change this
}
return

[sinkfaze]

First let's try the original function again (slightly modified) to make sure it works:

COM_CoInitialize()
MsgBox % Excel_GetCell() ; make sure there is an active cell
MsgBox % Excel_GetCell(3,7) ; make sure there is data in cell C7
COM_CoUninitialize()
return

Excel_GetCell(col="",row="") {
  xl := COM_GetActiveObject("Excel.Application") 
  r := !row ? COM_Invoke(xl,"ActiveCell.Row") : row
  c := !col ? COM_Invoke(xl,"ActiveCell.Column") : col
  cl := COM_Invoke(xl,"Cells",r,c) 
  res := COM_Invoke(cl,"Value") 
  COM_Release(cl),COM_Release(xl) 
  return res 
}

[lagomorph]

First let's try the original function again (slightly modified) to make sure it works:

COM_CoInitialize()
MsgBox % Excel_GetCell() ; make sure there is an active cell
MsgBox % Excel_GetCell(3,7) ; make sure there is data in cell C7
COM_CoUninitialize()
return

Excel_GetCell(col="",row="") {
  xl := COM_GetActiveObject("Excel.Application") 
  r := !row ? COM_Invoke(xl,"ActiveCell.Row") : row
  c := !col ? COM_Invoke(xl,"ActiveCell.Column") : col
  cl := COM_Invoke(xl,"Cells",r,c) 
  res := COM_Invoke(cl,"Value") 
  COM_Release(cl),COM_Release(xl) 
  return res 
}

works perfectly.

[sinkfaze]

Just to make sure let's try this first:

COM_CoInitialize() 
MsgBox % Excel_GetCell() ; make sure there is an active cell 
MsgBox % Excel_GetCell("C",7) ; make sure there is data in cell C7 
COM_CoUninitialize() 
return

Excel_GetCell(col="",row="") {

	xl :=	COM_GetActiveObject("Excel.Application")
	row :=	!row ? COM_Invoke(xl,"ActiveCell.Row") : row
	col :=	!col ? COM_Invoke(xl,"ActiveCell.Column") : Excel_GetColumnIndex(col)
	cl :=	COM_Invoke(xl,"Cells",row,col)
	res :=	COM_Invoke(cl,"Value") 
	COM_Release(cl), COM_Release(xl) 
   	return	res 
}

Excel_GetColumnIndex(Column) {

	if RegExMatch(Column,"^\d+$")
		return Column
	StringUpper, Column, Column 
	Loop, Parse, Column 
		t%A_Index% :=	Asc(A_LoopField) - 64 
	Return	t2 != 0 ? (26 * t1) + t2 : t1 

}

If that works then we'll try extracting from multiple cells in a column:

COM_CoInitialize()
Loop, 10 { ; make sure there is data in the range A1:A10
	MsgBox % Excel_GetCell("A",A_Index)
	res .= ((A_Index=1) ? "" : "`n") Excel_GetCell("A",A_Index)
}
MsgBox % res
VarSetCapacity(res,0) 
COM_CoUninitialize() 
return

Excel_GetCell(col="",row="") {

	xl :=	COM_GetActiveObject("Excel.Application")
	row :=	!row ? COM_Invoke(xl,"ActiveCell.Row") : row
	col :=	!col ? COM_Invoke(xl,"ActiveCell.Column") : Excel_GetColumnIndex(col)
	cl :=	COM_Invoke(xl,"Cells",row,col)
	res :=	COM_Invoke(cl,"Value") 
	COM_Release(cl), COM_Release(xl) 
   	return	res 
}

Excel_GetColumnIndex(Column) {

	if RegExMatch(Column,"^\d+$")
		return Column
	StringUpper, Column, Column 
	Loop, Parse, Column 
		t%A_Index% :=	Asc(A_LoopField) - 64 
	Return	t2 != 0 ? (26 * t1) + t2 : t1 

}

[lagomorph]

Just to make sure let's try this first:

COM_CoInitialize() 
MsgBox % Excel_GetCell() ; make sure there is an active cell 
MsgBox % Excel_GetCell("C",7) ; make sure there is data in cell C7 
COM_CoUninitialize() 
return

Excel_GetCell(col="",row="") {

	xl :=	COM_GetActiveObject("Excel.Application")
	row :=	!row ? COM_Invoke(xl,"ActiveCell.Row") : row
	col :=	!col ? COM_Invoke(xl,"ActiveCell.Column") : Excel_GetColumnIndex(col)
	cl :=	COM_Invoke(xl,"Cells",row,col)
	res :=	COM_Invoke(cl,"Value") 
	COM_Release(cl), COM_Release(xl) 
   	return	res 
}

Excel_GetColumnIndex(Column) {

	if RegExMatch(Column,"^\d+$")
		return Column
	StringUpper, Column, Column 
	Loop, Parse, Column 
		t%A_Index% :=	Asc(A_LoopField) - 64 
	Return	t2 != 0 ? (26 * t1) + t2 : t1 

}

It gave me the value of my currently selected cell in one message box, and then the following error:

---------------------------
COM Error Notification
---------------------------
Function Name: "Value"
ERROR: The COM Object may not be a valid Dispatch Object!
First ensure that COM Library has been initialized through COM_Init().
()

Will Continue?
---------------------------
Yes No
---------------------------

[sinkfaze]

Argh...try this code and check the two message boxes before the final one and tell me if any of them have blank data

COM_CoInitialize()
MsgBox % Excel_GetCell("C",7) ; make sure there is data in cell C7
COM_CoUninitialize()
return

Excel_GetCell(col="",row="") {

	xl :=	COM_GetActiveObject("Excel.Application")
	row :=	!row ? COM_Invoke(xl,"ActiveCell.Row") : row
	col :=	!col ? COM_Invoke(xl,"ActiveCell.Column") : Excel_GetColumnIndex(col)
[color=red]	MsgBox % "Row: " row "`nColumn: " col[/color]
	cl :=	COM_Invoke(xl,"Cells",row,col)
[color=red]	MsgBox % "Cell pointer: " cl[/color]
	res :=	COM_Invoke(cl,"Value")
	COM_Release(cl), COM_Release(xl)
	return	res
}

Excel_GetColumnIndex(col) { 

	if col is digit 
		return	col 
	StringUpper, col, col 
	Loop, Parse, col 
		t%A_Index% :=   Asc(A_LoopField) - 64 
	Return   t2 ? (26 * t1) + t2 : t1 

}

[lagomorph]

I ran it and each msgbox had data - row & column, "pointer", and cell value.

[sinkfaze]

So no errors?

[lagomorph]

So no errors?

no errors at all