INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Reflections and Overflow Error

Reflections and Overflow Error

(OP)
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

RE: Reflections and Overflow Error

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...

CODE

Dim xlRow as Integer 

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reflections and Overflow Error

(OP)
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

RE: Reflections and Overflow Error

BTW, your overflow could be due to...

CODE

Dim xlRow as Integer 

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reflections and Overflow Error

(OP)
I'll try it! Thank you!

Jeane

RE: Reflections and Overflow Error

Here are some other suggestions.

This...

CODE

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...

CODE

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...

CODE

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...

CODE

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...

CODE

'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...

CODE

'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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reflections and Overflow Error

(OP)
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

RE: Reflections and Overflow Error

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...

CODE

Do
   oScrn.WaitForCursor(r, c)
   DoEvents
Loop 
...where r and c are the screen rest coordinates for the oScrn object.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reflections and Overflow Error

(OP)
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

RE: Reflections and Overflow Error

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reflections and Overflow Error

(OP)
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

RE: Reflections and Overflow Error

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reflections and Overflow Error

(OP)
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.smile

Jeane

RE: Reflections and Overflow Error

The only reflection I know is personal and introspective winky smile

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close