Use powershell clipboard to copy rows from excel in browser

Question

I hope you can help me with my PowerShell problem. I want to use the Set-Clipboaed and Get-Clipboard commands from PowerShell to copy specific rows from my excel file in two different textfields in a specific browser site.

The problem is that I always get the content copied from the rows in my excel file, only in the first textfield. For example: The content of row A1 is "Hello" and the content from row A2 is "World". If I copy the rows in the textfields of the browser site, both Strings "Hello" "World" will be displayed only in textfield1. My goal is to have the string "Hello" in textfield1 and the string "World" in textfield2 and later to have a command to use keys on my keyboard to paste the content like in KeePass with the credentials.

Here's what I've done so far. I used a little help from the site https://lazywinadmin.com/2014/03/powershell-read-excel-file-using-com.html and tried to split both rows in two different strings and then tried to copy both string with "Get-Clipboard" in the browser site.

    #Specify the path of the excel file
$FilePath = "PathToMyExcelFile\Test-Excel-Auto2.xlsx"

#Specify the Sheet name
$SheetName = "table1"

# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
#$objExcel = new-object -c excel.application
# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false
# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
# Load the WorkSheet 'BuildSpecs'
$WorkSheet = $WorkBook.sheets.item($SheetName)


Set-Clipboard $WorkSheet.Range("A1").Text
Set-Clipboard $WorkSheet.Range("A2").Text -Append


(Get-Clipboard) -split "'n'"

# Get-Clipboard) -split '\t|\r?\n'

# Get-Clipboard.Split ( "'\t|\r?\n'")


# Set-Clipboard $WorkSheet.Range("A1").Text
# $variable = Get-Clipboard
# Set-Clipboard $WorkSheet.Range("A2").Text -Append
# $variable2 = Get-Clipboard

I miss the part, how to get both strings copied in the two different textfields in my browser site.

Thanks in advance for your help.

MarT22

Although it doesn't use the clipboard have a look at this it may help with your problem: cmdrkeene.com/automating-internet-explorer-with-powershell — RetiredGeek
– RetiredGeek, Commented Jul 7, 2020 at 23:45
Thank you for sharing these URL, you made me curious and I will try it, after I have the solution with Get/Set-Clipboard :). — MarT22
– MarT22, Commented Jul 8, 2020 at 17:47

Robbie Crash · Accepted Answer · 2020-07-08 03:30:39Z

0

Setting $WorkSheet.Range("A2").Text means you're only copying the cell text ("world"). You're then appending the text with what's already in your clipboard ("hello"), which gives you just a concatenated string ("helloworld").

You need to set the clipboard content to a variable and store it for later. Depending on how many different fields you need, you'll either want to set the content into a temporary string or two, or you will want to look into hashtables.

It looks like you were going in the right direction in your commented out code. You'll want to do something like:

$Hello = Set-Clipboard $Worksheet.Range("A1").Text
$World = Set-Clipboard $Worksheet.Range("A2").Text

Then later when you need to paste the content you need to put the variables back into your clipboard, then paste the content.

For example, with something like KeePass you're going to be looking at username/password combos, so what you'd get would be:

PS C:\> $Username = Set-Clipboard $Worksheet.Range("A1").Text # ("Hello")
PS C:\> $Password = Set-Clipboard $Worksheet.Range("A2").Text # ("World")
PS C:\> Set-Clipboard $Username
PS C:\> Get-Clipboard
Hello
PS C:\> Set-Clipboard $Password
PS C:\> Get-Clipboard
World

answered Jul 8, 2020 at 3:30

Robbie Crash

2204 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MarT22 Over a year ago

Thanks for your response and help @RobbieCrash :). The idea with two different variables is great and also makes it easier for me to understand. If I execute my script with your last code block, I get in the output not the String "Hello" or "World". I get nothing as result. Also I still have the problem that Get-Clipboard only saves the last string ("World") and in the browser site only the first texfield will be filled with "World". Do yo have a solution or a tipp for me how to fill both textfields separately, so that Get-Clipboard command can save both variables and display them? Thanks.

Robbie Crash Over a year ago

I'm not sure what you mean. If you have any variable set to something ($i = "F") when you do set-clipboard $i, and then do get-clipboard, the output should be "F". Is this not happening for you? What happens when you just try typing $username?

Collectives™ on Stack Overflow

Use powershell clipboard to copy rows from excel in browser

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related