copy the selection from webpage and pasting in excel Topic is solved

[Immanuel]

Hi !

I am new to this auto-hotkeys !

I need to create 3 shortcut keys for copying the selected text or data from browser and paste it in excel ,
there are 3 columns in excel , and three shortcut keys , if i press the first shortcut key the selected data should be pasted in the excel 1 column and if
i press the 2 shortcut key the text should be pasted in the 2 column like wise for 3 shortcut keys and after pasting the text when i do this action again the excel cell should automatically move to the new row and paste the text .

Serously i know my below code is a mess but please kindly help me in this !

Code: Select all

^q:: ;excel - populate row
oXl := ComObjCreate("Excel.Application")
oXl.Visible := True
oXl.Workbooks.Add

QuoteSelection()
{
	selection:= GetSelection()  
	PasteText(Quote(selection))  
}

;version 1 (simpler, for fewer cells)
vText := selection
Loop, Parse, vText, `t
	oXl.Cells(1, A_Index) := A_LoopField


^W:: ;excel - populate row
oXl := ComObjCreate("Excel.Application")
oXl.Visible := True
oXl.Workbooks.Add

QuoteSelection()
{
	selection:= GetSelection()  
	PasteText(Quote(selection))  
}

;version 1 (simpler, for fewer cells)
vText := selection
Loop, Parse, vText, `t
	oXl.Cells(1, A_Index) := A_LoopField


^e:: ;excel - populate row
oXl := ComObjCreate("Excel.Application")
oXl.Visible := True
oXl.Workbooks.Add

QuoteSelection()
{
	selection:= GetSelection()  
	PasteText(Quote(selection))  
}

;version 1 (simpler, for fewer cells)
vText := selection
Loop, Parse, vText, `t
	oXl.Cells(1, A_Index) := A_LoopField

[awel20]

Code: Select all

^q::
^w::
^e::
    ; Copy
    Clipboard := ""
    Send, ^c
    ClipWait, 1
    if (ErrorLevel != 0)
    {
        MsgBox, 48, Copy,  Nothing was copied.
        return
    }
    ; Get the active Excel app. Excel needs to be running.
    ExcelApp := Excel_Get()
    if !IsObject(ExcelApp)
    {
        MsgBox, 48, Excel,  % "Can't find Excel.`n" ExcelApp
        return
    }
    ; Get the column to use
    if (A_ThisHotkey = "^q")
        Col := 1
    else if (A_ThisHotkey = "^w")
        Col := 2
    else if (A_ThisHotkey = "^e")
        Col := 3
    ExcelApp.ActiveCell.EntireRow.Cells(Col).Value := Clipboard
    ; Select the next row
    if (A_ThisHotkey = "^e")
        ExcelApp.ActiveCell.Offset(1, 0).Select
return

; Excel_Get by jethrow (modified)
; Forum:    https://autohotkey.com/boards/viewtopic.php?f=6&t=31840
; Github:   https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk
Excel_Get(WinTitle:="ahk_class XLMAIN", Excel7#:=1) {
    static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
    WinGetClass, WinClass, %WinTitle%
    if !(WinClass == "XLMAIN")
        return "Window class mismatch."
    ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
    if (ErrorLevel)
        return "Error accessing the control hWnd."
    VarSetCapacity(IID_IDispatch, 16)
    NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
    if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc) != 0
        return "Error calling AccessibleObjectFromWindow."
    window := ComObject(9, pacc, 1)
    if ComObjType(window) != 9
        return "Error wrapping the window object."
    Loop
        try return window.Application
        catch e
            if SubStr(e.message, 1, 10) = "0x80010001"
                ControlSend, Excel7%Excel7#%, {Esc}, %WinTitle%
            else
                return "Error accessing the application object."
}

[FanaticGuru]

I need to create 3 shortcut keys for copying the selected text or data from browser and paste it in excel ,
there are 3 columns in excel , and three shortcut keys , if i press the first shortcut key the selected data should be pasted in the excel 1 column and if
i press the 2 shortcut key the text should be pasted in the 2 column like wise for 3 shortcut keys and after pasting the text when i do this action again the excel cell should automatically move to the new row and paste the text .

Someone asked for almost the exact same thing here:
https://autohotkey.com/boards/viewtopic.php?f=5&t=44946

Here is the solution that was provided:

Code: Select all

1::
2::
3::
	Column := A_ThisHotkey + 0	; force this to be treated as a number and not a string otherwise Excel will think you are looking for a column named the literal string 1
	Clipboard := ""
	SendInput ^c
	ClipWait,1	; wait up to 1 second for text to appear on clipboard
	if ErrorLevel	; something went wrong, abort
		return
	xlApp := ComObjActive("Excel.Application")
	xlCell_EmptyBelowData := xlApp.Columns(Column).Find("*",,,,,2).Offset(1,0)	; get cell just below last cell with anything in it
	if  !xlCell_EmptyBelowData.Address	; no cell found with anything in it, column is blank
		xlCell_EmptyBelowData := xlApp.Cells(1,Column)	; default to first cell
	xlCell_EmptyBelowData.Value := Clipboard
return

You push 1, 2, or 3 and what is currently selected will be copies to the first empty cell in columns A, B, or C.

FG

[Immanuel]

@awel20 , @FanaticGuru ! Guys thanks a lot , u guys r really amazing ! i have been breaking my head with this syntax prob so long , now its working perfectly . thank you !

and i have a doubt is it possible to get the specified data directly without selecting from web page source code ?
in detail there is web page in that i am going to select title , date , name , isbn and and paste it in excel .
without selecting is it possible to take the data from web-page directly or from the source code of the webpage ?

[Blackholyman]

yes it is, but without knowing almost exactly what and from where its hard to give examples that you can use

note: when using ClipWait remember to clear out the clipboard before, else it will not work correctly

[FanaticGuru]

yes it is, but without knowing almost exactly what and from where its hard to give examples that you can use

note: when using ClipWait remember to clear out the clipboard before, else it will not work correctly

Good advice on both points. (Editted my post to clear the clipboard)

You can do really great things with getting information from a website through COM with IE but it is very specific to the website. Websites' HTML code typically has class and id info that can be used to get the text from certain controls.

It is hard for others to get these names to write the code for you.

FG

[Immanuel]

hi FG,

this is the web-page (www.amazon.in/dp/B07HLKV538/)
i need to get the title , date , Asin , author-name .
You have helped me to get these by selecting the text and pasting it in excel but if it can be done directly without selecting it save lots of time .

[FanaticGuru]

hi FG,

