Excel to Variable - seeking more efficient way Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
Monoxide3009
Posts: 22
Joined: 09 Apr 2018, 15:53

Excel to Variable - seeking more efficient way

21 May 2018, 17:14

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] [Expand] [Download] GeSHi © Codebox Plus



Is there a better way to do this when I have 10+ variables?
FanaticGuru
Posts: 1137
Joined: 30 Sep 2013, 22:25

Re: Excel to Variable - seeking more efficient way

21 May 2018, 23:42

Code: [Select all] [Expand] [Download] (Untitled.ahk)GeSHi © Codebox Plus


FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers
Monoxide3009
Posts: 22
Joined: 09 Apr 2018, 15:53

Re: Excel to Variable - seeking more efficient way

22 May 2018, 15:30

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
Posts: 1137
Joined: 30 Sep 2013, 22:25

Re: Excel to Variable - seeking more efficient way  Topic is solved

22 May 2018, 16:10

Monoxide3009 wrote: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] [Download] (Untitled.ahk)GeSHi © Codebox Plus

; 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
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers
Monoxide3009
Posts: 22
Joined: 09 Apr 2018, 15:53

Re: Excel to Variable - seeking more efficient way

22 May 2018, 16:38

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
Posts: 1137
Joined: 30 Sep 2013, 22:25

Re: Excel to Variable - seeking more efficient way

23 May 2018, 17:39

Monoxide3009 wrote: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] [Expand] [Download] (Untitled.ahk)GeSHi © Codebox Plus

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
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers
Monoxide3009
Posts: 22
Joined: 09 Apr 2018, 15:53

Re: Excel to Variable - seeking more efficient way

23 May 2018, 20:01

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.

Return to “Ask For Help”

Who is online

Users browsing this forum: No registered users and 53 guests