borrowed code not working 1

[mizsydney]

this code worked for me until I tried to modify it for a different use. now I get and "object value set to nothing" error, even though I didn't set any object in my old code.

this is horribly written, I copied it from other functioning macros and since I don't fully understand the dim, set, etc I've tried to leave things alone.

would someone be able to help me clean this up, please? TIA

    ' 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 = 100        ' 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 logged-on 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
'   
    
'---------------------------------------------------------get info from excel file
 
'Declare the Excel Object

        Dim obj as object
        Dim xlApp As Object, xlSheet As Object, Row As Long
        Set xlApp = CreateObject("excel.application")
        
 

        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        
        Set obj = Getobject("G:\Property Transfers\Programs\PT macros\address\multi.xls")
        
      
         
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        
   Set xlSheet = xlApp.activesheet
'Set xlSheet = xlApp.Worksheets("Sheet1") 
 
 
       
       
        Set L1 = xlApp.activesheet.Range("B3")  'get line 1
        Set L2 = xlApp.activesheet.Range("C3")  'get line 2
        Set L3 = xlApp.activesheet.Range("D3")  'get line 3
        Set L4 = xlApp.activesheet.Range("E3")  'get line 4
        Set jul = xlApp.activesheet.Range("H3") 'get julian date     
                 
        Set rollMyRange = xlApp.activesheet.Range("F6") 'get roll ind
        
   ' set narrind = xlApp.activesheet.Range("E5")     'get narr ind
        

        
 
'--------------------------------------------------------------------------------       
        
Dim APN 
Dim SessSM as object
Set myscreen = Sess0.Screen

'


 '--------------------------------------------------------------------------------
 ' ask for APN
 
 APN=inputbox("enter next APN, then <Tab> <Enter>, or click OK")        
        
        
'---------------------------------------------------------call up the record to change             
          
    Sess0.Screen.putstring(APN) 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("adr") 2, 74 '-----------send reason code


'<---------------------------------------------------  blank field text and send new text to smup screen
 Sess0.Screen.MoveTo 5,14
Sess0.Screen.Sendkeys("<EraseEOF>")    
Sess0.Screen.putstring(L1) 5, 14 

 Sess0.Screen.MoveTo 5,45
Sess0.Screen.Sendkeys("<EraseEOF>")    
Sess0.Screen.putstring(L2) 5, 45 

 Sess0.Screen.MoveTo 6,14
Sess0.Screen.Sendkeys("<EraseEOF>")    
Sess0.Screen.putstring(L3) 6, 14 

 Sess0.Screen.MoveTo 6,45
Sess0.Screen.Sendkeys("<EraseEOF>")    
Sess0.Screen.putstring(L4) 6, 45 

 Sess0.Screen.MoveTo 6, 72
Sess0.Screen.putstring(jul) 6, 72 


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




exit sub
  



end sub

 

[SkipVought]

Hi,

I get and "object value set to nothing" error

on what statement?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mizsydney]

sorry! meant to include that important bit of info - line 96:


Set L1 = xlApp.activesheet.Range("B3") 'get line 1

if I rem that out, line 97:

Set L2 = xlApp.activesheet.Range("C3") 'get line 2

 

[SkipVought]

I'd use the EXPLICIT statement...

'  Set xlSheet = xlApp.activesheet[b]
  Set xlSheet = xlApp.Worksheets("Sheet1") 
 
 
       
       
        Set L1 = xlSheet.Range("B3")[/b]  'get line 1
...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mizsydney]

thanks Skip! actually my first choice was to specify the worksheet name, but I couldn't get it to work so I reluctantly changed it back to activesheet. when I use your code, I get an object error for that line:

Set xlSheet = xlApp.Worksheets("Sheet1")

thank you again for your willingness to try to read my sloppy code.

 

[SkipVought]

'this is a WORKBOOK.  It has the Excel application object, as you cannot have  workbook without the Excel application object...
Set obj = Getobject("G:\Property Transfers\Programs\PT macros\address\multi.xls")

Set xlSheet = obj.Worksheets("Sheet1")

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mizsydney]

works perfectly! thank you, thank you, thank you. it's like magic!! you are a god. thanks again!!!