Using data in a macro from notepad 2

[casg]

Hi,
I'd like to feed a macro with data in the notepad,
I've read about CreateObjet in other posts,
but how would it be ?
Where can I find info about methods and properties of the "CreatedObjects"?
The data I want to introduce is thus:
1231
1231
22
2
That's a row of numbers, only.
I'd really appreciate an almost working solution, since I'm rather new in VB and Extraa!

--
thanks

 

[IknowMe]

From the EB help files (feeling a little lazy this morning)

Declare Sub createfile()
Sub main
   Dim acctno as String*3
   Dim recno as Long
   Dim msgtext as String
   Call createfile
   recno=1
   newline=Chr(10)
   Open "C:\TEMP001" For Random As #1 Len=3
   msgtext="The account numbers are:" & newline
   Do Until recno=11
         Get #1,recno,acctno
         msgtext=msgtext & acctno
         recno=recno+1
   Loop
   MsgBox msgtext
   Close #1
   Kill "C:\TEMP001"
End Sub

Sub createfile()
   Rem Put the numbers 1-10 into a file

   Dim x as Integer
   Open "C:\TEMP001" for Output as #1
   For x=1 to 10
      Write #1, x
   Next x
   Close #1
End Sub

 

[casg]

hmm, right, that solves what I asked, but, I really meant something else.

I mean, I want to retrieve data from notepad.exe without using any file, because of a legal restriction.

I want to paste something into notepad and retrieve from it without storing it in a file. It'd be nice also to retrieve it directly from the clipboard, but from the help files I've read that exists extraa->clipboard, but I haven't found the clipboard->extraa way.

anyway many thanks for your response, although it'd be a minor law breaking.

 

[IknowMe]

Give some clarification as to how your obtaining and placing the info into notepad, and I can offer more suggestions.

For example if your just doing a cut paste from extra, why not just scrape the info into an array and use it from there. That way the stored info only exsist while your macro is running and dissapears once it dies.

 

[IknowMe]

Again from the help files.

Sub Main()
	Dim Sys As Object, Sess As Object, MyScreen As Object
	Dim MyArea As Object

	Set Sys = CreateObject("EXTRA.System")
	' Assumes an open session
	Set Sess = Sys.ActiveSession
        Set MyScreen = Sess.Screen
	MyScreen.Select 7,10,7,10
	MyScreen.Copy
	MyScreen.Select 23,6,23,6
	MyScreen.Paste

	' This example demonstrates the Paste method for Area objects.

Set MyArea = MyScreen.Area(9,10,9,10,,3)
	MyArea.Select
	MyArea.Copy
	Set MyArea = MyScreen.Area(23,6,23,6,,3)
	MyArea.Select
	MyArea.Paste
End Sub

 

[casg]

Yes, clarification is very good idea , the process is this:
we get some listings comprising personal data (which is protected by law), we can see the listings prior to printing them. 'We can see it' means we can copy and paste them into excel, for example, why excel, because, it will split the listings into sensible columns and then we can select the column with the numbers we want to use.

When we use that column, we want to perform a repetitive process with each number of that column in extraa ! (search for data, print it, analyze the data in the screen... ) .

I think that Myarea.paste will always paste info into extraa! screen, but we want to use the listing in the macro (for looping on it and perform certain process) .

thanks again.

 

[shadygrove]

Forget notepad and just use VBA from Excel. The following VBA script is something I use. I have a column of Record Identifiers and I scroll down this column pulling up each record and extracting some information from Extra!. This information is written back to the Excel spreadsheet.

I find automating Extra! in VBA much easier than writing EB macros in Extra!.

Check out faq99-4069

' This Excel VBA script was created by Andrew Becherer.
' With code posted by calculus to faq99-4069
' located at [URL unfurl="true"]http://www.tek-tips.com/faqs.cfm?fid=4069[/URL]
' Date: Friday, January 27, 2006

' Remember to declare your variables!
Option Explicit

Public System As ExtraSystem
Public Sessions As ExtraSessions
Public Sess0 As ExtraSession
Public MyScn As ExtraScreen

Sub GetData()

Set System = New ExtraSystem
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
Set MyScn = Sess0.Screen

'Extra Objects
    Set System = CreateObject("EXTRA.System") ' Gets the system object
    
    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

