Spreadsheet to Auto Entry

readme.md

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.

sendkeys2.vbs

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