this is the web-page (http://www.amazon.in/dp/B07HLKV538/)
i need to get the title , date , Asin , author-name .
You have helped me to get these by selecting the text and pasting it in excel but if it can be done directly without selecting it save lots of time .

Here is an example of using COM with IE to then access the DOM structure of the HTML. (Lots of abbreviations!)

Code: Select all

F12::
	For IE in ComObjCreate("Shell.Application").Windows ; for each open window
		If InStr(IE.FullName, "iexplore.exe") ; check if it's an ie window
			break ; keep that window's handle
	; this assumes an ie window is available. it won't work if not
	
	MsgBox % IE.Document.getElementsByClassName("contributorNameID")[0].innerHTML
	MsgBox % IE.Document.getElementByID("ebooksProductTitle").innerHTML
	ProductDetails := IE.Document.getElementsByClassName("bucket")[0].innerHTML
	RegExMatch(ProductDetails, "ASIN=(.*?)&", M)
	MsgBox % M1
return

You have to have the webpage open in Internet Explorer. Not Edge, not Chrome, or any other web browser. This only works with Internet Explorer and hopefully a newer version like 11.

The Author is pretty easy it has a nice Class name to find it. The Title also is pretty easy as it has an ID to find it. The ASIN is a little harder. I only saw it in the Product Details section and that is handed as one block of text in the HTML. So I used some RegEx magic to pull just the ASIN out of the block of text that is the Product Details.

I was unsure what was needed for date. There is a date in () after the publisher in the Product Details. RegEx could be used to get this also if it is consistently after the publisher name like this.

Also this HTML is dynamically generated so no guarantees this will work perfectly for all books on Amazon but with some experimenting could probably get something pretty reliable.

Could design it so that IE navigated to the page or the HTML could be pulled from the website by WinHttp without using IE.

With a little work could probably get it so you could pull the info for a hundred books from Amazon and create an Excel file entirely in the background.

FG

[Immanuel]

hi FG,

Thanks for the effort ! u r genius ! but i cant use internet explore for many reason !
if u get any solution for using this in Mozilla kindly let me know .
once again thanks a lot for your help and patience .

[FanaticGuru]

Thanks for the effort ! u r genius ! but i cant use internet explore for many reason !
if u get any solution for using this in Mozilla kindly let me know .

I know of no Mozilla automation simliar to what you can do with IE.

The functionality of IE can be used programmically to access the same information without the user ever seeing IE assuming IE is installed on the computer which it is in a normal Windows installation.

For example this script will get the author of a book at the provided Amazon URL.

Code: Select all

URL = http://www.amazon.in/dp/B07HLKV538

WB := ComObjCreate("InternetExplorer.Application")
WB.Navigate(URL)
While WB.ReadyState != 4 || WB.document.ReadyState != "complete" || WB.Busy ; wait for the page to load
   Sleep, 10
MsgBox % WB.Document.getElementsByClassName("contributorNameID")[0].innerHTML

I don't use IE either for my normal web browsing but I still use it for automation like above.

Something simliar could be done with WinHTTP without using any browser.

This function will get the HTML of a URL which then could be used to create a DOM object and then extract information.

Code: Select all

Get_HTML(Http)
{
	if !whr
		whr := ComObjCreate("WinHttp.WinHttpRequest.5.1")
	whr.Open("GET", Http, true)
	whr.Send()
	whr.WaitForResponse()
	return whr.ResponseText
}

The returned string with be all the HTML code returned by the URL. Even if you don't want to use a DOM object, you could use RegExMatch to extract the info from the HTML code.

FG

[Immanuel]

Hi FG ,

Thanks for showing this logic ! was very helpful !
with your example i am learning new ways .

[Immanuel]

Hi FG,

I need a help , in the below code there is an error when i press 1 it paste the data in first row when i press 2 it paste in the second row ! .
this happens when i added a new module to bring the cell to the first column empty row . if i remove this module its working fine .
and i need a example code to paste a text " create new " when i press ctrl + s in column J .

Thank you

1::
2::
3::
4::
5::
; Copy
Clipboard := ""
Send, ^c
ClipWait, 1
if (ErrorLevel != 0)
{
MsgBox, 48, Copy, Nothing was copied.
return
}
; Get the active Excel app. Excel needs to be running.
ExcelApp := Excel_Get()
if !IsObject(ExcelApp)
{
MsgBox, 48, Excel, % "Can't find Excel.`n" ExcelApp
return
}
; Get the column to use
if (A_ThisHotkey = "1")
Col := 3
else if (A_ThisHotkey = "2")
Col := 4
else if (A_ThisHotkey = "3")
Col := 5
else if (A_ThisHotkey = "4")
Col := 6
else if (A_ThisHotkey = "5")
Col := 10

ExcelApp.ActiveCell.EntireRow.Cells(Col).Value :=
; Select the next row
if (A_ThisHotkey = "5")
ExcelApp.ActiveCell.Offset(1, 0).Select
^a::
ThisSheet := xlApp.ActiveSheet
CellsCount := xlApp.Rows.Count
LastRow := 1
Loop, 3 {
ThisRow := ThisSheet.Cells(CellsCount, A_Index).End(-4162).Row
if (ThisRow > LastRow)
LastRow := ThisRow
}
ThisSheet.Cells(LastRow + 1, 3).Select
return

[FanaticGuru]

Hi FG,

I need a help , in the below code there is an error when i press 1 it paste the data in first row when i press 2 it paste in the second row ! .
this happens when i added a new module to bring the cell to the first column empty row . if i remove this module its working fine .
and i need a example code to paste a text " create new " when i press ctrl + s in column J .

Thank you

1::
2::
3::
4::
5::
; Copy
Clipboard := ""
Send, ^c
ClipWait, 1
if (ErrorLevel != 0)
{
MsgBox, 48, Copy, Nothing was copied.
return
}
; Get the active Excel app. Excel needs to be running.
ExcelApp := Excel_Get()
if !IsObject(ExcelApp)
{
MsgBox, 48, Excel, % "Can't find Excel.`n" ExcelApp
return
}
; Get the column to use
if (A_ThisHotkey = "1")
Col := 3
else if (A_ThisHotkey = "2")
Col := 4
else if (A_ThisHotkey = "3")
Col := 5
else if (A_ThisHotkey = "4")
Col := 6
else if (A_ThisHotkey = "5")
Col := 10

ExcelApp.ActiveCell.EntireRow.Cells(Col).Value :=
; Select the next row
if (A_ThisHotkey = "5")
ExcelApp.ActiveCell.Offset(1, 0).Select
^a::
ThisSheet := xlApp.ActiveSheet
CellsCount := xlApp.Rows.Count
LastRow := 1
Loop, 3 {
ThisRow := ThisSheet.Cells(CellsCount, A_Index).End(-4162).Row
if (ThisRow > LastRow)
LastRow := ThisRow
}
ThisSheet.Cells(LastRow + 1, 3).Select
return

You have merged two people's code that used different variable names.

I don't really know what you want but this code might help:

Code: Select all

1::
2::
3::
4::
5::
  if (A_ThisHotkey = "1")
    Column := 3
  else if (A_ThisHotkey = "2")
    Column := 4
  else if (A_ThisHotkey = "3")
    Column := 5
  else if (A_ThisHotkey = "4")
    Column := 6
  else if (A_ThisHotkey = "5")
    Column := 10	
  Clipboard := ""
  SendInput ^c
  ClipWait,1	; wait up to 1 second for text to appear on clipboard
  if ErrorLevel	; something went wrong, abort
    return
  xlApp := ComObjActive("Excel.Application")
  xlCell_EmptyBelowData := xlApp.Columns(Column).Find("*",,,,,2).Offset(1,0)	; get cell just below last cell with anything in it
  if  !xlCell_EmptyBelowData.Address	; no cell found with anything in it, column is blank
    xlCell_EmptyBelowData := xlApp.Cells(1,Column)	; default to first cell
  xlCell_EmptyBelowData.Value := Clipboard
return

This allows when you press 1,2,3,4, or 5 then the clipboard is copied to the first empty cell in columns 3,4,5,6, or 10. The hotkey and corrisponding columns could easily be changed.

FG