Jump to content

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

Basic Ahk_L COM Tutorial for Excel


  • Please log in to reply
227 replies to this topic
Joe Glines
  • Members
  • 118 posts
  • Last active: Jan 24 2016 03:08 PM
  • Joined: 23 Dec 2009

I'm searching through a range of 30,000 cells in Excel and the following works fine to replace any @ it finds.  

For Cell in XL.Range(replace_Rg){
Text:= Cell.Value
StringReplace, text, text, @,,
Cell.Value:=Text
}

 

I'm trying to use StringReplace as I believe it would be faster than a regex.   My questions are

1) Am I correct that StringReplace will be faster

2) is there a way to streamline the above code?  I was thinking something like this but can't get it to work.

 

For Cell in XL.Range(replace_Rg){
StringReplace, Cell.value, Cell.Value,@,,
}

Automating the mundane 1 script at a time...
https://www.linkedin.com/in/joeglines
The-Automator

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
XL.Range(replace_Rg).Replace("@","")


Joe Glines
  • Members
  • 118 posts
  • Last active: Jan 24 2016 03:08 PM
  • Joined: 23 Dec 2009

That works lightning fast and is awesome!  I absolutely love being reminded that I haven't a clue what I'm doing!  It makes work so much fun!  

 

Thanks, again, for all the help! 


Automating the mundane 1 script at a time...
https://www.linkedin.com/in/joeglines
The-Automator

Hamlet
  • Members
  • 302 posts
  • Last active: Mar 23 2014 03:37 PM
  • Joined: 22 Jan 2009

I am making Excel file from text file.

<<A>> is fine. But <<B>> has problem.

Can you tell me why ?

 

<<A>>

FileAppend, TeSTiNG,     test.txt
EX := ComObjCreate( "Excel.Application")     
EX.Workbooks.OpenText( "test.txt")
EX.ActiveWorkbook.SaveAs( "test.xls",  -4143)       
;      "xlWorkbookNormal (xlNormal)"  >  -4143 
EX.Quit            
FileDelete, test.txt

 

 

<<B>>

FileAppend, TeSTiNG,     test.txt
EX := ComObjCreate( "Excel.Application")     
WB := EX.Workbooks.OpenText( "test.txt")
WB.SaveAs( "test.xls",  -4143)                    
EX.Quit            
FileDelete, test.txt

 

 

Thanks..

 

(I am with Excel 2003 and Vista)



plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

Hey guys, I was wondering if the community could help me out with an idea.

 

I'm working on adjusting quantities of items in our inventory system. If I have to change the quantity on hand of an item, I have to plug in all the data by hand into a shared Excel spreadsheet.

 

The problem with this, besides it being extremely tedious, is that I am not always at my desk: I am usually realizing these changes in the warehouse.

 

Would it be possible to send the data to Autohotkey, and from there, AHK can start plugging in all the data into the spreadsheet automatically on my computer?

 

Thanks.



StepO
  • Members
  • 195 posts
  • Last active: Mar 06 2014 05:02 PM
  • Joined: 05 Sep 2011

I'd think the easiest way would be to have a smartphone and a PC with access to the same dropbox folder.

 

On the PC you run an ahk script with settimer, that regularly checks for contents in a text file in that folder.

 

On the phone you just write something like "apples:4" in that text file.

 

The script then just splits the line into the needle and the amount and does something like

ln := sheet.range("A:A").find(needle).row

sheet.range("B" . ln).value := amount

Try to see how far you get and I'm sure people in support will help.


Feel free to correct me when you spot any mistakes. Please?


Quickbuzz
  • Members
  • 1 posts
  • Last active: Aug 16 2013 05:20 PM
  • Joined: 09 Jul 2013

How would you insert a row at the top of an excel document?



Jackie Sztuk _Blackholyman
  • Spam Officer
  • 3757 posts
  • Last active: Apr 03 2016 08:47 PM
  • Joined: 28 Feb 2012

How would you insert a row at the top of an excel document?

You can do it like this!
oExcel := ComObjCreate("Excel.Application") ; create Excel Application object
oExcel.Workbooks.Add ; create a new workbook

oExcel.Range("A1").Value := 3 ; set cell A1 to 3
oExcel.Range("A1").EntireRow.Insert ; insert EntireRow

oExcel.Visible := 1 ; make Excel Application visible
ExitApp

Helping%20you%20learn%20autohotkey.jpg?d

[AHK] Version. 1.1+ [CLOUD] DropBox ; Copy [WEBSITE] Blog ; About

scout
  • Members
  • 134 posts
  • Last active: Jun 11 2015 01:41 PM
  • Joined: 11 Apr 2011

How to add object (oleObject.add)?

f7::
;xl := ComObjActive("Excel.Application")
;FileReadLine, path, GetFilesPaths.txt,1
xl := ComObjCreate("Excel.Application")
xl.Visible := 1
xl.Workbooks.Add
VFile := "C:\Users\jeneves\Documents\B.jpg"
xl.ActiveSheet.OleObjects.Add(Filename :="C:\Users\jeneves\Documents\B.jpg", False, False).Select
return

Im getting error on this.... "Unable to add property"



sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
Try this:

f7::
xl:= ComObjCreate("Excel.Application"), _ := ComObjMissing()
xl.Visible := 1
xl.Workbooks.Add
VFile := "C:\Users\jeneves\Documents\B.jpg"
xl.ActiveSheet.OleObjects.Add(_,VFile)
return
AHK doesn't handle VBA's named parameters (yet).

scout
  • Members
  • 134 posts
  • Last active: Jun 11 2015 01:41 PM
  • Joined: 11 Apr 2011

skinkfaze

 

that works!

 

So, the problem was that i wasnt informing the first paramater (ClassType) ?

 

I tried many diferents way:

 

xl.ActiveSheet.OleObjects.Add( ,VFile)

xl.ActiveSheet.OleObjects.Add("",VFile)

 

could you explain for me the use of _ := ComObjMissing() ?

 

Thank you so much.



jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
No need to worry about it - your omitted parameter attempt will work soon enough: Available for testing: enhancements to function-calling

scout
  • Members
  • 134 posts
  • Last active: Jun 11 2015 01:41 PM
  • Joined: 11 Apr 2011

Got it... so it will be implemented in our official AHK soon.

 

Thank you.



					
					

cdjones
  • Members
  • 73 posts
  • Last active: Feb 07 2014 09:42 PM
  • Joined: 15 Sep 2009

Hey there,

 

I've made a book/sheet navigator for excel using the tips (and code) from this site.

 

I'm trying to close a workbook with

        oexcel.displayalerts :=true
        oExcel.Workbooks(clicked).Close()

This closes the book with changes saved without the Excel "Do you want to save the changes ?" box.

 

I'd like the excel option box to come up. what do I need to change ?

 

 



Joe Glines
  • Members
  • 118 posts
  • Last active: Jan 24 2016 03:08 PM
  • Joined: 23 Dec 2009

I worked on this library about a year ago.  I always thought I'd have time to get back to it and better organize it plus work out some of the bugs but just haven't had the time.  I thought I'd post it here in case someone finds it useful. 

 

I'm not a programmmer so some of these are going to seem very simplistic but I've found them very helpful when automating Excel.  XL Library


Automating the mundane 1 script at a time...
https://www.linkedin.com/in/joeglines
The-Automator