Paste clipboard into Excel as new row

[srousskikh]

Hi everyone!

 

I have a list of URLs to be copied into an active (open) Excel workbook each as new row in column A. I was able to attach to the workbook and get number of rows as follow:

#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $sWorkbook = "d:\DOCUMENTS\Surveys\_surveys_paneldirector.xlsx"
Local $oExcel = _Excel_Open()
If @error Then Exit
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then
    _Excel_Close($oExcel)
    Exit
EndIf

; Attach to the first Workbook where the file path matches
$oWorkbook = _Excel_BookAttach($sWorkbook)
If @error Then Exit

$oRange = $oExcel.ActiveSheet.Range("A:A")
MsgBox(4096, "Excel Count Non-Blank Cells", $oExcel.WorksheetFunction.CountA($oRange))

Will you please help me with the remaining part of the code (to paste each URL from clipboard into new Excel row)?

 

[srousskikh]

I copy URLs manually one by one, not in bulk

[srousskikh]

I have also found the code to paste clipboard into fixed cell:

$oExcel.Application.ActiveSheet.Range("A1").Select
$oExcel.Selection.Paste

How to paste it as new row?

[JockoDundee]

Global $iNextNewRow = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1

$oExcel.Application.ActiveSheet.Range("A" & $iNextNewRow).Select
$oExcel.Selection.Paste

 

[srousskikh]

Many thanks for quick reply!

Now it selects new row in A column but throws error "The requested action with object has failed". There is a plain text in the clipboard, Excel from Office 365. 

[water]

You could use the Excel UDF that comes with AutoIt.
You could either use _Excel_RangeCopyPaste to write the content of the clipboard to cell or write all URL to an array and use _Excel_RangeWrite to write all URLs in one go to tjhe workbook.

[srousskikh]

18 minutes ago, water said:

You could use the Excel UDF that comes with AutoIt.
You could either use _Excel_RangeCopyPaste to write the content of the clipboard to cell or write all URL to an array and use _Excel_RangeWrite to write all URLs in one go to tjhe workbook.

Many thanks! I have already tried to paste it into fixed cell as follows:

_Excel_RangeCopyPaste($oExcel.ActiveSheet, Default, "A41")

It selects the cell but doesn't actually paste the text form clipboard

[srousskikh]

42 minutes ago, JockoDundee said:

Global $iNextNewRow = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1

$oExcel.Application.ActiveSheet.Range("A" & $iNextNewRow).Select
$oExcel.Selection.Paste

 

Many thanks! First two lines work well and the scripts selects new row. And this:

$oExcel.Selection.Paste

throws the error mentioned above. Just unable to paste from the clipboard for some reason

[srousskikh]

Thanks everyone!

I have made it work this way:

Global $iNextNewRow = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1
$oExcel.ActiveSheet.Range("A" & $iNextNewRow).Select
$oExcel.ActiveSheet.Paste

 

[water]