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
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
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Why doesn't this code work
CODE
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
RE: Why doesn't this code work
CODE
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!
RE: Why doesn't this code work
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
Skip,
Just traded in my old subtlety...
for a NUANCE!
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Why doesn't this code work
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Why doesn't this code work
RE: Why doesn't this code work
CODE
RE: Why doesn't this code work
WHERE are you running the OnTime statement?
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Why doesn't this code work
CODE
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
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
Application.OnTime TimeValue("10:30:00"), "Main"
end sub
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Why doesn't this code work
RE: Why doesn't this code work
i'm not able to get the ontime to work using Extra
CODE
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Why doesn't this code work
zach