Copy/Paste from Extra! to Excel 97

[2010noACAPS]

Let me start off by saying this site is great. It got me an answer to a copy/paste situation that I've been trying off and on for about a year to work since I'm not a programmer, just a user and have no experience with Basic/VB.

I have another project in mind to automate one other job funtion that takes a reference ID, loan #, and customers last name from one screen and puts it into an excel spreadsheet. I found a post that might be the answer if it wasn't in spanish, so I don't know if it's the right thing or not. This was in thread99-468324, a reply by mbars. Also, I found in another Screen Scrape post how to append spaces. This will not be a problem, my problem is on our system the customer's name is listed as "LastName, FirstName" and I just want to "print" LastName into the spreedsheet field. Is there something that could tell the macro to just copy what's in front of a comma or to print just what's in front of a comma? I'd also like to do this on a pre-made template file.

It's a tall order for a lower-than-novice like myself to put on myself but I like effeciency and this would lessen the time it takes to do this particular job function for me and my co-workers.

 

[calculus]

First off, take a look at the FAQs for this forum:

How do I use VB(A) to manipulate attachmate (6.5+)? faq99-4069
I'm ready for Extra Basic... now what? faq99-4087
How do I get data to Excel? faq99-4068

There are several ways to make this happen for you. The easiest way is to code in VBA (Excel) a macro that will connect to Extra and get the data. Look in the EB FAQ for the GetString method. You can also use the Instring method to get the last name.

CustomerName = Sess0.Screen.GetString(Row,Col,length)
LastName = Left(CustomerName,Instr(CustomerName,",")-1)

The LastName line is in 2 parts.
"Instr(CustomerName,",") - returns the position of the comma in the customer name. Subtract 1 to get the position of the last letter of the name.
The "Left" syntax will take only the left side of the string for however many characters you specify:
Left(CustomerName,5)
We've just compounded the code to look for the comma in the name.

From there you'll have to set up VBA to put the LastName into the cell you want like this:

Cells(Row,Col).Value = LastName

calculus

 

[2010noACAPS]

Great! I get the LastName part perfectly. I'm still a little confused on putting the strings in the righ cells. Will this "Cells(Row,Col).Value = LastName" put the strings in the next available cell in a particular column? I'm trying to grasp the whole Visual/Extra Basic language.

I am still trying to get the Programmer-hat to fit. I'm more a layout design kind of guy. Programming doesn't come too naturally for me. And since it would be easier to use Excel VBA I'll go try and find a good book to give me a little more help so I don't bother you all with silly questions.

 

[calculus]

Actually, the easiest way to learn VBA is to record what you want to happen, then look at the code.

For:
Cells(Row,Col).Value = LastName

Row is the row you want to put the data in, Col is the column you want to put the data in. As long as you don't have a lot of blank lines on the bottom of your file this works well for getting the first blank line.

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

So if you wanted the last name in Column A (1st column):

NewRow = Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Cells(NewRow,1).Value = LastName

Or if you want to do it in 1 line of code:

Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1,1).Value = LastName

calculus

 

[2010noACAPS]

Sorry for not responding before now, it's the start of our busy season for mortgages. I finally made up my mind that if I'm going to do this I'll have to work on it at home since now that I want to shorten the macro I make to work for another Excel list. I'm starting tonight so I'll be sure to keep you posted.

I'm really starting to get the VB bug now that I see how much it can help. I really want to get into Database Administration but I was always leary about the languages used, VB or C+. VB always looked like math that didn't make since. It's slowly, very slowly, sinking in what the syntax of the language is so I think I might be comfortable enough to look into DB Admin classes.

If I haven't said it before, I REALLY appreciate all the help I've gotten from this site and this particular forum. Thanks!


T

 

[2010noACAPS]

I almost forgot...

We use Status codes in one of our sessions, is it possible to make the macro look and see if the customer's file is in a certain status before it goes into printing the strings in Excel? The status codes are always at the same coordinates on certain screens so I have an idea:

MyString = Sess0.Screen.GetString(X, Y, Length)
If MyString = "BK" Then

Not really sure how VB uses If/Then statements but you get the picture. Am I on the right track? The downside to working at home is I cannot go into Extra! to see if it's working right away.

 

[calculus]

Yep, on the right track.

VBA If/Then syntax would be something like this:

