227 replies to this topic

[plastikglass]

RegExMatch(hay,"[0-9]+",parens)

RegExReplace(parens,"[\(\)]")
 

 

Wow, that actually worked. I had no idea about encasing the expressions in quotes.

 

But although this code did print out the numbers from the parathesis, how do you go about perserving the zeroes in front of the number?

 

In my case, it just writes out "8" and not the intended "0008".

 

Thanks in advance.

[ruespe]

Wow, that actually worked. I had no idea about encasing the expressions in quotes.
 

But although this code did print out the numbers from the parathesis, how do you go about perserving the zeroes in front of the number?
 

In my case, it just writes out "8" and not the intended "0008".
 

Thanks in advance.

[0-9]+ and [\(\)] are no expressions but strings. (hay,"[0-9]+",parens) is an expression. And strings within expressions have to be written in quotes.

 

And try hay := c.Text instead of hay := c.Value
 

[gambe]

hi everybody, i'm a beginner, i started yesterday with Ahk.. i need a big help:
i have to login to a web site, copy some data from it an paste to an existing  excel spreadsheet, but i have no idea how to do it!!
i read other tutorial here but i can't understand anything, becasue i'm too much beginner, thank you all for the help!
thanks to this forum i wrote down this to log in to the web site:

 

Loginname := "user"
Password := "password"

URL := "https://www.*******.com/"

WB := ComObjCreate("InternetExplorer.Application")
WB.Visible := True
WB.Navigate(URL)
While wb.readyState != 4 || wb.document.readyState != "complete" || wb.busy ; wait for the page to load
   Sleep, 10
wb.document.getElementsByTagName("INPUT")[2].value := Loginname
wb.document.getElementsByTagName("INPUT")[3].value := Password
wb.document.getElementsByTagName("INPUT")[5].Click()
While wb.readyState != 4 || wb.document.readyState != "complete" || wb.busy ; wait for the page to load
   Sleep, 10

[mevcrna]

I'm trying to automate some processes to copy data from one Excel sheet to another using "Insert Copied Cells"

The macro looks like this:

 

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A3:F8").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
End Sub
 

The Autohotkey Macro looks like this:

 

Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open("MySpreadsheet.xlsx") ;open an existing file
Xl.Visible := True ;make spreadsheet visible
Xl.Range("A3:F8").Select ;select the range to act on
X1.Range.Selection.Copy ;copy the selected range
X1.Selection.Insert Shift:=xlDown ;insert in the selected area and shift cells down.

 

When I run the macro, I get an error message that the last line is an "unrecognized action".  SInce this is cut and paste from a macro, how can it not be recognized?

 

Any suggestions appreciated.  And yes, I am reading MSDN and Searching these forums but not luck so far.

Thank you.

 

[Jackie Sztuk _Blackholyman]

Xl not = X1


Also insert is a function

Range.Insert(Shift:=xlDown, CopyOrigin)

you also need to set xlDown

Hope it helps

[mevcrna]

Typos are going to kill me.

Thank you for your patient reply.

[SandyR]

I've just started using AHK and I'm stuck and would love some help.  I'm trying to copy data from the first worksheet of an existing Excel workbook, create a new worksheet as the third worksheet in the workbook, and copy the data in.  I used the Excel Macro recorder to get the VB code to add a worksheet in the third position and got: 

 

    Sheets.Add After:=Sheets(Sheets.Count)

 

But when I try to put this in my AHK script as

    xl.Sheets.Add After:=Sheets(2)

 

I get an error saying the line does not contain a recognized action.  I'm assuming the "After:=" is throwing things off, but I don't know how to get around it.  If I leave off the After part, the worksheet gets added in the first position, which I don't want. I looked into moving it after creating it, but the VB command for that involves the same ":=".

 

Any advice?

 

 

[sinkfaze]

Any advice?

 
Yes, bookmark the online 2003 Excel Visual Basic Reference or download the 2003 Excel Visual Basic Reference help file. They aren't entirely up to date with 2010, but the vast majority of the core functionality is the same, and you will want these references around to help you.

