×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Why doesn't this code work

thread99-1588194

Why doesn't this code work

Why doesn't this code work

(OP)
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

CODE

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

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



    '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


 

RE: Why doesn't this code work



Hi,

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

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

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

   Dim ws as worksheet
   set ws Activeworkbook.sheets("TheSheetYouWantToWorkOn")

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Why doesn't this code work

(OP)
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.

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

RE: Why doesn't this code work

(OP)
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.

RE: Why doesn't this code work



CODE

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Why doesn't this code work

(OP)
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.

RE: Why doesn't this code work




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

CODE

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Why doesn't this code work




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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Why doesn't this code work

(OP)
got it, thx for your help

RE: Why doesn't this code work



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: VBA Visual Basic for Applications (Microsoft).

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Why doesn't this code work

(OP)
will do, thx for your help Skip.

RE: Why doesn't this code work

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

CODE

Application.OnTime TimeValue("10:30:00"), "Main"
But that doesn't work. It starts automatically as soon as it run the macro.

RE: Why doesn't this code work




WHERE are you running the OnTime statement?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Why doesn't this code work

(OP)
at the beginning like this

CODE

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"

CODE

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

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

RE: Why doesn't this code work




NO NO NO.

CODE

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

 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Why doesn't this code work

(OP)
got it, thx
 

RE: Why doesn't this code work

hi skip and ptw78,

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

CODE

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

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

any ideas?

thanks
zach

RE: Why doesn't this code work




Hi,

New questions belong in new threads.

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Why doesn't this code work

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

zach

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close