MyString = Sess0.Screen.GetString(X, Y, Length)
If MyString = "BK" Then
...Do myStuff Here
ElseIf MyString = "SomeOtherCode" then
...Do Other Stuff here
Else
...Catch all other cases here
End If

You'll probably just be using 1 If piece for your program.

Good luck,

calculus

 

[2010noACAPS]

Sometimes it will be "Status - Grade". So will the if/then statment just look for only "BK" and that's it? I guess my question is can i tell it that if BK is somewhere in the string then it's OK to print in Excel.

 

[calculus]

Then you'll want the Instring function:

Instr(BigString, SearchforString)

The function returns 0 if it's not found, and a positive integer if it is found. The integer is the position in the string, starting from the left.

calculus

 

[2010noACAPS]

I think I'm getting over my head, because I want this all to be started by a UserForm. I have the code done, I think, for the getting data part from ACAPS (Extra), but I'm really at a loss as to how to start if off with the UserForm. I have the UserForm drawn out in Excel having the user inputing the ACAPS# and then hitting Submit, Reset, or Cancel, I just don't know how to connect the objects to the form. I have O'Reilly's "VB & VBA in a Nutshell" and while it's given me a lot of info I haven't found that piece of information yet. Is there some specific variable I should be looking for to get an example? I also need to be able to have a message box pop up with whatever error message that ACAPS spits back, such as the ACAPS# entered was invalid. I also need the form to stay open even after the task is completed with the text box cleared.

This is my code so far:

Public Sessions As Object
Public System As Object
Public Sess0 As Object


Set System = CreateObject("EXTRA.System")

If (System Is Nothing) Then MsgBox "Could not create the EXTRA System object.  Stopping macro playback.": Stop
Set Sessions = System.Sessions
If (Sessions Is Nothing) Then: MsgBox "Could not create the Sessions collection object.  Stopping macro playback.": Stop
Set Sess0 = System.ActiveSession
If (Sess0 Is Nothing) Then MsgBox "Could not create the Session object.  Stopping macro playback.": Stop

'UserForm stuff goes here I assume. Such as:
'Text.TextBox_1 = ReferenceNumber
'ReferenceNumber = Sess0.Screen.PutString(2, 45)
'Sess0.Screen.SendKeys(<Enter>)

Status = Sess0.Screen.GetString(5, 73, 6)
InStr(Status, BK)
If InStr = 0 Then MsgBox "File is not yet in BK status.": Stop
Else: Resume Next
 
ACAPSNumber = Sess0.Screen.GetString(2, 45, 15)
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Value = ACAPSNumber

CustomNumber = Sess0.Screen.GetString(7, 33, 10)
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Value = CustomNumber

CustomerName = Sess0.Screen.GetString(3, 2, 18)
LastName = Left(CustomerName, InStr(CustomerName, ",") - 1)
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Value = LastName


Set Sessions = Nothing
Set System = Nothing
Set Sess0 = Nothing

 

[calculus]

I'd start by suggesting you read through the VBA forum forum707.

Having said that, your program flow probably needs some attention. If you want to kick it off in Excel, show the user form modally:

userform1.show

You said you had 3 buttons, you'll need to assign code to each button's click event. The cancel will look like this:

Unload UserForm1

In the submit's click event you'll have the code for Extra. I assume you're using a textbox to get the ACAPS# from the user. I would assign that to a variable first, then refer to the variable in code.

Private Sub cmdSubmit_Click()
ACAPS = Trim(userform1.textbox1.text) 'Trim removes leading and trailing spaces

'Extra Code here

'Your If/Then looks a bit strange
If InStr = 0 Then MsgBox "File is not yet in BK status.": Goto BadBK:   'Create a BadBK label that will skip all the stuff you don't want to do

BadBK:
End Sub

Your userform will not close in this situation. You'd have to press cancel to make that happen.

Check out the VBA forum for more info on userforms and program flow.

calculus

 

[2010noACAPS]

OK, this is what I have under my Forms folder in VB Editor

Private Sub cmdSubmit_Click()
    Dim ACAPSNumber As String
    ACAPSNumber = Trim(UserForm1.txtData.Text)

Private Sessions As Object
Private System As Object
Private Sess0 As Object

Set System = CreateObject("EXTRA.System")

