Background processing

[Feyd]

I've written the following script to do a simple cut and paste between two programs. The source file contains hundreds of cells so it would be nice if I could suppress the windows so I don't have to watch the process. Also is there way do other work without interfering with the process and vice versa? Any pointers on how to speed it up or make the script more efficient would be appreciated. Thanks in advance.

CODE

$pausetime = 15000

;Open company customized electronic form document to receive data

$message = "Select EForms template to use.";

$var1 = FileOpenDialog($message, @WorkingDir, "Eform Templates (*.ifm)", 1 + 4 )

If @error Then

MsgBox(4096,"","No File(s) chosen")

EndIf

ShellExecute($var1,"",$var1,"open")

Sleep($pausetime)

$title1 = WinGetHandle("", "")

; Open Excel data source file

$message = "Hold down Ctrl or Shift to choose multiple files."

$var2 = FileOpenDialog($message, @WorkingDir, "Excel (*.xls;*.xl;*.xlt)", 1 + 4 )

If @error Then

MsgBox(4096,"","No File(s) chosen")

Else

ShellExecute($var2,"",$var2,"open")

;sleep($pausetime/2)

WinWait("Security Warning", "")

Send("!E")

sleep($pausetime/10)

$title2 = WinGetHandle("", "")

EndIf

;Switch back to empty eform and set cursor position for first cut and paste

WinActivate($title1)

Send("{TAB 7}")

sleep($pausetime/100)

$data1 = ""

;Cycle through Excel data source cells by running a macro which also copies the cell data to the clipboard.

;Clipboard data pasted into eform and the cursor is advanced to the next eform "cell."

While 1

WinActivate($title2)

WinWaitActive($title2)

send("^q")

WinActivate($title1)

WinWaitActive($title1)

$data1 = ClipGet()

if $data1 = "END" Then

Exit

Else

$data1 = StringReplace($data1, @CRLF, "")

send($data1 & "{TAB}" )

EndIf

WEnd

Excel VBA code for macro:

CODE

Option Explicit

Dim RngFormData As Range

Sub RangeSet()

Dim i As Range, j As Range, test As Boolean

Dim x As Double, y As Double, nNames As Name

For Each nNames In ActiveWorkbook.Names

If nNames.Name = "FormData" Then test = True

Next

If test Then

Set RngFormData = Range("FormData")

Set i = Range("counter1")

Set j = Range("counter2")

Else

x = WorksheetFunction.CountA(Columns("a:a")) - 1

y = WorksheetFunction.CountA(Rows("1:1")) - 1

Set RngFormData = Range("a2", Range("a1").Offset(x, y))

RngFormData.Name = "FormData"

Set i = RngFormData(1).Offset(RngFormData.Rows.Count + 2, 1)

i.Name = "counter1"

i(1).Value = 1

Set j = RngFormData(1).Offset(RngFormData.Rows.Count + 3, 1)

j.Name = "counter2"

j(1).Value = 1

End If

If j(1).Value = RngFormData.Rows.Count + 1 Then

i(1).Value = "END"

i(1).Copy

j(1).Value = Empty

i(1).Value = Empty

ActiveWorkbook.Names("counter1").Delete

ActiveWorkbook.Names("counter2").Delete

ActiveWorkbook.Names("FormData").Delete

End

Else

RngFormData(j(1).Value, i(1).Value).Copy

If i(1).Value = RngFormData.Columns.Count Then

j(1).Value = j(1).Value + 1

i(1).Value = 1

Else

i(1).Value = i(1).Value + 1

End If

End If

End Sub

[qazwsx]

you could use control send rather than send and you could set the window states to hide.

[Feyd]

you could use control send rather than send and you could set the window states to hide.

That worked great thanks!