AutoHotkey Homepage AutoHotkey Community
Let's help each other out
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Copy a list from Excel
Goto page 1, 2  Next
 
Post new topic   Reply to topic    AutoHotkey Community Forum Index -> Ask for Help
View previous topic :: View next topic  
Author Message
JOXER



Joined: 30 Jun 2004
Posts: 3
PostPosted: Wed Jun 30, 2004 2:34 pm    Post subject: Copy a list from Excel Reply with quote
Hi,
I'm trying to copy a list of numbers, one by one, from Excel to an accounts package.
I am only using AutoHotKey a few hours so am a bit lost..
I have used the Window Spy to find my starting cell, and can copy the cell using
MouseClick, left, 87, 164
Sleep, 100
Send, {CTRLDOWN}c{CTRLUP}

but can't get back to the starting position to move one cell down...I belive I could then loop this if it worked...
I have tried MouseMove, but get syntax errors....any ideas ?
Thanks...
Back to top
View user's profile Send private message
BoBo
Guest
PostPosted: Wed Jun 30, 2004 2:57 pm    Post subject: Reply with quote
What about to create/write a plain text file following the standard *.csv (comma separated value) syntax ?

var1,var2,var3,var4,...

a) parse the (MyExcelFile.csv) file directly using a loop
b) pipe the content of each single cell into whatever destination file

if that sounds to complex give us a line of your source file and a sample line of the format how it should look like in the destination file, and let's see how we can make it ...
Back to top
JOXER



Joined: 30 Jun 2004
Posts: 3
PostPosted: Wed Jun 30, 2004 4:18 pm    Post subject: Copy a list from Excel Reply with quote
That sounds like it could work....but having difficulty getting list of numbers from Excel format to csv format...even when i save as .csv, they still look like this
987374
987375
987376

as opposed to 987374, 987375, 987376 etc.

any thoughts...I'm not a programmer, just a lowly accounts person !
Back to top
View user's profile Send private message
Guest
Guest
PostPosted: Wed Jun 30, 2004 6:41 pm    Post subject: Coping Excel Cells Reply with quote
I cannot help you directly, but have had great failure with coping Excel cells, until I realized you cannot just copy, you get other things as well.

This is my solution and works perfectly, but there must be a better way.

I have this as a gosub to copyv. after putting the cursor in a cell.

;COPY VARIABLES, USING EXCEL F2 AS A METHOD TO STRIP OUT THE RETURN AT THE END OF EACH EXCEL CELL

COPYV:

Send, {F2}{HOME}{SHIFTDOWN}{END}{SHIFTUP}{CTRLDOWN}c{CTRLUP}{TAB}

RETURN
Back to top
Chris
Site Admin


Joined: 02 Mar 2004
Posts: 10467
PostPosted: Wed Jun 30, 2004 7:02 pm    Post subject: Reply with quote
In Excel, it's probably best to avoid navigating with the mouse. Instead, use keystrokes. For example:

Send, ^{Home} ; Go to Row 1 Col 1
Send, ^{End} ; Go to lower-right cell
Send, ^{PgUp 9}^{PgDn 2} ; Go to the 3rd worksheet or tab.

; Jump directly to a specific cell:
Send, ^g
WinWaitActive, Go To
Send, B18{enter}

Also, the following is a more reliable way to copy:
clipboard = ; start off empty
Send, ^c
ClipWait
; At this point, you can continue with confidence, knowing something has been copied to the clipboard.

If needed, to remove the CRLF from the end of the copied cell (before pasting it), you can also use this method:
StringReplace, clipboard, clipboard, `r`n

If you have a large quantity of data to copy over (perhaps 100 rows or more), it might be better to use a parsing loop. If that is the case, we can help you with that.
Back to top
View user's profile Send private message Send e-mail
JOXER



