Reflections and Overflow Error

[JeaShe]

I am a longtime macro developer in extra basic. We are going to move to Reflections in a couple weeks and I am testing all the macros to ensure they will work. I've been fairly successful except for one problem and that is that I get a Extra basic error 55 or 58. The thing is not consistent though. I used the same code two days ago all day and then today I get the error each time I try to run it. I'll send some code. I think I need to somehow strengthen it so it doesn't fail so easily if that makes sense since it's a runtime error. I just can't figure out why it happens sometimes and not others. I know I should do this in Excel but I don't do VBA very well and I can't deploy it as easy as I can the reflections code.
'--------------------------------------------------------------------------------
Macro will move records from No Results to SAME or MORE after performing
' a different macro. If it states returns pulled with no ref or bal due
' and col 44 indicates empty and col23 states "no return in the cell"

'--------------------------------------------------------------------------------

Option Explicit

Dim objExcel as Object, objWorkBook as Object, scrErr as String
Dim xlRow as Integer

Declare Sub GetExcel()

Sub Main()

'-------------------------------------------------------------------------

Call GetExcel()

'-------------------------------------------------------------------------

' This section of code contains the recorded events
Dim xlNRTab, xlSMTab, objSameMoreTxt as String
Dim xl44Col as String , xl42Col, xl23Col as String, xl11Col as String
Dim xl8Col, xlMove

'SET ROW NUMBER
xlNRTab = "2"

xlSMTab = Inputbox ("Enter first available Same or More row number.")
If xlSMTab = "" Then
xlSMTab = "2"
End If

'BEGIN LOOPING THROUGH THE RECORDS
Do

doevents

'CAPTURE DATA IN THESE COLUMNS
xl42Col = objWorkbook.Worksheets("No Results").cells(xlNRTab, 42)
xl23Col = objWorkbook.Worksheets("No Results").cells(xlNRTab, 23 )
xl11Col = objWorkbook.Worksheets("No Results").cells(xlNRTab, 11 )

If (xl23Col = "TransId is not in R200") Then
If xl42col = 0 Then 'COL11 = "SAME"
objWorkbook.Worksheets("No Results").cells(xlNRTab, 11)= "SAME"
xlMove = 1
ElseIf xl42col <0 Then 'COL11 = "MORE"
objWorkbook.Worksheets("No Results").cells(xlNRTab, 11)= "MORE"
xlMove = 1
Elseif xl42col >0 Then 'COL11 = "LESS"
objWorkbook.Worksheets("No Results").cells(xlNRTab, 11)="Less"
xlMove = 1
End If
End If

If xlMove = 1 Then
'GET DATA FOR SAME OR MORE SHEET
objWorkBook.Worksheets("No Results").Select
objWorkBook.Worksheets("No Results").Rows(xlNRTab).Select
objWorkBook.Worksheets("No Results").Rows(xlNRTab).Copy

'PASTE IT IN SAME OR MORE SHEET
objWorkbook.Worksheets("Same or More").Select
objWorkbook.Worksheets("Same or More").Rows(xlSMTab).Select
objWorkBook.ActiveSheet.Paste
xlSMTab = xlSMTab + 1

'REMOVE IT FROM NO RESULTS
objWorkBook.Worksheets("No Results").Select
objWorkBook.Worksheets("No Results").Rows(xlNRTab).Select
objWorkBook.Worksheets("No Results").Rows(xlNRTab).Delete
xlNRTab = xlNRTab - 1
End If

xl42Col = 0
xl8Col = ""
xlMove = 0

doevents

xlNRTab = xlNRTab + 1
xl8Col = objWorkbook.Worksheets("No Results").cells(xlNRTab, 8 )

Loop until xl8Col = ""

msgbox "Macro is done"

End Sub

'--------------------------------------------------------------------------
Sub GetExcel()

Dim objExcel as Object

'START EXCEL
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If objExcel is Nothing Then
Set objExcel = CreateObject("Excel.Application")
If objExcel is Nothing Then
msgBox ("Cannot open Excel.")
Stop
End If
End If

