Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

select case statement

Status
Not open for further replies.
ram567

ram567

Programmer
Dec 28, 2007
123
US
could you help me the below one
what i am trying to do if the account- id <> 20.xxxx, yyyy
it will write into excel
what happens here the below code it shows including 20, xxxx,yyyy and journla id kkkkkk, demo


Select case Account_id
case "20", "xxxxx", "yyyyy"

case Journalid
case "kkkkkkkk", "DEMO"


case else


xl_sheet.Cells(j, "A").Value = Account_id
xl_sheet.Cells(j, "B").Value = Journalid

End Select

 
Sort by date Sort by votes



Code: 
    Select Case Account_id
        Case "20", "xxxxx", "yyyyy"
            'nothing happens
        Case Journalid
            'nothing happens
        Case "kkkkkkkk", "DEMO"
             'nothing happens
       Case Else
            xl_sheet.Cells(j, "A").Value = Account_id
            xl_sheet.Cells(j, "B").Value = Journalid
    End Select
If Account_id equals any of those values, nothing happens. OTHERWISE do the Else.

Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Upvote 0 Downvote
Skip
thanks for your help this one
still writing the below one. though it has in case . it should not be.
"kkkkkkkk", "DEMO"
 
Upvote 0 Downvote




I do not understand your problem.

Please be CLEAR, CONCISE & COMPLETE.

Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Upvote 0 Downvote

Skip
i thing it is not problem with select case
the coding problem coudl you see that what is the problem it
actualy it happens if it is DEMO IN THAT PAGE IT DOES NOT WRITE AFTER DEMO THAT WHOLE PAGE IS NOT WRITING IN EXCEL

Declare Sub Wait(Sess As Object)

Sub Main()
Dim Sys As Object, Sess As Object

Set Sys = CreateObject("Extra.System")

If Sys Is Nothing Then
MsgBox ("Could not create Extra.System...is E!PC installed on this machine?")
Exit Sub
End If

Set Sess = Sys.ActiveSession

If Sess Is Nothing Then
MsgBox ("No session available...stopping macro playback.")
Exit Sub
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Const xlFilterCopy As Integer = 2

Dim xl As Object, xl_wb As Object, xl_sheet As Object, file_name As String
Dim account As String, part_no As String, part_name As String, part_key As String
Dim i As Integer, j As Long

file_name = "C:\testRAM567.xls"

Set xl = CreateObject("Excel.Application")
Set xl_wb = xl.Workbooks.Open(file_name)
Set xl_sheet = xl_wb.Sheets("Sheet1")

xl.Visible = True
xl.DisplayAlerts = False

xl_sheet.Range("A:A").EntireRow.Font.Size = 12
xl_sheet.Columns("A").ColumnWidth = 13
xl_sheet.Columns("B").ColumnWidth = 16
xl_sheet.Columns("C").ColumnWidth = 27
xl_sheet.Columns("D").ColumnWidth = 17

xl_sheet.Cells(1, 1) = "ACCOUNTS"
xl_sheet.Cells(1, 2) = "PARTNUMBER"
xl_sheet.Cells(1, 3) = "PARTNAME"
xl_sheet.Cells(1, 4) = "PARTKEY"

j = 2

Do
For i = 6 To 23
account = Trim(Sess.Screen.GetString(i, 11, 5))
part_no = Trim(Sess.Screen.GetString(i, 22, 10))
part_name = Trim(Sess.Screen.GetString(i, 35, 20))
part_key = Trim(Sess.Screen.GetString(i, 59, 10))
Select Case account
Case "20", "xxxxx", "yyyyy"
'nothing happens
Case part_no
'nothing happens
Case "kkkkkkkk", "DEMO"
'nothing happens
Case Else


xl_sheet.Cells(j, "A").Value = account
xl_sheet.Cells(j, "B").Value = part_no
xl_sheet.Cells(j, "C").Value = part_name
xl_sheet.Cells(j, "D").Value = part_key
j = j + 1
End Select
Next

Sess.Screen.SendKeys ("<PF8>")
Call Wait(Sess)
Loop Until Ucase(Sess.Screen.GetString(24, 1, 9)) <> "LAST PAGE"

xl_sheet.Cells.Sort Key1:=xl_sheet.Range("D1"), Header:=1
xl_sheet.Range("B2:B109").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=xl_sheet.Range("G1"), Unique:=True

xl_wb.Save
xl_wb.Close
xl.Quit

Set xl_sheet = Nothing
Set xl_wb = Nothing
Set xl = Nothing
Set Sess = Nothing
Set Sys = Nothing
End Sub

Sub Wait(Sess As Object)
Do While Sess.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End Sub




 
Upvote 0 Downvote



still writing the below one. though it has in case . it should not be.
"kkkkkkkk", "DEMO"
Click to expand...
Then your STRINGS are not consistent. Try using TRIM
Code: 
    Select Case [b]Trim(Account_id)[/b]
        Case "20", "xxxxx", "yyyyy"
            'nothing happens
        Case Journalid
            'nothing happens
        Case "kkkkkkkk", "DEMO"
             'nothing happens
       Case Else
            xl_sheet.Cells(j, "A").Value = Account_id
            xl_sheet.Cells(j, "B").Value = Journalid
    End Select


Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Upvote 0 Downvote
Skip
it is working now. thank you so much.
 