Joined: 30 Jun 2004
Posts: 3
PostPosted: Fri Jul 02, 2004 5:33 pm    Post subject: Reply with quote
First of all, allow me to say thanks for all the help; it is much appreciated.
The Parsing Loop sounds the best, as I have around 3000 rows of data -(and that's just this week). I need one value copied to an accounts package (different window) and then to press ok in this window, then to come back to Excel and take the next value and do the same....3000 times...
Imagine if you had a list of names and you had to enter them into a Database, but only one at a time, and to validate each one (by pressing ok)
Data looks something like this:
987374
987375
987376
987377
987499

Each of these has to be entered into the system one by one.

I know you may ask why I can't just import them directly using Access \ SQL or similar, and it's because I am not entering these numbers into a Table as such. These numbers are being entered as values of a programme which will then perform a job on these numbers.

Example - when I launch this programme, it asks me "What numbers do you wish to operate on ?" and I have to enter the 1st number, and press Ok, enter the 2nd number, press ok....etc. Then the programme performs an operation on all the numbers I have just entered.
Hope that explains a bit more clearly.

Thanks a lot for any new ideas ....!
Smile
Back to top
View user's profile Send private message
Chris
Site Admin


Joined: 02 Mar 2004
Posts: 10467
PostPosted: Fri Jul 02, 2004 8:16 pm    Post subject: Reply with quote
If you haven't already, be sure to read the quick-start tutorial at http://www.autohotkey.com/docs/Tutorial.htm -- it explains how to send keystrokes, activate windows, etc.

For the job you described, it sounds like a cycle of Copy - ClipWait - WinActivate - Paste will work. You can get more sophisticated (and possibly speed up the process) by using ControlSetText (which might possibly avoid the need to activate the target window). But if speed isn't important, you can probably stick to WinActivate and the Send command.

If you need something more complex to automate this process, feel free to ask more specific questions (which tend to generate more useful answers).
Back to top
View user's profile Send private message Send e-mail
Pallie
Guest
PostPosted: Sun Jul 04, 2004 3:00 pm    Post subject: Reply with quote
JOXER

I'm trying to do a similar thing but I you are ahead of me, can you let me know what you have got already?

Thanks

Mike
Back to top
Pallie
Guest
PostPosted: Mon Jul 05, 2004 1:37 am    Post subject: Reply with quote
Well, I suprised myself - spent an hour on it and managed to get something working. This copies the contents of column A in excel into word one cell at a time (make sure that both word and excel are open an they are Document/Book 1)

Code:

; triggered by pressing the windows key and the X key

#x::

; jump to excel

   WinActivate Microsoft Excel - Book1

; first cell

Send, ^{Home}


;  keep looping

Loop,
{

; copy the current cell

  clipboard = ;
  Send, ^c
  ClipWait
  StringReplace, clipboard, clipboard, `r`n

; if empty cell then stop

  if clipboard =
    break

; jump to word

   WinActivate Microsoft Word - Document1

; paste in the clipboard contents

  Send, ^v

; for word add a return at the end to get a new line
 
  Send, {Return}

; jump back to excel

   WinActivate Microsoft Excel - Book1

; go down to the next cell in excel
 
  send, {Down}
}


For your application, just replace the word WinActivate with the app you want and make sure you process the screen.

Mike
Back to top
Guest
PostPosted: Mon Jul 05, 2004 7:21 pm    Post subject: Reply with quote
That worked brilliantly - thanks a lot for all the help and aplologies too for non specific questions - this has saved me many hours plugging away..
Cheers
Joxer
Very Happy
Back to top
Pallie
Guest
PostPosted: Mon Jul 05, 2004 8:22 pm    Post subject: Reply with quote
I only found the download yesterday. To start with it looked daunting, the tuturial starts at too an advanced stage - it assumes you know too much. But the bit that is actually missing is only a tiny bit to explain how it fits together. If I get chance I'll write up that bit before I get too proficient (which I will given how usefull it is) and get to assume everyone knows more than they do. It is the least I can do for such a brilliant tool.

Mike
Back to top
Chris
Site Admin


Joined: 02 Mar 2004
Posts: 10467
PostPosted: Mon Jul 05, 2004 8:28 pm    Post subject: Reply with quote
Quote:
the tuturial starts at too an advanced stage - it assumes you know too much. But the bit that is actually missing is only a tiny bit to explain how it fits together

I value your perspective on this. Often myself and others who have scripted a lot don't realize what things look like to someone just learning. I'll try to incorporate whatever ideas you offer into the tutorial. Thanks.
Back to top
View user's profile Send private message Send e-mail
adamisageek



Joined: 05 Mar 2007
Posts: 20
Location: Erie, PA
PostPosted: Thu Mar 27, 2008 8:57 pm    Post subject: Reply with quote
I use AHK and Excel all day for my profession (pricing analyst for a large retail computer site) and I have some pointers that might help/clear some stuff up. Or not, just my $.02 Smile

Code:
; jump to excel

   WinActivate Microsoft Excel - Book1

It's ok to change this to:
Code:
WinActivate Microsoft Excel

This way the title doesn't need to say "Book1" so you can use it in any Excel document. But for more preciseness, use this:
Code:
IfWinExist ahk_class XLMAIN
   {
      WinActivate
      WinWaitActive ahk_class XLMAIN
   }
   else
   {
      msgbox Excel not running.`r`nStopping command.
      return 1
   }

The above code uses Microsoft Excel's class name, instead of it's Window Title text to activate it.

The same thing can be used with Word as well so instead of using:
Code:
WinActivate Microsoft Word - Document1

you can use:
Code:
WinActivate Microsoft Word

to use word document's not named "Document1".

When outputting the contents of the clipboard, I've found that using:
Code:
SendInput %clipboard%

is much faster than sending the keystroke to paste it:
Code:
Send, ^v


If you're going to add a return after outputting a variable like this:
Code:

; for word add a return at the end to get a new line 

  Send, {Return}

leaving the CRLF (`r`n) on the end of the data copied from Excel will output a return automatically so the Sending of a {Return} character is unnecessary. Although, I admit, sometimes I like to code scripts the less efficient way sometimes just so when I come back to the code later on, I can see that there's a {Return} getting sent for a reason. Wink

Also, I think Pallie forgot a
Code:
Return
after his loop ending.

Pallie wrote:
To start with it looked daunting, the tuturial starts at too an advanced stage - it assumes you know too much. But the bit that is actually missing is only a tiny bit to explain how it fits together.

I think you'll have this with any scripting language, and yes, sometimes it can be frustrating trying to learn something new from scratch. Wink
Just remember, most all programming languages (AHK included) use different commands and syntax to do everything. This is why it's important to understand the act of programming, what it is, and how to do it. It is at that point that you can start to learn a new programming language, such as AHK.
Here are some common questions about programming answered:
http://www.guidetoprogramming.com/main_site_conent/site_content/
_________________
My AutoHotKey.com username has changed to GeekyAdam
Back to top
View user's profile Send private message Visit poster's website AIM Address
BoBoĻ
Guest
PostPosted: Thu Mar 27, 2008 9:38 pm    Post subject: Reply with quote
@ adamisageek/JOXER
Check out [ADO (by Sean)]. It offers a more sophisticated way to extract/inject data into an Excel sheet.
Back to top
adamisageek



Joined: 05 Mar 2007
Posts: 20
Location: Erie, PA
PostPosted: Thu Mar 27, 2008 9:45 pm    Post subject: Reply with quote
I looked into that a little BoBo, today actually. But it seems more complicated than the simple functions I use in my script.

Granted, it could be a lot more precise/efficient but for now, I'm going to have to wait...it seems like I'll have to learn another entirely different programming language to use the ADO stuff.

I probably will though eventually, I have some ideas that will make my job much much easier, I just need to do some research first and start the huge overhaul to my code. Rolling Eyes
_________________
My AutoHotKey.com username has changed to GeekyAdam
Back to top
View user's profile Send private message Visit poster's website AIM Address
Display posts from previous:   
Post new topic   Reply to topic    AutoHotkey Community Forum Index -> Ask for Help All times are GMT
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You can post new topics in this forum
You can reply to topics in this forum


Powered by phpBB © 2001, 2005 phpBB Group