'...Macro Magic happens here...
    
    'declare variables necessary to find the record and record the data
    Dim currentRowIndex As Integer
    'the first data row is row 2
    currentRowIndex = 2
    Dim currentRecordIdentifier As String
    Dim currentCel As String
    Dim currentVal1 As String
    Dim currentVal2 As String
    Dim currentVal3 As String
    
    'Define Colums
    Dim recordIdentifierCol As Integer
    recordIdentifierCol = 1
    Dim Val1Col As Integer
    Val1Col = 2
    Dim Val2Col As Integer
    Val2Col = 3
    Dim Val3Col As Integer
    Val3Col = 4
    
    'Set to correct screen
    MyScn.PutString "ScreenCode", 1, 16
    
    ' The number of records to access	    
    For currentRowIndex = 2 To 100000
    
        'Get the record identifier
        Cells(currentRowIndex, recordIdentifierCol).Select
        currentRecordIdentifier = Cells(currentRowIndex, recordIdentifierCol).Value
        
        'Bring up the correct screen
        MyScn.PutString currentRecordIdentifier, 1, 25, 1
        MyScn.SendKeys ("<Enter>")
        ' Wait for the screen to settle
	' WaitHostQuiet does not work for me
        ' MyScn.WaitHostQuiet 1000
        Application.Wait Now + TimeValue("00:00:01")

                        
        'Get Value 1 from Extra!
        currentVal1 = MyScn.GetString(17, 21, 1)
        'Write the Value 1 to Excel
        Cells(currentRowIndex, Val1Col).Value = currentVal1
                        
        'Get Value 2 from Extra!
        currentVal2 = MyScn.GetString(17, 28, 1)
        'Write the Value 2 to Excel
        Cells(currentRowIndex, Val2Col).Value = currentVal2
        
        'Get Value 3 from Extra!
        currentVal3 = MyScn.GetString(17, 36, 1)
        'Write the Value 3 to Excel
        Cells(currentRowIndex, Val3Col).Value = currentVal3
            
    Next currentRowIndex
    
' Clean up after yourself!
Set Sessions = Nothing
Set System = Nothing
Set Sess0 = Nothing
Set MyScn = Nothing

End Sub

 

[IknowMe]

If I have this correct.

Copy info from some source, paste into excel then without saving any type of file, object excel get info, do something (is this something in excel or extra?) and close excel without ever saving anything.

Excel VBA would be best choice if you are manipulating info for each customer in the excel sheet (not what I think you want if your not saving any of your manipulations).

EB would be best choice if you are manipulating info for each customer in extra.

Either way your using both EB and VBA.

First scenario your using VBA with an Extra object using EB methods and functions to handle the Extra side.

Second scenario your using EB with an Excel object using VBA
methods and functions to handle the Excell side.

Pretty simple either way which are you shooting for?

 

[casg]

>Excel VBA would be best choice if you are manipulating info >for each customer in the excel sheet (not what I think you >want if your not saving any of your manipulations).

No, I handle info in the Extra session, and I get a final result like "yes, he's a target". I mean, most of the "brain" work must be done in extra. Excel would be just an input.

>Second scenario your using EB with an Excel object using VBA
>methods and functions to handle the Excell side.

Yes , I think this is better, I have to test the impressive code from shadygroove , but , I think that that code opens a new extra session (is this right? or it communicates with the existing one ?). Anyway, lovely code I have to mess with. Thanks.

 

[shadygrove]

My VBA script communicates with an existing session.

Its pretty simple if you just look past all of the Extra! object creation stuff. I just use .putString() and .sendKeys() to drive Extra! and .getString() to extract the info I need. It sounds like you would be doing more .putString() and .sendKeys() and less .getString().

I found using VBA easier than EB because the VBA editor is better. You can see the object model for all of the Extra! objects. The most difficult part getting started for me was groking the EB documentation.

 

[IknowMe]

A start

Sub Main

'Extra Object
Dim Sys As Object, Sess As Object, MyScreen As Object, MyArea As Object
	Set Sys = CreateObject("EXTRA.System")
	Set Sess = Sys.ActiveSession
	Set MyScreen = Sess.Screen
	
