Keystrokes for website that does not allow paste

[energywatch]

Hello,

Any help to speed up my work with a very outdated website is very appreciated!

I currently am manually typing thousands of data inputs out to 6 decimals into an EXTREMELY outdated system that I have no control over.

Each project requires 10 inputs into the form. You can not tab, scroll, etc between the fields and you can not paste anything, everything must be typed with keystrokes. (crazy I know, I'm going nuts!)

I would like to build VBA that uses data in my excel sheet and then inputs it using keystrokes into the form. I dont know how to build the vba. Would this be done with SendKeys? I have never used that before. My thoughts are the macro could go two ways:

Faster, more complex code?
1. Copy each of the projects 10 inputs from excel as separate values (myinput1, myinput2, etc) then when I manually navigate to the site and click in 'box1' with my curser be able to have the macro "type" myinput1 for me (I could use some sort of quick key to reference input1, 2, etc.). Then I manually click on 'box2' and it types myinput2 for me. And the process continues until I reach the 10th box. I will then manually save.

It's still horribly manual, but the software is likely from 1973, or earlier, and does not seam to have a way around it for us data input minions.

Slower but simpler code?
2. Have a simple code that copies one cell value at a time, I manually place my cursor into the box on the software site, then Ctl+K does a keystroke of the value I just copied.

Example of my excel data is below


[TABLE="width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]Input 1[/TD]
[TD]Input 2[/TD]
[TD]Input 3[/TD]
[TD]Input 4[/TD]
[TD]Input 5[/TD]
[TD]Input 6[/TD]
[TD]Input 7[/TD]
[TD]Input 8[/TD]
[TD]Input 9[/TD]
[TD]Input 10[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD="align: right"]1.233643[/TD]
[TD="align: right"]1.236443[/TD]
[TD="align: right"]1.233650[/TD]
[TD="align: right"]1.333643[/TD]
[TD="align: right"]1.233644[/TD]
[TD="align: right"]12.233643[/TD]
[TD="align: right"]740.233643[/TD]
[TD="align: right"]65.233643[/TD]
[TD="align: right"]647.299643[/TD]
[TD="align: right"]1.239843[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]1.236543[/TD]
[TD="align: right"]3.236443[/TD]
[TD="align: right"]1.783650[/TD]
[TD="align: right"]561.333643[/TD]
[TD="align: right"]87.233644[/TD]
[TD="align: right"]0.000000[/TD]
[TD="align: right"]790.233003[/TD]
[TD="align: right"]796.236670[/TD]
[TD="align: right"]89.657891[/TD]
[TD="align: right"]0.000000[/TD]
[/TR]
</tbody>[/TABLE]


Any help is much appreciated, this process takes HOURS each month. Its crazy how bad the software is, but I have no control over that part. I have to work within the system I have and try and make it faster.

*** I also have a second system which does allow pasting, but has the same navigation issues. If someone has a quick logic for saving 10 pieces of data at once, then pasting them. It would be copy, click, paste1, click paste2, click paste 3, .....

Look forward to feedback.

 

[DRSteele]

I can't answer your question outright. It would be useful if you'd tell us which website your question involves.

But it strikes me that PowerQuery might be a useful tool in grabbing data from a website. Watch this youtube video from ExcelIsFun and then let us know if you think it might help.
https://www.youtube.com/watch?v=sEw6ZdcGOU4&list=PLrRPvpgDmw0mSJCZaqQPFj0eto4qnzkCZ&index=35

 

[John_w]

Search for "IE automation Excel VBA". Example results:

IE (Internet Explorer) Automation using Excel VBA - Excel VBA Templates
https://www.youtube.com/watch?v=q7aa76YFFW8

 

[energywatch]

Search for "IE automation Excel VBA". Example results:

IE (Internet Explorer) Automation using Excel VBA - Excel VBA Templates
https://www.youtube.com/watch?v=q7aa76YFFW8

John Thank you for your reply. This is not an internet explorer site. It is an intranet site for our company, so there is not way to reference a web location. Its simply clicking into boxes on the screen.

Click and Paste,
Click and Paste,
Click and Paste....no tab, no navigation functions capable.


DRSteele, this is not a website you would be able to visit. It is a company software interface, not a public domain website. Also, I am not getting data from the interface. I am submiting data to it. Excel --> Company Form. Its basically a webform from 1970. No tab, no navigation. I need to click in the form boxes and paste.

What I need help with is pulling the data in the correct form from excel, storing it and then pasting where I click.

So far all I have is selecting the data in B3 and then trying to copy/store it prior to paste.

The F2 and Ctl+Shift+Home Send Keys work but I have to manually strike ctl+c. When I add the SendKey to copy, everything breaks.

Sub Send_Keys_CtlA()
'Select Cell
Range("B3").Select
'Press F2
Application.SendKeys "{F2}", True
'Press Ctl+Shift+Home
Application.SendKeys "^+{Home}", True

'Copy (copy the same way copies with keystrokes?
'Application.SendKeys "^c", True
End Sub


An additional limitation to the site is that the only way to paste is if you copy the content of the cell in the formula bar, not the cell itself.

Once I get B3 working completely I would develop a more complex way to reference each input, store it and paste on command.

 

[DRSteele]

I misunderstood. 'Tis way out of my realm, I fear. Best of luck!

 

[John_w]

It is an intranet site for our company, so there is not way to reference a web location.

IE automation works with internet and intranet sites, i.e. any http:// or https:// address.

Its basically a webform from 1970.

So, is yours an intranet site, hosted in a browser?

Alternatively, you could try code which uses Windows API functions such as FindWindow and SendMessageByString, to locate and populate individual fields (windows) in the form. There is example code at AppActivate losing focus which writes text to a Notepad window, which you will need to modify for your situation. However, it may be that your 'webform' is a single window and any code is unable to reference individual fields (windows) within it. You will need a Windows 'spy' tool to see if your 'webform' contains windows - see winapi - Spy++ alternative that can send messages - Stack Overflow for several tools; the AutoIt tool mentioned might be a complete solution, and I think its objects can be called from Excel VBA.