Excel macro raising errors within VBSTART/END Block

[sn]

Hi,

Scenario:
There are two Excel files, say F1 & F2, both open. Need to sort data in F2, identify if there are any records that meet a certain criteria, and if there are, then to copy those records and paste them to F1.

This works well within a macro within a third Excel but I'm trying to do this from within Macro Scheduler Pro - trial version.

I've included the Excel Macro within VBSTART/VBEND blcok at the top of my MSPro macro but I keep getting errors when trying to run it.

Any hints what might be causing this? Any pointers as to what's acceptable within VBSTART/VBEND block?

Thanks,
sn

Code: Select all

MSPro CODE: =================================

VBSTART

Sub SortDataByPMS(wb1, wb2)

Dim xlWb1
Dim xlWb2
Dim r
Dim maxR
Dim maxCol

    Set xlWb1 = Excel.Application.Workbooks(wb1)
    Set xlWb2 = Excel.Application.Workbooks(wb2)

    ' =============================
    xlWb2.Worksheets(1).Activate    ' With ThisWorkbook.Worksheets(1)

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    maxCol = Selection.Columns.Count
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

    Range("A65536").Select
    ActiveCell.End(xlUp).Select

    maxR = ActiveCell.Row

    r = 2
    Range("A" & r).Select
    Do While ActiveCell = "4" Or ActiveCell = "7"
        ActiveCell.Offset(1, 0).Select
    Loop

    r = ActiveCell.Row
    If r <maxR>winlist
separate>winlist,CRLF,windows
' MessageModal>windows_count
let>maxLoop=windows_count-1
Let>i=0
let>k=1
repeat>k
let>this=windows_%k%
MidStr>this,1,6,strSub
if>strSub=POItem
    ' MessageModal>this
	let>xl[%i%]=this
	GetWindowHandle>%this%,h
	let>wh[%i%]=h
	let>i=i+1
	' MessageModal>xl[%i%]
endif
Let>k=k+1
Until>k>maxLoop
' MessageModal>i

if>i=2
	let>f1=xl[0]
	let>f2=xl[1]
	let>fh1=wh[0]
	let>fh2=wh[1]
	midstr>f1,8,1,f1n
	midstr>f2,8,1,f2n
	if>f1n>f2n
		let>ftmp=f1
		let>f1=f2
		let>f2=ftmp
		let>fhtmp=fh1
		let>fh1=fh2
		let>fh2=fhtmp
	endif
    MessageModal>f1
	MessageModal>fh1
    MessageModal>f2
	MessageModal>fh2
endif

Let>WIN_USEHANDLE=0
SetFocus>%f1%
wait>1
WindowAction>2,%f1%
wait>1
WindowAction>1,%f1%
GetActiveWindow>awt,x,y,x1,y1
MessageModal>awt

VBRun>SortDataByPMS,%f1%,%f2%

[Marcus Tettmar]

You'll get an error at the first line because Excel.Application is meaningless.

A workbook is an object of an Excel application. But you have not defined what an Excel application is. You need to use the CreateObject function.

Immediately you'll also get an error at this line:

Range("A1").Select

Range has no context here. Macro Scheduler has no idea what Range means.

Range is an object belonging to a worksheet, which is an object belonging to a workbook, which is an object belonging to an Excel app.

You need to create the objects. When running the code inside Excel those objects already exists. It is in context. Outside of Excel "Range" is meaningless. You need to create the Range object. But first you need to create the Excel.Application object.

Please see the examples here in the forum. There is also an example that comes with the software. Search for CreateObject("Excel.Application") to get you started. And look at the sample Excel macro that comes with Macro Scheduler.