Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Getting data from Excel


  • Please log in to reply
54 replies to this topic
lagomorph
  • Members
  • 403 posts
  • Last active: May 15 2014 03:41 PM
  • Joined: 02 Apr 2010
Hello, I am very new to AHK and have been using it to switch between an Excel spreadsheet and another program, slowly Sending ^c and ^v to copy and paste values from the spreadsheet into the other program...

I'm desperate to find a better way to gather data from my Excel spreadsheet without using the clipboard and switching back and forth.

I recently implemented an array to store all the values on the spreadsheet at once, but it still has to slowly go to each individual cell, copy it, and then store it in the array...

I would highly value any advice you can offer me.

Thank you.

Below is the code I am currently using to copy values from Excel and enter it into my other program:

;
; AutoHotkey Version: 1.x
; Language:       English
; Platform:       Win9x/NT
;
; Script Function:
;	Perform Weekly Transfers from excel spreadsheet in UFS
;

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.


Clipboard = 
WinActivate, Microsoft Excel
AccountCount = 0
CurrentUnit = 1
CurrentAccount = 1

; -------
; Store Number of Accounts and Account numbers

Loop
{
	Clipboard = 
	Sleep 100
	Send ^c
	Clipwait
	clipboard = %clipboard%
	AccountCount += 1
	Array%AccountCount%a := clipboard	;Store Account number
	Sleep 50
	Send {right}
	Sleep 50
	if Array%AccountCount%a > 1
	continue
	break
}

; -------
; Store Number of Units in UnitCount

Loop, 2
{
	Send {down}
	Sleep 100
}
Send {home}
Sleep 100
Clipboard = 
Sleep 100
Send ^c
Clipwait
clipboard = %clipboard%
UnitCount := clipboard
Sleep 50
Send {down}
sleep 50

; -------
;Store Unit numbers and Account values (main loop)

Loop 23
;program is not looping based on %unitcount%
{
	Clipboard = 
	Sleep 100
	Send ^c
	Clipwait
	clipboard = %clipboard%
	Array%CurrentUnit%b := clipboard	;store unit number
	send {right}
	While (CurrentAccount < AccountCount)
	{
		Clipboard = 
		Sleep 100
		Send ^c
		Clipwait
		clipboard = %clipboard%
		Array%CurrentUnit%_%CurrentAccount% := clipboard
		Send {right}
		Sleep 100
		CurrentAccount += 1
		
	}
	Send {down}
	Sleep 100
	Send {home}
	CurrentUnit += 1
	CurrentAccount := 1
}

; -------
; Perform Transfers
CurrentUnit = 1
AccountCount -=1
WinActivate, UFS
Sleep 400
Loop
{
	Send {tab}
	Sleep 100
	Send {tab}
	Sleep 100
	Send Admin Charges
	Sleep 100
	Send {tab}
	Mainloop = %a_index%
	Send % Array%mainloop%b
	Send 27747001
	Sleep 100
	Send {tab}
	Sleep 200
	Loop
	{
		Send {tab}
		Send % Array%A_Index%a
		Send {tab 2}
		Sleep 100
		Send % Array%mainloop%_%a_index%
		Sleep 100
		if (A_Index = AccountCount)
		break
	}
	Send {enter}!s
	Sleep 1700
	Send !c
	Sleep 500
	Send !a
	Sleep 700
	CurrentUnit += 1
	If (CurrentUnit = UnitCount)	
;It's going 1 too far, maybe make above line < instead of =
	break
}


sinkfaze
  • Moderators
  • 6365 posts
  • Last active:
  • Joined: 18 Mar 2008
It's a steeper learning curve, but the most reliable way to copy data from a cell(s) on an Excel spreadsheet is by using the COM Standard Library, more particularly the MS Office Functions.

camerb
  • Moderators
  • 570 posts
  • Last active: Feb 16 2014 03:38 PM
  • Joined: 19 Mar 2009