'OPEN SPREADSHEET
Dim fileName as String, ExcelPath as String, nret, r, tmpId as String

'XXXX HAS ALREADY OPENED THE TEMPLATE AND DOWNLOADED DATA. THEN DID
'SAVEAS xxxxxxx Returns1.xlsx TO THEIR DESKTOP.
'OPEN SAME FILE TO FIX 'NO RESULTS'

tmpId = InputBox("Enter your userId, e.g., (A1234)")
ExcelPath = "C:\Users\" & tmpId & "\DeskTop\xxxxxxx Returns1.xlsx"

'OPEN WORKBOOK
Set objWorkBook = objExcel.Workbooks.Open (ExcelPath)
If objWorkBook is Nothing Then
MsgBox ("Could not open your Excel workbook. File:" & Excelpath)
objExcel.Quit
Stop
End If

'FORMAT COL 42 AS NUMBER
objWorkBook.Worksheets ("No Results").Columns ("AP:AP").Select
objWorkBook.Worksheets ("No Results").Columns ("AP:AP").NumberFormat = "#,##0.00;[Red](#,##0.00)"

If Not objExcel.Visible Then objExcel.Visible = True
End Sub
Thank you, Jeane

Jeane

 

[SkipVought]

Hi,

On what statement does your code error?

When I use Excel with a terminal emulator like Extra, I ALWAYS code in Excel VBA rather than Extra VB, which has a terribly awful ugly editor, because the Excel editor compared to the Extra editor is like driving a Viper compared to a Vega. You might consider making that change: well worth the effort.

BTW, your overflow could be due to...

Dim xlRow as Integer

Change Integer to Long, as Excel row limit, post '97, is greater than 32,767.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JeaShe]

Thanks for the advice Skip. My fear is that I have a download script that is run when around 200 staff log into their system each day. I know how to develop in Extra but not so much in VBA. Plus we have so many security issues that Office macros won't generally download. So I put the code in Extra and it generally works sending commands to excel from Extra. I just can't figure out this random error.

Jeane

 

[SkipVought]

BTW, your overflow could be due to...

Dim xlRow as Integer

Change Integer to Long, as Excel row limit, post '97, is greater than 32,767.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JeaShe]

I'll try it! Thank you!

Jeane

 

[SkipVought]

Here are some other suggestions.

This...

If (xl23Col = "TransId is not in R200") Then
If xl42col = 0 Then 'COL11 = "SAME"
objWorkbook.Worksheets("No Results").cells(xlNRTab, 11)= "SAME"
xlMove = 1
ElseIf xl42col <0 Then 'COL11 = "MORE"
objWorkbook.Worksheets("No Results").cells(xlNRTab, 11)= "MORE"
xlMove = 1
Elseif xl42col >0 Then 'COL11 = "LESS"
objWorkbook.Worksheets("No Results").cells(xlNRTab, 11)="Less"
xlMove = 1
End If
End If

...could be coded as...

If (xl23Col = "TransId is not in R200") Then
    Select Case xl42Col
        Case 0
            objWorkBook.Worksheets("No Results").Cells(xlNRTab, 11) = "SAME"
        Case Is < 0
            objWorkBook.Worksheets("No Results").Cells(xlNRTab, 11) = "MORE"
        Case Is > 0
            objWorkBook.Worksheets("No Results").Cells(xlNRTab, 11) = "Less"
    End Select
    xlMove = 1
End If

And this...

If xlMove = 1 Then
'GET DATA FOR SAME OR MORE SHEET
objWorkBook.Worksheets("No Results").Select
objWorkBook.Worksheets("No Results").Rows(xlNRTab).Select
objWorkBook.Worksheets("No Results").Rows(xlNRTab).Copy

'PASTE IT IN SAME OR MORE SHEET
objWorkBook.Worksheets("Same or More").Select
objWorkBook.Worksheets("Same or More").Rows(xlSMTab).Select
objWorkBook.ActiveSheet.Paste
xlSMTab = xlSMTab + 1

