Sign in to follow this  
Followers 0
Feyd

Background processing

3 posts in this topic

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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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

That worked great thanks!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0