Wow... I think this one is a bit too complex for what it needs to be. For a simple copy/paste between applications hotkey, I'd write something really quick and simple, bind it to a hotkey and go... like this:

AppsKey::

WinActivate, Excel
Send, ^c{DOWN}
;hit the down key so that it will go to the next cell down the column

WinActivate, UFS
Send, ^v{DOWN}
;go to the next cell in this program, too

return

It is a bit annoying since you'll be there pressing one key for a significant amount of time, but since you're new, it's a good starting spot. And, you can definitely add in some loops later.

lagomorph
  • Members
  • 403 posts
  • Last active: May 15 2014 03:41 PM
  • Joined: 02 Apr 2010

It's a steeper learning curve, but the most reliable way to copy data from a cell(s) on an Excel spreadsheet is by using the COM Standard Library, more particularly the MS Office Functions.

I tried this for more than an hour and BARELY figured out how to include additional libraries. I set up the COM.ahk and the Excel.ahk libraries in my /lib directory, launched one of the test codes, and was met with several errors and nothing working correctly. I read 17 pages of comments on the thread and I eventually determined that the stuff I was attempting to use (COM and the related excel functions) were intended to be used with AHKL and COM_L

...so I tried to download AHKL and I couldn't find an installer anywhere, just like binary code for it or something (I guess I'm supposed to have some sort of compiler for it?? I DO NOT code AHK is my first and only experience in any kind of coding or scripting!!)

Please help and thank you for your replies.

and cameron, the idea behind automating this tedious process is that I can run the script and walk away while it automatically performs the task. There are literally 400+ cells involved in this spreadsheet and I am not going to sit there tapping a hotkey 400 times...!

camerb
  • Moderators
  • 570 posts
  • Last active: Feb 16 2014 03:38 PM
  • Joined: 19 Mar 2009