'Excel Object
Dim Excel_Obj As Object, Excel_Sheet As Object
        Set Excel_Obj = GetObject(,"excel.application") 'gets excel assuming it's open
        Set Excel_Sheet = Excel_Obj.ActiveSheet         'get the open sheet

Dim TotalRows, Counter As Integer
Dim Column1()
TotalRows = 0
Redim Column1(TotalRows)

'get Excel info
While Excel_Sheet.Cells(TotalRows+1,1).value <> ""
    Redim Preserve Column1(TotalRows+1)
    Column1(TotalRows+1) = Excel_Sheet.Cells(TotalRows+1,1).value
    TotalRows = TotalRows+1
wend

If TotalRows > 0 then
    For Counter = 1 to TotalRows
    'do your extra stuff here
    msgbox Column1(Counter)
    Next
End If

End Sub

 

[casg]

Wow ! you guys are great! I'm eager to code all those things, tomorrow morning ! I 've given purple stars to you all!

 

[IknowMe]

If you struggle with one object or the other an easy way to get some help of your own is to record a macro and look at the source. Whatever either language can do for itself the other can do through it's object.

Excel

Set NewBook = Workbooks.Add
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False

NewBook.SaveAs Filename:=fName

EB with Excel object

Dim Excel_Obj As Object, NewBook as Object
Set Excel_Obj = GetObject(,"excel.application")
Set NewBook = Excel_Obj.Workbooks.Add

Do
fName = Excel_Obj.GetSaveAsFilename
Loop Until fName <> False

NewBook.SaveAs Filename:=fName

 

[casg]

I'm trying the code posted by shadygrove (in an excel macro) and in the line
public system as extrasystem

it says :"type not defined by the user"

I've tried too, the line:

Set Ses1 = GetObject("Session1.EDP")

But it says:
"depuration error"

How can I use an existing extra session in excel.

 

[casg]

No way, I've followed this steps:
- I've recorded a macro
- I've openened the macro editor
- Using tools->references I've added the "extra automation tools", I even can browse the objects in the objects inspector.
- I add this code to the newly recorded macro:

Set System = CreateObject("EXTRA.System") ' Gets the system object
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
myscn = Sess0.screen
myscn.Paste

But, in the very first line it says :
error '-2147221231 (80040111)' automation error.

I feel I'm so near to the solution !!!

 

[casg]

This has worked perfectly, thanks :

'Excel Object
Dim Excel_Obj As Object, Excel_Sheet As Object
Set Excel_Obj = GetObject(,"excel.application") 'gets excel assuming it's open
Set Excel_Sheet = Excel_Obj.ActiveSheet 'get the open sheet

Dim TotalRows, Counter As Integer
Dim Column1()
TotalRows = 0
Redim Column1(TotalRows)

'get Excel info
While Excel_Sheet.Cells(TotalRows+1,1).value <> ""
Redim Preserve Column1(TotalRows+1)
Column1(TotalRows+1) = Excel_Sheet.Cells(TotalRows+1,1).value
TotalRows = TotalRows+1
wend

 

[IknowMe]

it says :"type not defined by the user"

I believe it's telling you your using a record type you haven't defined.

e.x.

before using
Set Ses1 = GetObject("Session1.EDP")

you need to declare (dim,redim) Ses1 as a record type (object,string,integer ect.).

 

[casg]

No,
I haven't pasted the declarations, but they were there and the error wasn't about types but the "automation error." thingy.
Anyway, the EB-driven macro(script) is just impressive.

 

[Skie]

I would bet System is a reserved word. Change Set System to like Set objSystem.

Set objSystem = CreateObject("EXTRA.System")   ' Gets the system object
If (objSystem Is Nothing) Then MsgBox "Could not create the EXTRA System object.  Stopping macro playback": Stop
 Set Sessions = objSystem.Sessions
 If (Sessions Is Nothing) Then: MsgBox "Could not create the Sessions collection object.  Stopping macro playback.": Stop
 Set Sess0 = objSystem.ActiveSession
If (Sess0 Is Nothing) Then MsgBox "Could not create the Session object.  Stopping macro playback.": Stop
myscn = Sess0.screen
myscn.Paste

 

[casg]

No, I changed it to objSystem and the error is just the same.