Upvote 0 Downvote
Skip
it workes fine
but it does not write the last page
THE BELOW CODE I TRIED THIS ONE
Loop Until Ucase(Sess.Screen.GetString(24, 1, 11)) = "END OF LIST"
AND
Loop WHILE Ucase(Sess.Screen.GetString(24, 1, 11)) <> "END OF LIST"
I TRIED BOTH CODES. BUT STILL THE LAST PAGE I HAVE SOME . IT IS NOT WRITING IN EXCEL . COULD YOU SEE THAT. THANKS IN ADVANCE.
 
Upvote 0 Downvote
That's because once your code hits the page with "END OF LIST" it ends the loop. This results in the last page of data not being pulled.

Code: 
Do While TRUE
  For i = 6 To 23
    account   = Trim(Sess.Screen.GetString(i, 11, 5))
    part_no   = Trim(Sess.Screen.GetString(i, 22, 10))
    part_name = Trim(Sess.Screen.GetString(i, 35, 20))
    part_key  = Trim(Sess.Screen.GetString(i, 59, 10))
    Select Case account
      Case "20", "xxxxx", "yyyyy"
        'nothing happens
      Case part_no
        'nothing happens
      Case "kkkkkkkk", "DEMO"
        'nothing happens
      Case Else
        xl_sheet.Cells(j, "A").Value = account
        xl_sheet.Cells(j, "B").Value = part_no
        xl_sheet.Cells(j, "C").Value = part_name
        xl_sheet.Cells(j, "D").Value = part_key
        j = j + 1
   End Select  
  Next
  If UCase(Sess.Screen.GetString(24, 1, 9)) = "LAST PAGE" Then
    Exit Do
  Else
    Sess.Screen.SendKeys ("<PF8>")
    Call Wait(Sess)
  End If
Loop
 
Upvote 0 Downvote




Have you stepped thru your code and observed the VALUES of various objects and variables?

Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Upvote 0 Downvote
If it doesn't stop, then you'll want to step through the red section.

If you add the green code it'll exit the loop after 50 loops if the code in red is never executes.

If you add the blue code, then it'll stop everytime it reaches that point. This allows you to run your code until the point where you want to step through it to see what it's doing.

Code: 
[green]Dim iLoop As Integer
iLoop = 0
Do While iLoop < 50[/green]
  For i = 6 To 23
    account   = Trim(Sess.Screen.GetString(i, 11, 5))
    part_no   = Trim(Sess.Screen.GetString(i, 22, 10))
    part_name = Trim(Sess.Screen.GetString(i, 35, 20))
    part_key  = Trim(Sess.Screen.GetString(i, 59, 10))
    Select Case account
      Case "20", "xxxxx", "yyyyy"
        'nothing happens
      Case part_no
        'nothing happens
      Case "kkkkkkkk", "DEMO"
        'nothing happens
      Case Else
        xl_sheet.Cells(j, "A").Value = account
        xl_sheet.Cells(j, "B").Value = part_no
        xl_sheet.Cells(j, "C").Value = part_name
        xl_sheet.Cells(j, "D").Value = part_key
        j = j + 1
   End Select  
  Next
  [blue]Stop[/blue]
[red]  If UCase(Sess.Screen.GetString(24, 1, 9)) = "LAST PAGE" Then
    Exit Do[/red]
  Else
[green]    iLoop = iLoop + 1[/green]
    Sess.Screen.SendKeys ("<PF8>")
    Call Wait(Sess)
  End If
Loop
 
Upvote 0 Downvote
Skie
it stopped here
If UCase(Sess.Screen.GetString(24, 1, 9)) = "LAST PAGE" Then
Exit Do
it is not last page it is END- OF -LIST
AND I CHANGED GETSTRING (24,1,11)
NOW IT WORKS FINE
THANKS A LOT
 
Upvote 0 Downvote
Skip
thanks a lot. Too much spinning my head. you relived it so much.
 
Upvote 0 Downvote



If the ONLY text on row 24 is the screen message, then you could use something like this to be more inclusive...
Code: 
If Trim(UCase(Sess.Screen.GetString(24, 1, 80))) = "LAST PAGE" Then


Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Upvote 0 Downvote
Or if it's not the only text:
Code: 
If InStr(UCase(Sess.Screen.GetString(24, 1, 80)), "LAST PAGE") Then
 
Upvote 0 Downvote



Si!

Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Crash171
  • Locked
  • Question
Bug Problem. &quot;Illegal Statement&quot; Help!!!!!
Replies
2
Views
80
SkipVought
S
jay3120
  • Locked
  • Question
Send data from Attachmate Extra to current selected cell in Excel
2
Replies
22
Views
203
jay3120
jay3120
DizzyP
  • Locked
  • Question
Select text 2
2
Replies
22
Views
290
Magyar5
Magyar5
jfly25
  • Locked
  • Question
7.1a macro to select text?
Replies
10
Views
116
jonesy1
jonesy1
DwMcC
  • Locked
  • Question
For and Next statements must be in same block
Replies
2
Views
52
DwMcC
DwMcC

Part and Inventory Search

Sponsor

Back
Top