Indeed... but are you able to get that to work? If so, you can move on to making it more complicated (which I didn't suggest at first since you said you were new to AHK). You can check if that value you copied was blank, and if it was blank you could exit the AHK. Then, if all that goes well... enclose it in a loop, as below.

while true
{
WinActivate, Excel
Send, ^c{DOWN}
;hit the down key so that it will go to the next cell down the column

if (Clipboard="")
    return ; we've reached the end of the column

WinActivate, UFS
Send, ^v{DOWN}
}


sinkfaze
  • Moderators
  • 6365 posts
  • Last active:
  • Joined: 18 Mar 2008
Sorry for your troubles, getting the COM method to work will require you to jump into the deep end. But in order to achieve the functionality you're looking for it's probably the only good way to do it. Don't give up on it.

Here are the download links for use with AHK (not AHK_L):

COM Standard Library
MS Excel functions


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.

The method is just to take small steps make sure everything is working as you expect. Here's a first test for you to make sure you can get an object pointer to Excel:

COM_CoInitialize()
pxl := COM_GetActiveObject("Excel.Application")
MsgBox % pxl
COM_Release(pxl)
COM_CoUnInitialize()

If a message box pops up with a number, you've successfully retrieved the object pointer to Excel; if you get an error of some type post back to this thread with the information from the error(which you can copy by pressing Ctrl+C while the error message is active) so we can look into it further.

lagomorph
  • Members
  • 403 posts
  • Last active: May 15 2014 03:41 PM
  • Joined: 02 Apr 2010
But my script is working perfectly already. My question was if I could access the data in my spreadsheet without utilizing the clipboard at all.

The Excel functions through COM is exactly what I need but I just can't get it to work...Is it compatible with normal AHK or do I have to have AHKL? and COM_L?

lagomorph
  • Members
  • 403 posts
  • Last active: May 15 2014 03:41 PM
  • Joined: 02 Apr 2010

Sorry for your troubles, getting the COM method to work will require you to jump into the deep end. But in order to achieve the functionality you're looking for it's probably the only good way to do it. Don't give up on it.

Here are the download links for use with AHK (not AHK_L):

COM Standard Library
MS Excel functions


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.

The method is just to take small steps make sure everything is working as you expect. Here's a first test for you to make sure you can get an object pointer to Excel:

COM_CoInitialize()
pxl := COM_GetActiveObject("Excel.Application")
MsgBox % pxl
COM_Release(pxl)
COM_CoUnInitialize()

If a message box pops up with a number, you've successfully retrieved the object pointer to Excel; if you get an error of some type post back to this thread with the information from the error(which you can copy by pressing Ctrl+C while the error message is active) so we can look into it further.


it worked! I got a number in my msgbox!

Now I need to figure out how can I access a specific cell in a spreadsheet and then Send it to my other program...

THANK YOU!!! :D

camerb
  • Moderators
  • 570 posts
  • Last active: Feb 16 2014 03:38 PM
  • Joined: 19 Mar 2009
Oh, sounds like you're a bit more advanced, then. I wasn't going to suggest that you should have to dive that deep into AHK if you weren't that used to it yet. Have fun with COM, it's definitely a fun package.

lagomorph
  • Members
  • 403 posts
  • Last active: May 15 2014 03:41 PM
  • Joined: 02 Apr 2010

Oh, sounds like you're a bit more advanced, then. I wasn't going to suggest that you should have to dive that deep into AHK if you weren't that used to it yet. Have fun with COM, it's definitely a fun package.[/i]

hehe i said I was very new to AHK because I have been reading the COM and AHKL threads for the last 2 hours and feeling like a total unredeemable noob

sinkfaze
  • Moderators
  • 6365 posts
  • Last active:
  • Joined: 18 Mar 2008
Excellent. So let's try to get a value from a cell now. Here's a new test script with my modification of the Excel_GetCell function:

COM_CoInitialize()
MsgBox % Excel_GetCell()
COM_CoUninitialize()
return

Excel_GetCell(col="",row="") { ; if parameters are left blank will retrieve active cell
  xl := COM_GetActiveObject("Excel.Application")
  cl := COM_Invoke(xl,"Cells"
   , !row ? COM_Invoke(xl,"ActiveCell.Row") : row
   , !col ? COM_Invoke(xl,"ActiveCell.Column") : col)
  res := COM_Invoke(cl,"Value")
  COM_Release(cl),COM_Release(xl)
  return res
}


lagomorph
  • Members
  • 403 posts
  • Last active: May 15 2014 03:41 PM
  • Joined: 02 Apr 2010

---------------------------
exceltest.ahk
---------------------------
Error at line 7 in #include file "C:\Program Files\AutoHotkey\Lib\COM.ahk".

Line Text: COM_Init()
Error: Functions cannot contain functions.

The program will exit.
---------------------------
OK
---------------------------



sinkfaze
  • Moderators
  • 6365 posts
  • Last active:
  • Joined: 18 Mar 2008
The code I provided works fine on my end, check your exceltest.ahk file for any unmatched parenthesis () or braces {}.

lagomorph
  • Members
  • 403 posts
  • Last active: May 15 2014 03:41 PM
  • Joined: 02 Apr 2010

The code I provided works fine on my end, check your exceltest.ahk file for any unmatched parenthesis () or braces {}.


ahhh, you're right! I missed the final } in your script.

Now here's our new batch of errors:

---------------------------
COM Error Notification
---------------------------
Function Name: "Row"
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
---------------------------


I click continue twice, then

---------------------------
COM Error Notification
---------------------------
Function Name: "Cells"
ERROR: (0x800A03EC)
PROG:
DESC:
HELP: ,0

ERROR2: Exception occurred.

(0x80020009)

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


then the same errors, and then an empty msgbox.

sinkfaze
  • Moderators
  • 6365 posts
  • Last active:
  • Joined: 18 Mar 2008
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