AutoHotkey Community
It is currently May 27th, 2012, 1:27 pm

View unanswered posts | View active topics


Board index » AutoHotkey » Support

All times are UTC [ DST ]



Post new topic Reply to topic  Page 3 of 3
  [ 39 posts ]  Go to page Previous  1, 2, 3
  Print view | E-mail friend Previous topic | Next topic 
Author Message
Guest
 Post subject:
PostPosted: November 14th, 2011, 10:38 pm 
This is going to be a long a hard road for you, you really need to take baby steps. You can not include a Excel file in your script, you also make many typos.
Code:
#SingleInstance Force
#NoEnv

; #Include C:\Documents and Settings\rlp9124\My Documents\testtest.xlsx  ;;csv.ahk
; you can't "open" a Excel file like that
; Go to Excel, choose File, Save as, CSV format, file.csv
; Now go to the CSV library I linked to, copy the AHK code you see there and save
; it as csv.ahk. Place it in the same folder as this script.

#Include csv.ahk
CSV_Load("file.csv", "data") ; See data there, that identifies your "file" for the other functions
Cell := CSV_Search("data", "Customer number") ; see data here? That is your CSV file from above. Where it says Customer number provide an actual customer number you are looking for, say 12345 (I don't know of course)
MsgBox % Cell ; this should tell you which cell holds the customer data


Report this post
Top
  
Reply with quote  
BigMan
 Post subject:
PostPosted: November 15th, 2011, 6:25 pm 
Offline

Joined: November 10th, 2011, 11:52 pm
Posts: 131
Location: Heart of the Ozarks
Would love to take it slow, but per job I have to "fast path".
Lets skip a step and assume I am starting with a CSV file.
I do appreciate your trying to lead me down a pathway to enlightenment, but like I said, I seem to be getting pointed in many directions and I just need one path.
So here it is again:
1. the necessary columns of information will be a customer number (call it COL1) and the comment column (call it COL2) **from a CSV file
2. With this data collected, I want to run an AHK process that will look into another program (on our system) and add the appropriate "comment" (COL2) to the corresponding customer number.
3. Nobody needs to type anything, it just needs to run and add as it finds a matching customer number.

Does that make sense? Can AHK do something like this? Believe me I have searched and searched, and read more than I can remember.

It just always seems I find partials, then scripts with way to much, some using AHK_L, some using COM, some say don't use COM use something else...etc.
** as far as typos, most of this was copied from AHK site.


Report this post
Top
 Profile  
Reply with quote  
sinkfaze
 Post subject:
PostPosted: November 16th, 2011, 5:52 am 
Offline
User avatar

Joined: March 19th, 2008, 12:43 am
Posts: 5482
Location: the tunnel(?=light)
@BigMan

It looks to me like you might be better off using Excel and COM directly rather than converting everything to CSV.

If you have a way to retrieve the number from the program (and I assume you do), you can find that number in the list of numbers in the spreadsheet then retrieve the appropriate comment. Whether it can be sent directly to a field in the other program or is better sent to the Clipboard for you to paste manually will be for you to investigate. If you have just the one spreadsheet open (and the particular sheet is the active sheet), you could run something like this:

Code:
; assumes Customer# is in Column A, Comment is in Column B, and the Clipboard contains the Customer# from the program
xl :=   ComObjActive("Excel.Application")   ; connects to the active instance of Excel
if   r :=   xl.Columns("A").Find[Clipboard].Row   ; if we find the customer# and save that cell's row
   Clipboard :=   xl.Range["B" r].Formula   ; saves the applicable comment in that row to the Clipboard

_________________
Image
Try Quick Search for Autohotkey or see the tutorial for newbies.


Report this post
Top
 Profile  
Reply with quote  
BigMan
 Post subject:
PostPosted: November 17th, 2011, 10:14 pm 
Offline

Joined: November 10th, 2011, 11:52 pm
Posts: 131
Location: Heart of the Ozarks
I do appreciate your help with this. I have really been researching and continue to be impressed by AHK and what it can do. Of course if coded correctly.
Here is what I have come up with:
Code:
#E::            ;;;;HOTKEY is window key and the "E" key
Path = C:\Documents and Settings\xxx1234\My Documents\testcsv.xlsx

msgbox,262148,Get Information, Open File TESTCSV.XLSX
     IfMsgBox Yes
        Run %Path%
else
     IfMsgBox No
     return


Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(Path)     ;;;;open an existing file
Xl.Visible := True          ;;;;this necessary to display it
Xl.Range("A:B").Copy        ;;;copies all columns from A to B or A:D for ABCD


;;;DISPLAYS MESSAGE BOX TO COPY OR NOT
     msgbox, 262148,Copy, Copy columns A - B  ;;;adds 'pop up' box to say OK to copy selected cells
          IfMsgBox Yes
          WinActivate Clipboard
     else
          IfMsgBox No
          return


I would like to know on the Xl.Range part, how do you specify like if you wanted columns A and D only? (I will figure cells out later)
I also would like to add automation after clicking OK to copy columns that it takes me back to my original spot were I hit the HOTKEY.

I am not exactly sure where to add the information from your last post??? I worked on this part this morning.

I believe the new process could be:
User can hit the HOTKEY from "comment" area in Application and it should take them to Excel Spreadsheet, or where ever current data is stored.
From there they could then maybe search customer number of interest.
Then they could add comment back to the "comment" area in origianl Application????


I know my code ain't pretty but it's what I got....


Report this post
Top
 Profile  
Reply with quote  
SHASPOS60
 Post subject: Similar issue
PostPosted: December 19th, 2011, 3:33 am 
Offline

Joined: July 11th, 2011, 4:39 am
Posts: 21
I came across this thread looking for a solution to a similar issue. I am trying to create new folders with names of folders taken from Excel spreadsheet. I have used code from pg 1 of the thread but it's not working and I think I'm missing something about the clipboard variable

Code:
^!E::


WinActivate Microsoft Excel


; first cell

Send, ^{Home}


;  keep looping

Loop,
{

; copy the current cell

  clipboard = 
  Send, ^c
  ClipWait

; if empty cell then stop

  if clipboard =
  break


FileCreateDir, H:\CD Backups\Jacks, Fred\SERIES 22\%clipboard%
; jump back to excel

   WinActivate Microsoft Excel

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

}



Any help would be greatly appreciated!


Report this post
Top
 Profile  
Reply with quote  
sinkfaze
 Post subject: Re: Similar issue
PostPosted: December 19th, 2011, 4:00 pm 
Offline
User avatar

Joined: March 19th, 2008, 12:43 am
Posts: 5482
Location: the tunnel(?=light)
The method you're referencing is horribly outdated and inefficient. At the very least you could copy your entire list of folder names to the Clipboard and parse the Clipboard to create the files (pending your list is in one column):

Code:
; after you've manually copied the list to the Clipboard
Loop, parse, Clipboard, `n, `r
   if   !A_LoopField
      continue
   else   FileCreateDir, H:\CD Backups\Jacks, Fred\SERIES 22\%A_LoopField%

_________________
Image
Try Quick Search for Autohotkey or see the tutorial for newbies.


Report this post
Top
 Profile  
Reply with quote  
SHASPOS60
 Post subject:
PostPosted: December 20th, 2011, 12:14 am 
Offline

Joined: July 11th, 2011, 4:39 am
Posts: 21
Thank you so much sinkfaze! Worked amazingly!

Some folders were not created though, but thankfully I figured out that it is because they contain either ? or / which are illegal characters in folder names. Of course, I could do a find and replace in Excel but as I try to become more proficient in AHK, I would be interested to know if this could be done with the Loop, Parse function.


Thanks so much again!!! :lol:


Report this post
Top
 Profile  
Reply with quote  
Odlanir
 Post subject:
PostPosted: December 20th, 2011, 1:03 am 
Offline

Joined: August 7th, 2011, 1:23 pm
Posts: 754
Code:
Loop, parse, Clipboard, `n, `r
{
     if   !A_LoopField
      continue
     DirName := RegExReplace(A_LoopField, "[|\\?*<"":>+\[\]/]","_") ; change invalid char in "_"
     FileCreateDir  H:\CD Backups\Jacks, Fred\SERIES 22\%DirName%
}

_________________
Win7 - Firefox 10.0.2 - AHK_L 1.1.07.00
Please bear with me and my English which is so bad at times that even I don't understand myself


Report this post
Top
 Profile  
Reply with quote  
SHASPOS60
 Post subject:
PostPosted: December 20th, 2011, 1:34 am 
Offline

Joined: July 11th, 2011, 4:39 am
Posts: 21
Thank you so much Odlanir! :)


Report this post
Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 3 of 3
  [ 39 posts ]  Go to page Previous  1, 2, 3

Board index » AutoHotkey » Support

All times are UTC [ DST ]

Who is online

Users browsing this forum: BrandonHotkey, oldbrother and 19 guests

You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Powered by phpBB® Forum Software © phpBB Group