If (System Is Nothing) Then MsgBox "Could not create the EXTRA System object.  Stopping macro playback.": Stop
Set Sessions = System.Sessions
If (Sessions Is Nothing) Then: MsgBox "Could not create the Sessions collection object.  Stopping macro playback.": Stop
Set Sess0 = System.ActiveSession
If (Sess0 Is Nothing) Then MsgBox "Could not create the Session object.  Stopping macro playback.": Stop

ACAPSNumber = Sess0.Screen.PutString(2, 45, 15)

Status = Sess0.Screen.GetString(5, 73, 6)

If InStr(Status, BK, 1) = 0 Then MsgBox "File is not yet in BK status.": GoTo BadBK:     'Create a BadBK label that will skip all the stuff you don't want to do

BadBK:  UserForm.Activate
    txtData.Text = ""
    txtData.SetFocus

Else: Resume Next

ACAPSNumber = Sess0.Screen.GetString(2, 45, 15)
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Value = ACAPSNumber

CustomNumber = Sess0.Screen.GetString(7, 33, 10)
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Value = CustomNumber

CustomerName = Sess0.Screen.GetString(3, 2, 18)
LastName = Left(CustomerName, InStr(CustomerName, ",") - 1)
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Value = LastName


Set Sessions = Nothing
Set System = Nothing
Set Sess0 = Nothing

txtData.Text = ""
txtData.SetFocus

End Sub

Private Sub cmdReset_Click()
    
    txtData.Text = ""
    txtData.SetFocus

End Sub

Private Sub cmdClose_Click()
    
    Unload UserFrom1

End Sub

And this is what I have under Module1

Private Sub UserForm_Activate()
    txtData.Text = ""
    txtData.SetFocus
End Sub

But when I go to compile it stops at the first "Public" expression and tells me

Compile Error: Invalide attribute in Sub or Function

What am I doing wrong?

 

[calculus]

I'm not sure what's not compiling correctly, but here a few things of note.

Your code

If InStr(Status, BK, 1) = 0 Then MsgBox "File is not yet in BK status.": GoTo BadBK:     'Create a BadBK label that will skip to end

BadBK:  UserForm.Activate
    txtData.Text = ""
    txtData.SetFocus

Else: Resume Next

Should look more like this.

If InStr(Status, BK, 1) = 0 Then 
    MsgBox "File is not yet in BK status." 
    GoTo BadBK:     'Create a BadBK label that will skip to end
[B]End If[/B]

ACAPSNumber = Sess0.Screen.GetString(2, 45, 15)
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Value = ACAPSNumber

CustomNumber = Sess0.Screen.GetString(7, 33, 10)
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Value = CustomNumber

CustomerName = Sess0.Screen.GetString(3, 2, 18)
LastName = Left(CustomerName, InStr(CustomerName, ",") - 1)
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Value = LastName

[b]BadBK:[/b]
Set Sessions = Nothing
Set System = Nothing
Set Sess0 = Nothing

txtData.Text = ""
txtData.SetFocus
End Sub

As to the whole userform issue, you don't have the userform ever being displayed. How are you trying to run the program?

Under ThisWorkbook use this code to show the userform:

Private Sub Workbook_Open()
     UserForm1.Show
End Sub

Your object declarations in the userform should be as follows:

[B]Dim[/B] Sessions As Object
[B]Dim[/B] System As Object
[B]Dim[/B] Sess0 As Object

In running the code on my machine, I get an error here:

Status = Sess0.Screen.GetString(5, 73, 6)

If InStr(Status, BK, 1) = 0 Then

Because you've not mentioned what BK is, so your comparing Status to an empty variable, which throws an error. Otherwise it looks like the code works.

When trying to run the code, make sure you're in the workbook open routine so that the first line of code will show the userform. Also, do you have the userform unloading when cancel is clicked?

calculus

 

[2010noACAPS]

Most of that worked. When I compiled it the first time after making the changes, it told me

Block If without End If

So I put

If InStr(Status, BK, 1) = 0 Then
    MsgBox "File is not yet in BK status."
    GoTo BadBK:
    'Create a BadBK label that will skip all the stuff you don't want to do
End If

and that worked.

Also I put in 'BK = "BK"' before the "If InStr..." code for the empty variable because BK is BK. The compiler took it but that doesn't seem right. Why then can't I put "If InStr(Status, "BK", 1) = 0..."