In your case, the problem is not the variable After, it's the fact that VBA can utilize named parameters for its functions, but AHK cannot use them in that same way; AHK has to use the parameters in order. So in the case of the Add method:
 

expression.Add(Before, After, Count, Type)

 
So we just need to fill in the second parameter. But before we tackle this, you need to be aware of another AHK function used to deal with optional parameters we intend to skip. This function is ComObjMissing(), which we can save to a variable. We need this because we would like to skip the first parameter and use only the second parameter.

So let's take a look at a piece of sample code as written in AHK based on your needs, which assumes that you already have a workbook open and it is the active workbook:

xl :=	ComObjActive("Excel.Application")	; connects to the active instance of Excel
 , _ :=	ComObjMissing()	; saving the capability to "skip" a parameter to var
xl.Sheets.Add(_,2)	; adds a sheet after the second sheet

But now as regards the code from the macro recorder:
 

Sheets.Add After:=Sheets(Sheets.Count)

 
You'll need to do something slightly different:

xl :=	ComObjActive("Excel.Application")	; connects to the active instance of Excel
 , _ :=	ComObjMissing()	; saving the capability to "skip" a parameter to var
xl.Sheets.Add(_,xl.Sheets.Count)	; adds a sheet after the last sheet

HTH

[udhay]

Hello Mickers,

Greetings for the day,

 

I am a beginner to AHK, Could you please suggest me how to use the COM in Program, when i try to copy & paste the above seen codes into my program i am getting errors. Could you please help me to understand the syntax or how to use COM to link Excel data to a variable in AHK. It would be so clear if you could show a full program as a example to link Excel data to a variable in AHK.

 

Thanks a lot in Advance.

[SandyR]

Thanks for the help sinkfaze - you explained it very clearly!  Here's the code that ended up working for me.  I'm not sure why I had to use Book.Sheets.Add, but when I tried using just Sheets.Add, I got errors, so I went with this.

 

xlFile := "myfilename"

 

xl := ComObjCreate("Excel.Application")
_ := ComObjMissing()

Book:= xl.Workbooks.Open(xlFile)
xl.Visible := True
Sheet := xl.ActiveSheet

Book.Sheets.Add(_, Book.Sheets(Book.Sheets.Count), _,_)

 

[Cruncher1]

@Mickers et al.

 

A million thanks. This was exactly what I was looking for! No more nonsense with SendInput {TAB} and all the mouse clicks, I can just send the values straight to the right cells and get them right back out into AHK variables too.

 

Perfect. I knew there had to be a way!

[Joe Glines]

I'm using COM to import files into Excel, manipulate the file, then save and quit excel.  Unfortunately when I open up Excel the file i just manipulated is not in the recently accessed files (under the file menu)  Is there a way to get Excel to show it or a way to "fake" it?

[sinkfaze]

Untested:

filePath :=	A_Desktop "\test.xlsx", mruList :=	[]
For file in ComObj("Excel.Application").RecentFiles
	if	(A_Index <> 1)
		mruList.Insert(file.name)
mruList.Insert(RegExReplace(filePath,"^[A-Z]:"))
For file in ComObj("Excel.Application").RecentFiles
	file.name :=	mruList[A_Index]

[Joe Glines]

Thanks sinkfaze!  I'm able to follow most of the code but am haven't a clue why there is a need to state the A_Index is not 1 when inserting the file name into the MRUList.

 

I tried it out and it reports the following error for this line: file.name := mruList[A_Index]

 

Error 0x8002000E - Invalid number of parameters.

Specifically: name

 

I was able to look at your example and use the following to add it  (XL was a pointer to the Excel Application)

 

XL.RecentFiles.Add("sinkfaze rocks.xls")

 

[sinkfaze]

...haven't a clue why there is a need to state the A_Index is not 1 when inserting the file name into the MRUList.

 

Since you're copying out the current file list and adding the newest file to it, I was under the assumption that the list is already at maximum capacity and the last file would need to be "bumped" off the list in favor of the new file. Apparently it wasn't going to work that way anyway, but at least you found the workaround.