How to create script to copy a cell from Excel and paste into a text box located on a browzer.

Question

At work we have to track our calls via an online application made via vb.net. To enter the needed info into the text boxes and drop down boxes you have to deal with more than one page. It is cumbersome to have to type or even copy and paste the needed info.

I want to be able to type the info onto one page in excel and then have a script be able to copy and paste into the text boxes located on the browser screen automatically, without me having to do the copying and pasting.

Is this possible? Can anyone point me in the right direction?

I noticed when you try to create an email account they always have that one box where you have to type the squiggly letters into. I assume this is because there are apps that can automatically paste or write to a text box located on a browzer screen.

Any help to point me in the right direction would be great.

Answer 1

As far as I can see, what you want is some 3rd party script or a VBA script in the Excel worksheet to do the copy/pasting for you.

What I can tell you will not work, is to try and have the browser do the work. There's a lot of restrictions placed on what a browser can and cannot do, and they mostly involve preventing it from accessing data on your computer. This includes copying from Excel spreadsheets.

What I think you may be able to do, is to build a very very rigid Windows Shell script. Using the SendKeys function, you can execute a set of key-presses exactly as if you had done them yourself.

You could have the script do something like this:

1. Activate the Excel's window
2. Move to the 1st cell you wish to copy. (By using the arrow keys.)
3. Copy it. (By using CTRL-INSERT)
4. Activate the VB.NET window
5. Move to the place you insert the data from the 1st cell. (Using TAB over and over.)
6. Paste it. (By using SHIFT-INSERT)

I must warn you, that such a script will fail if anything is out of place - the current cursor position is wrong, the text of each of the windows' has changed, etc.

Here's an example:

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.AppActivate "the-exact-text-in-the-titlebar-of-Excel"
WScript.Sleep 50
WshShell.SendKeys "{RIGHT}"
WshShell.SendKeys "{RIGHT}"
WshShell.SendKeys "{DOWN}"
WshShell.SendKeys "^{INSERT}"
WshShell.AppActivate "the-exact-text-in-the-titlebar-of-vb.net-browser-window"
WScript.Sleep 50
WshShell.SendKeys "{TAB}"
WshShell.SendKeys "{TAB}"
WshShell.SendKeys "{TAB}"
WshShell.SendKeys "+{INSERT}"

The example assumes:

1. The cell to copy from Excel is in the 2rd column to the right of the current cursor position in Excel, and one row down.
2. The place to paste in the vb form is the 3th control from the current cursor position in the browser.

Answer 2

I can't think of any particular way other than @scraimer's SendKeys to do exactly what you asked for. Take his warnings to heart, SendKeys is very tricky to do reliably.

Another possibility to get the same end result is to change your .aspx to accept GET variables and pre-populate its fields on PageLoad() you might have 3 variables that populate the name, company and message;

http://www.mysite.com/myform.aspx?var1=JoeBolgs&var2=Company+XYZ&var3=Help+me+please

Then in VBA create a form or sheet that a user can fill out var1 -> var3

// obviously make your var names nicer than mine :)
myUrl = "http://www.mysite.com/myform.aspx?"
myUrl = myUrl & "var1="  & strVar1
myUrl = myUrl & "&var2=" & strVar2
myUrl = myUrl & "&var3=" & strVar3

OpenBrowserURL(myUrl)

and have a OpenBrowserURL() routine

Sub OpenBrowserURL(strURL As String)
  ActiveWorkbook.FollowHyperlink Address:=strURL, NewWindow:=True
End Sub

I use something similar for help pages called via a menu and it works a treat. A user clicks a menu and VBA opens a url with some data in the user's favourite browser.

The only other way i've interacted with a website from VBA programatically is by making calls to wininet.dll directly for GET and POST data. Though don't think it would help in your question.

One last thought, if you want to stop people gaming your site somehow, your should encrypt your vars. It's not to hard to encrypt then UUEncode the data and send that as a request variable. Then just URLdecode it and decrypt it on the server using the same algorythm you used to encrypt it.

Good luck.

Answer 3

join two excel sheets + a excel cell change + change another sheet in vb.net with source