Why doesn't this code work

[ptw78]

I'm running this in excel. I can't seem to figure out what is wrong. I get a debug message at this line of code

For x = 2 To Worksheet.UsedRange.Rows.Count

Sub Main()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "H:\Macros - Reports\FC Refferals Macros\120 Macro\120 Macro (4)\120 Macro Input 4.xls"
        Dim obj As Object
        Dim objWorkbook As Object
        Set obj = CreateObject("Excel.Application")
        obj.Visible = True
        obj.Workbooks.Open file



    'Do loop starting at row 2 and ending at Worksheet.UsedRange.Rows.Count
    'which is end of all rows in sheet.
    For x = 2 To Worksheet.UsedRange.Rows.Count
    
    'Set account number = to row (row x, column 1), where x = 2 for the first loop and going
    'all the way to the end of the count of rows, so on the second loop, x would be 3, so
    'it would read the account from row 3, column 1.  It starts at 2 because it assumes there
    'is a header row on the worksheet, you can change it in the above for statement
    str_act_num = Worksheet.Cells(x, 1)
  

  'moves cursor to location 7,30
    Sess0.Screen.MoveTo 7, 30
    

    
    'send acct num string and erases end and enters
    Sess0.Screen.SendKeys ("x")
    Sess0.Screen.MoveTo 10, 21
    Sess0.Screen.SendKeys (str_act_num & "<EraseEOF><Enter>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    
    
    
    If Sess0.Screen.GetString(23, 2, 17) = "ACCOUNT NOT FOUND" Then
        
        str_status = "ACCOUNT NOT FOUND"
     
        Else
        
        str_status = "ACCOUNT FOUND"
            

       str_reason = Sess0.Screen.GetString(21, 26, 8)
       Worksheet.Cells(x, 4) = str_reason
       
       str_reason = Sess0.Screen.GetString(21, 44, 1)
       Worksheet.Cells(x, 2) = str_reason
       
       str_reason = Sess0.Screen.GetString(4, 16, 8)
       Worksheet.Cells(x, 5) = str_reason
       
       str_reason = Sess0.Screen.GetString(12, 55, 13)
       Worksheet.Cells(x, 6) = str_reason
        
       str_reason = Sess0.Screen.GetString(12, 14, 3)
       Worksheet.Cells(x, 7) = str_reason
      
       
       Sess0.Screen.SendKeys ("<pf3>")
       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
       
       
       End If
 
  
   Worksheet.Cells(x, 3) = str_status
   
Next
 
    MsgBox "Macro Done"
    
End Sub

 

[SkipVought]

Hi,

Worksheet is an object in Excel. But WHICH ONE in your code?

Sub Main()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "H:\Macros - Reports\FC Refferals Macros\120 Macro\120 Macro (4)\120 Macro Input 4.xls"
        Dim obj As Object
        Dim objWorkbook As Object
        Set obj = CreateObject("Excel.Application")
        obj.Visible = True
        obj.Workbooks.Open file
[b]
'you now have an ActiveSheet in the workbook you just opened.
'do you REALLY KNOW what sheet that is?
'it depends what sheet was active when the workbook was last saved.
'do you want your code to depend on THAT???
'I certainly would NOT!
'here's what I would do...
[/b]
   Dim ws as worksheet
   set ws Activeworkbook.sheets("TheSheetYouWantToWorkOn")

   For x = 2 To ws.UsedRange.Rows.Count

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

Ok got it working but a few annoyances. It opens up another workbook but does not but anything in it. It does however get the data i need in my workbook where the marcro is. But while doing that it seems excel kind of freezes, meaning i cannot scroll down and look at the status of where it's at or anything. I also noticed that the first 10 or so row don't get filled in, until the macro is done. Like it got the data just waited to put it in the cells i needed until the end. Here is my code now.

Sub Main()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "H:\Macros - Reports\FC Refferals Macros\120 Macro\120 Macro (4)\120 Macro Input 4.xls"
        Dim obj As Object
        Dim objWorkbook As Object
        Set obj = CreateObject("Excel.Application")
        obj.Visible = True
        obj.Workbooks.Open file
  Dim ws As Worksheet
   Set ws = ActiveWorkbook.Sheets("Sheet1")



    'Do loop starting at row 2 and ending at Worksheet.UsedRange.Rows.Count
    'which is end of all rows with data in the sheet.
    For x = 2 To ws.UsedRange.Rows.Count
    
    'Set account number = to row (row x, column 1), where x = 2 for the first loop and going
    'all the way to the end of the count of rows, so on the second loop, x would be 3, so
    'it would read the account from row 3, column 1.  It starts at 2 because it assumes there
    'is a header row on the worksheet, you can change it in the above for statement
    str_act_num = ws.Cells(x, 1)
  

  'moves cursor to location 7,30
    Sess0.Screen.MoveTo 7, 30
    

    
    'send acct num string and erases end and enters
    Sess0.Screen.SendKeys ("x")
    Sess0.Screen.MoveTo 10, 21
    Sess0.Screen.SendKeys (str_act_num & "<EraseEOF><Enter>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    
    
    
    If Sess0.Screen.GetString(23, 2, 17) = "ACCOUNT NOT FOUND" Then
        
        str_status = "ACCOUNT NOT FOUND"
     
        Else
        
        str_status = "ACCOUNT FOUND"
            

       str_reason = Sess0.Screen.GetString(21, 26, 8)
       ws.Cells(x, 4) = str_reason
       
       str_reason = Sess0.Screen.GetString(21, 44, 1)
       ws.Cells(x, 2) = str_reason
       
       str_reason = Sess0.Screen.GetString(4, 16, 8)
       ws.Cells(x, 5) = str_reason
       
       str_reason = Sess0.Screen.GetString(12, 55, 13)
       ws.Cells(x, 6) = str_reason
        
       str_reason = Sess0.Screen.GetString(12, 14, 3)
       ws.Cells(x, 7) = str_reason
      
       
       Sess0.Screen.SendKeys ("<pf3>")
       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
       
       
       End If
 
  
   ws.Cells(x, 3) = str_status
   
Next x
 
    MsgBox "Macro Done"
    
End Sub

 

[ptw78]

correction, it doesn't skip the first 10 or so rows. It goes through all the rows but only places a small amount of the data then at the end of the macro being ran all the data is put on the remaining columns instantly pretty much. But it still doesn't allow me to scroll down and view anything, i get the hour glass.

 

[SkipVought]

  Dim wb as workbook
  Set wb = obj.Workbooks.Open(file)
  Dim ws As Worksheet
  Set ws = wb.Sheets("Sheet1")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

Ok everything runs ok, but I have small issue say i have 50 rows all w/data i run it then say a day later i put in 35 rows w/data over that 50, and just delete the data in the remaing 15(not delete the rows, just clear the cells out)
i go to run it that second time with 35 rows of data but it runs to 50, the last 15 are just random crap. It's like it's thinking there is still data in the remaining 15 row and cells, when are deleted. Can that be fixed? If I go and manually delete the rows where there was old data it will work, but I may use 1000 rows one day and 8000 another day.

 

[SkipVought]

Either delete the rows, or code this, assuming that ALL YOUR DATA IS CONTIGUOUS and headings begin in Row 1 Column A...

For x = 2 To ws.Cells(1,1).CurrentRegion.Rows.Count

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, I rarely use UsedRange simply because of the well-documented behavior your described.

My tables are always contiguous, so I regularly use CurrentRegion.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

got it, thx for your help

 

[SkipVought]

BTW,

Extra Basic is a poor cousin of VBA. I do ALL my Extra coding in Excel VBA, because that is where my source data resides and where my results are written.

For help with the Excel Object Model and VBA coding, check out forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

will do, thx for your help Skip.

 

[ptw78]

If i was wanting to run this at a certain time in the morning how would I do that? I put in

 Application.OnTime TimeValue("10:30:00"), "Main"

But that doesn't work. It starts automatically as soon as it run the macro.

 

[SkipVought]

WHERE are you running the OnTime statement?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

at the beginning like this

Sub Main()

Application.OnTime TimeValue("10:30:00"), "Main"

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

but I also tried as a seperate Sub like this and I got Ambiougs name detected "Main"

Sub runTime()
Application.OnTime TimeValue("10:30:00"), "Main"
End Sub

Sub Main()
'code......
End Sub

 

[SkipVought]

NO NO NO.

sub ScheduleMain()
  Application.OnTime TimeValue("10:30:00"), "Main"
end sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

got it, thx

 

[vzachin]

hi skip and ptw78,

i'm not able to get the ontime to work using Extra

sub ScheduleMain()
  [blue]Application.OnTime TimeValue("10:30:00"), "Main"[/blue]
end sub

Sub Main()
'code......
End Sub

any ideas?

thanks
zach

 

[SkipVought]

Hi,

New questions belong in new threads.

When you post your new thread, please include the application. Extra has no OnTime method AFAIK.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vzachin]

thanks skip. i will post in new thread. but you already answered the question.

zach