Skip to content

Instantly share code, notes, and snippets.

@tatarize
Last active April 10, 2023 21:17
Show Gist options
  • Save tatarize/37f664e0240bc1548ad3a43c7acc2021 to your computer and use it in GitHub Desktop.
Save tatarize/37f664e0240bc1548ad3a43c7acc2021 to your computer and use it in GitHub Desktop.
Spreadsheet to Auto Entry

Spreadsheet to Auto Entry

This script should allow you to enter data in a custom ordering with special keypresses and allow some customization and development on-the-fly. Each line you will be prompted to give the desired ordering for the sendkeys, if you edit it. It will reflect this for the next attempt. When you have a working ordering for the keystrokes, you can rename the script file to that ordering name and it will be used as the default ordering.

The replacement variables $1 - $n are presented for each line.

Instructions

  1. Open the spreadsheet containing the data you want to enter into the program.
  2. Save the spreadsheet data in a Tab Delimited format.
  3. Create a new VBScript file and copy the code into it.
  4. Save the VBScript file with a name that reflects the desired SendKeys ordering. For example, if you want the SendKeys ordering to be "{wait 500}$1{tab}$2{tab}$3{tab}$4{enter}", save the VBScript file as "{wait 500}$1{tab}$2{tab}$3{tab}$4{enter}.vbs".
  5. Run the VBScript file by dropping the text file on it.
  6. When prompted, enter the desired SendKeys ordering. The current values of the cells in the current row will be displayed to help you create the ordering.
  7. Click OK to send the first line in the desired ordering. Each time you click OK, the next line will be sent in the same ordering until all lines have been processed. If you need to stop the process at any time, press Cancel in the prompt window.
  8. One line will be sent at a time. You can update the ordering for each line that is sent until you get the correct formatting. Then rename the script with that final ordering.

Why use this script?

This script is useful for anyone who needs to enter data into a program that does not allow you to paste across fields. For example, if you have a spreadsheet of data and you need to enter it into a web form, this script can help automate the process and save you time.

Customization

Ordering

You can set the order in which the data from the spreadsheet is entered into the target program by changing the name of the script. For example, if you want the script to enter data in the order of column 1, column 3, column 2, and column 4, rename the script to {wait 500}$1{tab}$3{tab}$2{tab}$4{enter}.vbs. If you have multiple processes with different orderings, you can make copies of the script and name them accordingly.

Delays

You can use the {wait n} command to add a delay of n milliseconds between key presses. This can be useful if the target program is slow to respond or if you need to allow time for certain actions to complete before proceeding. It's important to note that this script is designed specifically for automating data entry using the SendKeys function in VBScript. As such, it may not be suitable for all data entry tasks, and may require some modification to work with different programs or data formats.

What are some advantages of using a custom ordering?

By setting a custom ordering for your data, you can optimize the data entry process for your specific needs. For example, you might order the fields based on how frequently they are used, or you might group related fields together to make the process more intuitive.

It's important to note that this script is designed specifically for automating data entry using the SendKeys function in VBScript. As such, it may not be suitable for all data entry tasks, and may require some modification to work with different programs or data formats.

Limitations

In addition, the use of the SendKeys function can sometimes be unreliable or unpredictable, particularly with more complex programs or data structures. As with any automation tool, it's important to thoroughly test the script and ensure that it is working as intended before relying on it for important or sensitive data.

Requirements

This script requires VBScript, which is included with most versions of Windows. It also requires the Microsoft Scripting Runtime library, which should be installed by default on most Windows systems.

Keys

  • TAB {tab}
  • ENTER {enter}
  • UP ARROW {up}
  • DOWN ARROW {down}
  • LEFT ARROW {left}
  • RIGHT ARROW {right}
  • BACKSPACE {backspace}
  • DELETE {delete}
  • HOME {home}
  • END {end}
  • INSERT {insert} or {ins}
  • CAPS LOCK {capslock}
  • NUM LOCK {numlock}
  • PAGE UP {pgup}
  • PAGE DOWN {pgdn}
  • ESC {esc}
  • F1 through F16 {f1} through {f16}
  • KEYPAD ADD {add}
  • KEYPAD SUBTRACT {subtract}
  • KEYPAD MULTIPLY {multiply}
  • KEYPAD DIVIDE {divide}

Special

  • Wait {wait n}: Pauses keyboard input for n MS. 1000 = 1 second.
Option Explicit
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim objArgs
Set objArgs = WScript.Arguments
' Check if a file has been dropped onto the script
If objArgs.Count = 0 Then
MsgBox "No file was dropped onto the script. Please drag and drop the tab delmited file onto the script to proceed.", vbCritical, "Error"
WScript.Quit
End If
Dim order
If Left(fso.GetBaseName(WScript.ScriptFullName), 1) = "{" Then
order = fso.GetBaseName(WScript.ScriptFullName)
Else
order = "{wait 500}$1{tab}$2{tab}$3{tab}$4{enter}"
End If
Dim objFile
Set objFile = fso.OpenTextFile(objArgs(0), 1)
Dim line
Do Until objFile.AtEndOfStream
line = objFile.ReadLine()
Dim items
items = Split(line, vbTab)
order = InputBox("SendKeys Ordering:" & vbCrLf & vbCrLf & Join(getItemLines(items), vbCrLf), "SendKeys", order)
If order = "" Then ' Check if cancel was pressed
WScript.Quit
End If
Dim edited_order
edited_order = order
Dim i
For i = 1 To UBound(items) + 1
edited_order = Replace(edited_order, "$" & i, items(i - 1))
Next
edited_order = Replace(edited_order, "~", "{~}")
edited_order = Replace(edited_order, "!", "{!}")
edited_order = Replace(edited_order, "^", "{^}")
edited_order = Replace(edited_order, "+", "{+}")
edited_order = Replace(edited_order, "%", "{%}")
Dim parts
parts = Split(edited_order, "{wait ")
Dim iPart
For iPart = 0 To UBound(parts)
Dim part
part = parts(iPart)
If iPart > 0 Then
Dim waitTime
waitTime = Split(part, "}")(0)
WScript.Sleep CLng(waitTime)
part = Replace(part, waitTime & "}", "")
End If
If Len(part) > 0 Then
CreateObject("WScript.Shell").SendKeys part
End If
Next
Loop
objFile.Close
Function getItemLines(items)
Dim i, itemLines()
ReDim itemLines(UBound(items))
For i = 0 To UBound(items)
itemLines(i) = "$" & i + 1 & " = " & items(i)
Next
getItemLines = itemLines
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment