get data from excel, send to extra?!

[mizsydney]

I most humbly apologize in advance for my lack of knowledge, I'm sure my questions are painfully obvious to you guys!

I am trying to set up a macro that will scrape a cell value from excel, send to extra & <enter> to bring up a record, send more data from cells once the Extra screen comes up, <enter> to save the record, and keep going row by row, until the next row in excel is blank.

I have begged/borrowed/stolen scraps of macro code from this site and got something that sorta works, but I'm not understanding some basic concepts. the Help files for both excel VBA and Extra sort of assume you know what you're doing so they haven't been helpful to me. and recording macros hasn't helped me either.

I started here:

http://www.tek-tips.com/viewthread.cfm?qid=1526657&page=2

I got this from vzachin:

Sub Main
    Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession

'Declare the Excel Object
Dim xlApp As Object, xlSheet As Object, MyRange As Object
Set xlApp = CreateObject("excel.application")
xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
xlApp.Visible = True

    xlApp.Workbooks.Open FileName:="C:\MHNAR.xls" 
  
Set xlSheet = xlApp.activesheet
Set MyRange = xlApp.activesheet.Range("A:A")

Dim Row As Long,rw as Long
rw=5
do
With xlApp.ActiveSheet
Set MyRange = .Range("A2:A65536").Resize(xlApp.CountA(.Range("A2:A65536")))
End With
For Rows = 4 To 65536
if MyRange.Rows(Rows).Value = "" Then Exit Sub
Sess0.Screen.PutString MyRange.Rows(Rows).Value, rw, 18
if rw = 22 then
rw = 5
else
rw = rw + 1
end if
Sess0.Screen.Sendkeys("<Enter>")    
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Next Rows

loop
End Sub

this seems to be going down the excel column and then <enter>ing on Extra when it runs out of lines on the Excel spreadsheet. it seems to work for that purpose but I can't puzzle out how to change it to do what I need. (more than one cell, rows instead of columns, etc)


this is the code I have that fetches cells from Excel and sends to Extra:

    ' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$
 
Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
    Dim Sessions As Object
    Dim System As Object
    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
    End If
    Set Sessions = System.Sessions
 
    If (Sessions is Nothing) Then
        Msgbox "Could not create the Sessions collection object.  Stopping macro playback."
        STOP
    End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
    g_HostSettleTime = 1000        ' milliseconds
 
    OldSystemTimeout& = System.TimeoutValue
    If (g_HostSettleTime > OldSystemTimeout) Then
        System.TimeoutValue = g_HostSettleTime
    End If
 
' Get the necessary Session Object
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    If (Sess0 is Nothing) Then
        Msgbox "Could not create the Session object.  Stopping macro playback."
        STOP
    End If
    If Not Sess0.Visible Then Sess0.Visible = TRUE
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
    
    

'
Dim MyScreen As Object
Set myscreen = Sess0.Screen
'
'   -------------------------------------------fetch and check for SMUP screen
'
'
  Sess0.Screen.Sendkeys("<Clear>")
  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop  
    Do While Sess0.Screen.OIA.XStatus <> 0 : Loop        
  
Sess0.Screen.MoveTo 1, 1
Sess0.Screen.Sendkeys("SMUP<Enter>")
  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop  

  
    v1$ = MyScreen.GetString(1, 2, 4)
    if v1$ <> "SMUP" then
           'no -> error!!!
            msgbox "oops!  Log onto SMUP first."
       exit sub
    end if
'   
    
'---------------------------------------------------------open excel file
 
'Declare the Excel Object
        Dim xlApp As Object, xlSheet As Object, Row As Long
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = False
        xlApp.Workbooks.Open FileName:="C:\MHNAR.xls" 
        Set xlSheet = xlApp.activesheet
        Set APNMyRange = xlApp.activesheet.Range("G5")  'get clean APN
        Set narMyRange = xlApp.activesheet.Range("F5")  'get clean narrative
        Set rollMyRange = xlApp.activesheet.Range("A5") 'get roll years 
        set narrind = xlApp.activesheet.Range("E5")     'get narr ind
        
'---------------------------------------------------------call up the record to change             
          
    Sess0.Screen.putstring(APNMyRange) 1, 7     'send APN


    Sess0.Screen.putstring(rollMyRange) 2,24    'send roll year
   
        Sess0.Screen.Sendkeys("<Enter>")
        
  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop  


    Sess0.Screen.WaitForString "SMUP",1,2



'<---------------------------------------------------send update codes & initials
'
Sess0.Screen.putstring("u") 2, 65
Sess0.Screen.putstring("m h") 4, 52 '<------------------initials

        Sess0.Screen.Sendkeys("<Enter>")
        
  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop  


    Sess0.Screen.WaitForString "ENTER",12,24
      

Sess0.Screen.putstring("nar") 2, 74 '-----------send reason code


'<---------------------------------------------------  blank field text and send to smup screen
 Sess0.Screen.MoveTo 8,14
Sess0.Screen.Sendkeys("<EraseEOF>")	
Sess0.Screen.putstring(narMyRange) 8, 14 
Sess0.Screen.putstring(narrind) 8, 74 

'<---------------------------------------------------  enter changes
'
 Sess0.Screen.Sendkeys("<Enter>")

  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop 
            
 Sess0.Screen.WaitForString "UPD", 24, 30



'<--------------------------------------------------  need loop to next XL line here


'
'<---------------------------------------------------  End Program
'
  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop  
 '           
sess0.Screen.WaitHostQuiet(qt)
System.TimeoutValue = OldSystemTimeout


'
'<----------------------- close excel
'
xlApp.Quit
set xlApp = Nothing

exit sub
  


 
end sub

this "works" to send the info from one row of excel to the fields I need on extra, but I don't know how to make it keep going until the next row in the excel file is blank.



I have an excel worksheet that I will receive info on, each row will have information for one particular record over 3 or 4 columns. the sheet may have one row or 20 (I can set a maximum if needed to work with extra). I would like to specify the tab name (instead of "active worksheet").


I need the macro to get a cell from column G in excel, send to a specific field on extra, and <enter> to view a record, then send keystrokes to various fields on extra to enter the change codes I need to access the record and <enter> to retrieve the record for change, then update 3 or 4 fields on that extra screen with the other info on that row, <enter> in Extra after the putstrings from excel, and then keep going until the spreadsheet is out of rows.

I understand the putstrings and how to manipulate extra, it is getting the excel data I'm stuck on. how do I set the first row (where to start) in excel, pick the cells I need, and keep going until I am out of rows?

maybe if someone is able to point me in the right direction, a light bulb will go off and this will start making sense.

thanks for anything you can help me with!

 

[SkipVought]

Hi,

I would like to specify the tab name (instead of "active worksheet").

dim lRow as long, iCol as integer
Set xlSheet = xlApp.Worksheets("YourSheeetName")
With xlSheet
  Set APNMyRange = .Range("G5")  'get clean APN
  Set narMyRange = .Range("F5")  'get clean narrative
  Set rollMyRange = .Range("A5") 'get roll years 
  set narrind = .Range("E5")     'get narr ind

  do
     for iCol = 1 to 7
        select case iCol
           case 1 'roll years
              Sess0.Screen.putstring(.cells(lrow, icol).value) 2,24  
           case 5 ' narr ind
             Sess0.Screen.putstring(.cells(lrow, icol).value) 8, 74 
           case 6 'clean narrative
             Sess0.Screen.putstring(.cells(lrow, icol).value) 8, 14 
           case 7 'clean APN
             Sess0.Screen.putstring(.cells(lrow, icol).value) 1, 7
        end select
     next
     lRow = lRow + 1
  loop while .cells(lrow, "A").value <> ""
end with

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Oh yes, assign 5 to lRow as an initial value.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]