'REMOVE IT FROM NO RESULTS
objWorkBook.Worksheets("No Results").Select
objWorkBook.Worksheets("No Results").Rows(xlNRTab).Select
objWorkBook.Worksheets("No Results").Rows(xlNRTab).Delete
xlNRTab = xlNRTab - 1
End If

...could be coded as...

If xlMove = 1 Then
'GET DATA FOR SAME OR MORE SHEET
    objWorkBook.Worksheets("No Results").Rows(xlNRTab).Copy _
    objWorkBook.Worksheets("Same or More").Rows(xlSMTab)
    
    xlSMTab = xlSMTab + 1
    
'REMOVE IT FROM NO RESULTS
    objWorkBook.Worksheets("No Results").Rows(xlNRTab).Delete
    
    xlNRTab = xlNRTab - 1
End If

And this...

'FORMAT COL 42 AS NUMBER
objWorkBook.Worksheets("No Results").Columns("AP:AP").Select
objWorkBook.Worksheets("No Results").Columns("AP:AP").NumberFormat = "#,##0.00;[Red](#,##0.00)"

...could be coded...

'FORMAT COL 42 AS NUMBER
objWorkBook.Worksheets("No Results").Columns("AP:AP").NumberFormat = "#,##0.00;[Red](#,##0.00)"

I'd avoid using Activate and Select unless you actually need the user to SEE the sheet or cell that is being referenced.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JeaShe]

Ok, I've made your edits. You think tightening up the code might help? I thought I had to use the activate and select to access the Excel object specifically. But I'll try not to leave it viewable and see if doesn't go through faster.

Jeane

 

[SkipVought]

All you need is a valid, fully qualified reference. Select and Activate are needless encumberances to good coding in Excel.

The major issue that you need to be aware of and compensate for is the asynchronous nature of a terminal emulator between your code and the mainframe. When you send a comand off to the mainframe, no one knows when the mainframe will respond, and your code could go merrily along, when it should be WAITING for the mainframe to complete its response.

I have found that using WaitForCursor(r,c) in a loop has worked best for me...

Do
   oScrn.WaitForCursor(r, c)
   DoEvents
Loop

...where r and c are the screen rest coordinates for the oScrn object.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JeaShe]

Yes, I've been advised of this since day-1. So I do have a similar loop I use when I'm sending to the mainframe. In this case, I'm only working in Excel. I am interested in creating the code in Excel instead of Extra, except I have problems distributing the Excel macro. The way I deploy macros is to put them in a folder and when the user logs on to their computer a script runs which takes the macros saved in the folder and copy to or over existing macros on the user's system. How would I be able to deploy the Excel macros? Thank you

Jeane

 

[SkipVought]

Similarly, I believe.

Keep your Excel/Extra in a single module. Mine I named Attachmate. Then EXPORT that module to a public server, that your script can access from any user's ID. Your script should then IMPORT/Replace the module in the user's Excel workbook. In my case I had sophisticated users who simply IMPORTed as notified, from the public server.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JeaShe]

I see. Then the user goes in and saves it to their Normal, etc. I'm afraid my users are not as sophisticated. But I'll work on it. Thank you

Jeane

 

[SkipVought]

their Normal" refers to Word but not Excel. But, yes, they would IMPORT and Save into their VBAProject in the appropriate workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JeaShe]

Told you I wasn't very savvy on that! I knew that, but I'm not clear on the Excel version. Right now I'm trying to get 40 macros converted to Reflections from Extra. Micro Focus created a session that allows 3270 files such as quick pads and macros to run but I am running into some problems like this one and I think because the code is a little sloppy it's finding the errors. I'm coming along tho just need to do it in time. Thank you for your help.

Jeane

 

[SkipVought]

The only reflection I know is personal and introspective ;-)

But if you have code problems, plz post your code and explain the error or result. We may have some other members who could help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]