Stop Macro when GetString is "Blank"

[Gatorsbucs]

I have a macro that runs on a loop, it is getting data from the mainframe and populating it in and Excel spreadsheet. The one problem I am having is getting the macro to move on to the “Next X” if and when it comes to “Blank Data” in the mainframe. I have the code set to go through 10 pages right now because I will never really know how much data is there for each “X”. Thanks for any help

 

[SkipVought]

Hi,

No one has visiblity to your C: drive!

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

 

[Gatorsbucs]

Global g_HostSettleTime%

Sub Main

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

g_HostSettleTime = 0100

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If

Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Dim Excel As Object
Dim Workbook As Object
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Set Workbook = Excel.Workbooks.Open("C:\Documents and Settings\xxxxxxx\Desktop\xxxxxx.xlsm")

For X = 3 to 10

If X < 10 Then
C = C + 1
End IF

With Excel.Worksheets("XXXX")
R = 10
ID = .range("A" & 3)
MN = .range("B" & X)
End with


If MN = "" Then
Msgbox "MACRO IS COMPLETE"
Workbook.Save
Excel.Quit
Exit Sub : End If


Sess0.Screen.Sendkeys("<HOME>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys(ID)
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.MoveTo 07, 15
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("x<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("XXXX")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys(MN)
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("x<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<PF11>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.MoveTo 09, 11
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("x<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

For P = 1 to 10
For Y = 10 to 19

A2 = Sess0.Screen.GetString(Y, 05, 14)
Workbook.Worksheets("XXXX").Cells(R, C).Value = A2
R = R + 1
Next Y


Sess0.Screen.Sendkeys("<PF8>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
R = R + 1
Next P


IF Y = 20 Then
Y = 10
End IF

Sess0.Screen.Sendkeys("<PF12>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<PF12>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<PF12>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<PF12>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<PF12>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)



Next X
End Sub

 

[SkipVought]

...
        A2 = Sess0.Screen.GetString(Y, 5, 14)
        if Trim(A2) = "" then Exit Sub

BUT rather than just abruptly exiting, I'd go to a finalize label and clean up my objects and shut things down in an orderly fashion.

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

 

[Gatorsbucs]

Thanks Skip...I don't think I communicated this very well..When the macro runs the first " MN = .range("B" & X) "
it may have 1 page,10 pages, or just a half page of data to gather. What I would like the code to do is not exit but move on to the next " MN = .range("B" & X) " once "Blank" data is gathered.. Thanks

 

[SkipVought]

First Excel and Workbook are RESERVE words and ought not to be used as variables...

        Dim xl As Object
        Dim wb As Object
        Set xl = CreateObject("Excel.Application")
        xl.Visible = True
        Set wb = xl.Workbooks.Open("C:\Documents and Settings\xxxxxxx\Desktop\xxxxxx.xlsm")

It seems that your outer loop ought to be the rows in your sheet.

dim r as excel.range

with wb.Worksheets("XXXX")
  for each r in range(.Cells(3, "B"),.Cells(3, "B").end(xldown))
     ID = r.offset(o,-1),value
     MN = r.value
' now do your screen thing. the loop will go no further than non-blank cells    
   next

end with

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

 

[Gatorsbucs]

Thanks Skip!