Shortage for few auto excel operations ?

Get help with using AutoHotkey and its commands and hotkeys
sirljove
Posts: 4
Joined: 26 Oct 2017, 09:12

Shortage for few auto excel operations ?

26 Oct 2017, 09:37

Hello I am not a very experienced person with Excel and advance functions so I need help with this 3 problems that keep spending infinite time and nerves. :crazy:

1. I have 20 opened tabs in my browser and I want to paste all the 20 URLs to a certain Excel column and selected 15-20 rows in that column. (for an example paste 20 URL addresses to Excel starting from B1 concluding with B20.

2. Frequently when I receive an Excel file with multiple link addresses in one row I need to double click on each link address(Excel cell) to transform it into an actual Hyperlink and only then I can open those link addresses with one click. Is there a way to transform a bulk of 20 Excel cells into actual Hyperlinks or I need to double-click on each cell one by one to transform them into Hyperlinks ?

3. I receive an Excel file and need to open all of the addresses from a certain column( B1 to B20). I always click on one then the browser opens then I Alt+Tab to Excel and click on the next link, and repeat that for 20 times.
Is there a way to open 20 link addresses from 20 Excel cells (B1 to B20) all at once in 20 browser tabs ?

I would appreciate any kind of help, or some redirection for help.
Thanks very much.
User avatar
king-of-hearts
Posts: 30
Joined: 01 Oct 2017, 06:29

Re: Shortage for few auto excel operations ?

26 Oct 2017, 10:06

First, open the Excel with the links.

Here the code:

Code: Select all

xls := ComObjActive("Excel.Application")
for cell in xls.activeworkbook.sheets(1).range("b1:b20")
	Run, % cell.value
return
https://autohotkey.com/boards/viewtopic.php?f=6&t=38707 - MS Access Manager - SQL Query: Incredible tool for MS Access/SQL Queries on the fly!!
sirljove
Posts: 4
Joined: 26 Oct 2017, 09:12

Re: Shortage for few auto excel operations ?

26 Oct 2017, 11:25

king-of-hearts wrote:First, open the Excel with the links.

Here the code:

Code: Select all

xls := ComObjActive("Excel.Application")
for cell in xls.activeworkbook.sheets(1).range("b1:b20")
	Run, % cell.value
return
Thanks very much for quick reply!
I am a total basic user I am not sure where should I paste this text to in order to try it ?
User avatar
king-of-hearts
Posts: 30
Joined: 01 Oct 2017, 06:29

Re: Shortage for few auto excel operations ?

26 Oct 2017, 11:40

You should put this code inside an AHK Script then run.

There's a tutorial how to do it: https://autohotkey.com/docs/Tutorial.htm
https://autohotkey.com/boards/viewtopic.php?f=6&t=38707 - MS Access Manager - SQL Query: Incredible tool for MS Access/SQL Queries on the fly!!
sirljove
Posts: 4
Joined: 26 Oct 2017, 09:12

Re: Shortage for few auto excel operations ?

26 Oct 2017, 13:01

I made the script, when I run it nothing actually happens...
Browser is opened with multiple tabs, excel also opened.
User avatar
king-of-hearts
Posts: 30
Joined: 01 Oct 2017, 06:29

Re: Shortage for few auto excel operations ?

26 Oct 2017, 13:11

Hmm, in mine this works fine (the code executes the last active Excel file and selects the cells from the first sheet!)


I just put this on column B:

Code: Select all

http://www.google.com
http://www.google.com
http://www.google.com
http://www.google.com
http://www.google.com
https://autohotkey.com/boards/viewtopic.php?f=6&t=38707 - MS Access Manager - SQL Query: Incredible tool for MS Access/SQL Queries on the fly!!
sirljove
Posts: 4
Joined: 26 Oct 2017, 09:12

Re: Shortage for few auto excel operations ?

26 Oct 2017, 15:41

No it is simply not working either nothing happens or an error appears.
Maybe I made the script in a bad way but I am pretty sure that I did it exactly like in the tutorial.
User avatar
jeeswg
Posts: 5956
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Shortage for few auto excel operations ?

26 Oct 2017, 22:42

Here is some code that retrieves urls from Internet Explorer and puts them into Excel. With bits of code that can be enabled/disabled by setting 'if 0' and 'if 1'. I don't know of any ways to retrieve multiple urls from web browsers other than Internet Explorer.

Code: Select all

;[Excel_Get function]
;excel and com? - Ask for Help - AutoHotkey Community
;https://autohotkey.com/board/topic/73940-excel-and-com/#entry469769

q:: ;get urls from Internet Explorer, and insert into Excel instance
oArray := {}

;get urls from Internet Explorer (all windows) (by order tab created)
if 0
{
	for oWin in ComObjCreate("Shell.Application").Windows
		if (oWin.Name = "Internet Explorer")
			oArray.Push(oWin.document.url)
	oWin := ""
}

;get urls from Internet Explorer (most recently active window) (by tab order left to right, although may not retrieve all tabs if too many)
if 1
{
	ControlGet, hCtl, Hwnd,, TabBandClass1, ahk_class IEFrame
	oAcc := Acc_Get("Object", "4.1.4.1", 0, "ahk_id " hCtl)
	for _, oChild in Acc_Children(oAcc)
	{
		oTemp := StrSplit(oChild.accDescription(0), "`r`n")
		if InStr(oTemp.2, "://")
			oArray.Push(oTemp.2)
	}
}

;open a new Excel workbook
if 0
{
	oXl := ComObjCreate("Excel.Application")
	oXl.Visible := True
	oXl.Workbooks.Add
}

;open an existing workbook
if 0
{
	oXl := ComObjCreate("Excel.Application")
	oXl.Visible := True
	vPath := A_Desktop "\MyFile.xlsx"
	oXl.Workbooks.Open(vPath)
}

;operate on an open workbook
if 1
{
	WinGet, hWnd, ID, ahk_class XLMAIN
	oXl := Excel_Get()
}

;put urls into column as text
;Loop, % oArray.Length()
;	oXl.Cells(A_Index,1).Value := oArray[A_Index]

;put urls into cells as hyperlinks
;Hyperlinks.Add Method (Excel)
;https://msdn.microsoft.com/en-us/vba/excel-vba/articles/hyperlinks-add-method-excel
Loop, % oArray.Length()
	oXl.Cells(A_Index,1).Hyperlinks.Add(oXl.Cells(A_Index,1), oArray[A_Index])

;run hyperlinks contained in cells
Loop, % oArray.Length()
{
	vUrl := oXl.Cells(A_Index,1).Hyperlinks(1).Address
	MsgBox, % vUrl
	;Run, % vUrl ;open with default browser
	Sleep, 300
}
return
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], boardtc, Kobaltauge, Odlanir, SALZKARTOFFEEEL and 58 guests