Then while I was at work today I found "object.XStatus". I thought this could be part of my error message for when people enter the wrong ACAPS# to show the error ACAPS gives when it's typed wrong. I thought I'd change BadBK: to look at xINVALID_NUM.XStatus and print it in a MsgBox before it goes back to the UserForm.

Is this correct?

ErrMsg = Sess0.Screen.xINVALID_NUM.XStatus
Dim Msg, Title, Response
Msg = ErrMsg
Style = vbOKOnly + vbCritical
Title = "ACAPS Error"

BadBK:
Set Sessions = Nothing
Set System = Nothing
Set Sess0 = Nothing
Response = MsgBox(Msg, Style, Title)
txtData.Text = ""
txtData.SetFocus

End Sub

 

[2010noACAPS]

I'm at work today and am trying to test my macro. It doesn't do anything. The form comes up but it doesn't connect to Extra!. The code that's in my 5/13 posting, along with all the corrections from yesterday, is in UserForm1 under Forms and the Show function is in ThisWorkbook.

 

[calculus]

You need to put some elbow greese in the debugging process. I'm not able to debug this for you. Step through the procedures and use the watches (right click a variable or object to add a watch) to figure out what the program is doing.

You should be able to tell if it's connecting to Extra by watching the Extra objects. Also, is Extra open? Anything misspelled?

It's also possible to add external references to Extra, but this is probably complicating the issue for you.

Step through the code and find your error. Your previous post about adding a "DO" before the end sub is incorrect, which means you've got another bad "if" statement or "Do" or "For" statement. That's probably causing you some problems.

calculus

 

[2010noACAPS]

OK, I'm running into when I add Watches it is not letting me StepOver, it will just let me do StepInto and Run to Cursor.

I think this is part of my problem in my coding:

Set System = CreateObject("EXTRA.System")

1) We don't have access to Extra! itself. All we have on our systems are shortcuts to Sessions.
2) Those Sessions our on our personal server drives not our CPUs.

I'm using Excel 2000 so I'm going to try

Set System = CreateObject("Production Mainframe Session 1.System", H:\E!TCP\Sessions)

I'll try when I go in tomorrow. I think that is most of my problem. I have to spruce up my flow anyway so that may fix other problems.

 

[2010noACAPS]

OK, I got it to mostly work because I turned the Reference on for the Attachmate Library. (DUH!) I'm having a problem now when it gets to

LastName = Left(CustomerName, InStr(1, CustomerName, ",", 1) - 1)

it's telling me run-time error 5 or 3 or 13, "Invalid Ojbect Call" or something like that. I'm going to try Left$ and hope that works.

Also it's not printing anything to Excel before it gets to the error so i'm hoping it prints after the code runs. Hope and see!

 

[2010noACAPS]

*REPHRASE of previous post*
I have created 2 macros simultaneously. The first macro tests to see if a file has been booked yet and the second just to scrape info to Excel. Well the first macro just jumps to the BadBK: no matter what state the file is in and the second macro gives me run-time error 5 or 3 or 13, "Invalid Ojbect Call" or something like that for the LastName=.  Neither one of the macros prints to Excel as it goes. Extra! is doing what I want it to do, it's Excel that's giving me the problem. Nothing in Excel Help file is helping. I looked at everything Cell related and all that, but it just tells me what I've found out here. I know I'm overlooking something very simple! I feel like I've reached a road block. Any help is appreciated. (Even a simple "Look here..")

Thanks

 

[calculus]

The VBA forum is here:
VBA Visual Basic for Applications (Microsoft) Forum (29636 members)
forum707
There are a ton of people using this forum, so answers come quickly. You will have to be more specific about what's giving you the problem.


Looking at code snipets above, this could be a cause for the first problem. As you step through the program, hover over the "Status" variable and make sure it is being picked up properly. If "Status" is not coming from Extra properly, then BK will not be part of the "Status" Variable and will kick the Msgbox below and exit.

Status = Sess0.Screen.GetString(5, 73, 6)
If InStr(Status, BK, 1) = 0 Then
MsgBox "File is not yet in BK status."
GoTo BadBK: 'Create a BadBK label that will skip to end
End If

On to the second error. Your code snipet is:

LastName = Left(CustomerName, InStr(1, CustomerName, ",", 1) - 1)

Assuming that CustomerName is in the form LastName, FirstName. Try this instead. It's a bit simpler, but your code looks right.
LastName = Left(CustomerName,Instr(CustomerName,",")-1)

Hope this helps,

calculus