Excel to Variable - seeking more efficient way Topic is solved

[Monoxide3009]

Hello All,

I currently have a working script to pull variables from excel and use them in another program. The problem is, the process I am using is somewhat slow. I was looking for a more efficient way, if one exists. Ideally, I would like to be able to pull a whole row of variables at once. Right now, my script goes cell to cell, copy, move from clipboard to variable, repeat to end of row, then reset to the next row.

I am self taught, so please bare with my jenky code, but an example of what I have is below:

Code: Select all

{ ; Copy HAWB
Send, {right}{right}
Sleep, 100
Send, ^c
Sleep, 100
HAWB:=RegExReplace(clipboard,"\R")
Sleep, 100
clipboard = ;
Sleep, 100
StringLower, HAWB, HAWB
Sleep, 100
}

{ ; Copy MAWB
Send, {right}
Sleep, 100
Send, ^c
Sleep, 100
MAWB:=RegExReplace(clipboard,"\R")
Sleep, 100
clipboard = ;
Sleep, 100
StringLower, MAWB, MAWB
Sleep, 100
}

Is there a better way to do this when I have 10+ variables?

[FanaticGuru]

Code: Select all

xlApp := ComObjActive("Excel.Application")

; one cell to one variable
HAWB := xlApp.Range("A1").Value
MsgBox % HAWB

Data := {}
; Get all cell values in column A and put in an array
for Cell in xlApp.ActiveSheet.UsedRange.Columns("A").Cells
	Data.Push(Cell.Value)

; Display second cells value in array
MsgBox % Data.2

; Display all the values in the array
for Index, Value in Data
	Display .= Index "`t" Value "`n"
MsgBox % Display

FG

[Monoxide3009]

Thank you for the response. I will try to poke through this and try to figure it out. But I will be honest, this all looks amazingly foreign to me =D

[FanaticGuru]

Thank you for the response. I will try to poke through this and try to figure it out. But I will be honest, this all looks amazingly foreign to me =D

Yes, this is accessing Excel in a totally different way than through the clipboard.

COM allows AutoHotkey to communicate directly with Excel as one program talking to another.

You can keep it very basic like below to start.

Code: Select all

; Create a connection between AHK and an open active Excel file
xlApp := ComObjActive("Excel.Application")

; one cell to one variable 
HAWB := xlApp.Range("A1").Value ; store the value of the A1 range of the connected Excel, just change the A1 to any cell you want the value of
MsgBox % HAWB ; display results just for demonstration purposes

FG

[Monoxide3009]

Ooof. That helped break it down a lot for me. I think I understand enough to make use of this. Though with the sensitive data I am using, I will need a lot of testing.

It is not uncommon for my company to have multiple excel files open. With that in mind, my current script obtains the specific excel title needed and stores it as a variable using WinActivate, %EXCEL% to pull the appropriate one. Can I add the title into this somewhere?

Example (I use this to make it more user friendly for others that use the script):

MsgBox, 262144,, Select (click on) the Excel window, then hit ok
WinGetTitle, EXCEL, A
Sleep, 250

WinActivate, %EXCEL%
Sleep, 250



Also - Is there a way to obtain the row that is currently selected as a variable? This is less necessary because I can just make that a manual input on the first iteration, and have the variable++ for consecutive, but the less manual input the better (and more awe inspiring to the brass)

[FanaticGuru]

It is not uncommon for my company to have multiple excel files open. With that in mind, my current script obtains the specific excel title needed and stores it as a variable using WinActivate, %EXCEL% to pull the appropriate one. Can I add the title into this somewhere?

Also - Is there a way to obtain the row that is currently selected as a variable? This is less necessary because I can just make that a manual input on the first iteration, and have the variable++ for consecutive, but the less manual input the better (and more awe inspiring to the brass)

Once you embrace COM, alot can be done with Excel very quickly and reliably.

Here is an example of a function that will get a handle to a workbook.

Code: Select all

xlWb1 := xlGet_Workbook("One.xlsx", A_Desktop "\Test\")
xlWb2 := xlGet_Workbook("Two.xlsx")

MsgBox % xlWb1.ActiveSheet.Range("A1").Value
MsgBox % xlWb2.Worksheets(2).Range("A1").Value
MsgBox % xlWb2.Worksheets("Test").Range("A2").Value
MsgBox % xlWb2.Worksheets("Test").Range("A2").Row
MsgBox % xlApp.ActiveCell.Address
MsgBox % xlApp.ActiveCell.Row
MsgBox % xlApp.ActiveWorkbook.Name

xlGet_Workbook(WB, Path:="")
{
	global xlApp ; once function is called xlApp will be available to use outside the function
	If !Path	; default path
		Path := A_Desktop "\Test\"
	if !xlApp ; If no xlApp then attempt to connect to running app or open app
	{
		try
			xlApp := ComObjActive("Excel.Application")
		catch
			xlApp := ComObjCreate("Excel.Application")
		xlApp.Visible := true
	}
	try ; try to connect to open workbook
		xlWb := xlApp.Workbooks(WB)
	catch
		try ; try to open workbook
			xlWb := xlApp.Workbooks.Open(Path WB)
		catch ; catch failure to open
		{
			MsgBox % "Wookbook: " WB "`nCannot be found at: " Path WB
			return
		}
	return xlWB
}

This basically connects to a workbook, opening the workbook if needed. It has some error checking but if you attempt to access stuff that don't exist you will probably get an error. You would need to change all the names and paths to actually test this script to files that exist on your system.

It also shows some basic other things like how to reference specific sheets and how to find out information about the active cell or workbook.

And if you really want to be fancy all of this could be done in the background without ever displaying or appearing to open Excel. You can access everything about an Excel workbook completely invisibly.

FG

[Monoxide3009]

Thanks again. You have really helped me a lot.

I was able to get everything working much faster. I still dont understand some of the newest examples, but I have plenty of time the rest of the week to play around with it.