need a little help with my VBA coding 2

[VbaNewbie08]

im having problems with the ** areas below.

I apologize im a newbie when it comes to this kind of stuff

Sub Main()

'Extra Objects
Dim System As Object
Dim ExScreen As Object
Set System = CreateObject("EXTRA.System")
Set ExScreen = System.ActiveSession.Screen

'Excel Objects
Dim appExcel As Object
Dim wbExcel As Object
Dim aSheet As Object

Set appExcel = CreateObject("Excel.Application")
Set wbExcel = appExcel.Workbooks.Open("P:\Accounts_Payable\Admin\Extra.xls")
Set aSheet = wbExcel.Sheets("Main")

For x = 1 To aSheet.UsedRange.Rows.Count

'ExScreen.SendKeys ("<clearpagecursorhome>")
ExScreen.PutString aSheet.Cells(2, 1).Text, 4, 34
ExScreen.PutString aSheet.Cells(2, 2).Text, 6, 34
ExScreen.PutString aSheet.Cells(2, 3).Text, 8, 34
ExScreen.PutString aSheet.Cells(2, 4).Text, 10, 34
ExScreen.PutString aSheet.Cells(2, 5).Text, 12, 34
ExScreen.PutString aSheet.Cells(2, 6).Text, 13, 34
ExScreen.PutString aSheet.Cells(2, 7).Text, 14, 34
ExScreen.PutString aSheet.Cells(2, 8).Text, 15, 34
ExScreen.PutString aSheet.Cells(2, 9).Text, 16, 34
ExScreen.SendKeys ("<Enter>")
ExScreen.WaitHostQuiet (xtraSettleTime)
**at this point if there is an error(s) it will show up in 23,12 thru 23,37 (error 1)and 24,12 thru 24,37 (error 2). I need it to copy error 1 to excel into column 28 and error 2 into column 29, then start over with the next row***
ExScreen.PutString aSheet.Cells(2, 10).Text, 12, 10
ExScreen.PutString aSheet.Cells(2, 11).Text, 12, 18
ExScreen.PutString aSheet.Cells(2, 12).Text, 12, 31
ExScreen.PutString aSheet.Cells(2, 13).Text, 12, 47
ExScreen.PutString aSheet.Cells(2, 14).Text, 12, 53
ExScreen.PutString aSheet.Cells(2, 15).Text, 12, 60
ExScreen.PutString aSheet.Cells(2, 16).Text, 12, 72
ExScreen.PutString aSheet.Cells(2, 17).Text, 15, 2
ExScreen.PutString aSheet.Cells(2, 18).Text, 15, 11
ExScreen.PutString aSheet.Cells(2, 5).Text, 15, 20
ExScreen.PutString aSheet.Cells(2, 6).Text, 15, 33
ExScreen.PutString aSheet.Cells(2, 8).Text, 15, 44
ExScreen.PutString aSheet.Cells(2, 19).Text, 15, 66
ExScreen.PutString aSheet.Cells(2, 20).Text, 15, 73
ExScreen.PutString aSheet.Cells(2, 21).Text, 18, 2
ExScreen.PutString aSheet.Cells(2, 22).Text, 18, 10
ExScreen.PutString aSheet.Cells(2, 23).Text, 18, 16
ExScreen.PutString aSheet.Cells(2, 24).Text, 18, 30
ExScreen.PutString aSheet.Cells(2, 25).Text, 18, 41
ExScreen.PutString aSheet.Cells(2, 26).Text, 18, 51
ExScreen.SendKeys ("<Enter>")
**at this point if there is an error(s) it will show up in 23,12 thru 23,37 (error 3)and 24,12 thru 24,37 (error 4). I need it to copy the error 3 to excel into column 30 and error 4 into column 31.***
**if no erros at this point i need it to copy a ref number generated by extra (24/29 thru 24/36) into column 27 in excel**
**and then it needs to loop until it hits a blank row***
Next

appExcel.Quit
End Sub

 

[SkipVought]

Hi,

First off, you're not using your LOOP variable...

    For x = 1 To aSheet.UsedRange.Rows.Count
  
        'ExScreen.SendKeys ("<clearpagecursorhome>")
        ExScreen.PutString aSheet.Cells([red][b]x[/b][/red], 1).Text, 4, 34
'...

"...and then it needs to loop until it hits a blank row"

WHY? You already have defined the end of the loop as aSheet.UsedRange.Rows.Count.

Check out GetString for reading your errors.


Skip,

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

 

[VbaNewbie08]

Skip,

The loop has been fixed. Thanks. Can you help with the other 2 issues or point me in the right direction?

 

[SkipVought]

exactly what?

Skip,

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

 

[VbaNewbie08]

I copied that code from a previous post on this site, and edited to fit my scenario. The problem is I don’t know much about writing, I mainly know how record and edit. So i'm not sure how to get the "GetString" working

 

[SkipVought]

Did you consult Help?

Did you try anything that did not work?

What did you try?

What did happen?

Skip,

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

 

[vzachin]

try something like this:
assuming there is a specific error that is the same each time:

error1 = Trim(ExScreen.GetString (23,12,26))
error2 = Trim(ExScreen.GetString (24,12,26))
if error1 = "whateveryourerroris" and _
if error2 = "whateveryourerroris" then
aSheet.Cells(28, ??) = error1
aSheet.Cells(29, ??) = error2
end if

if there is no error, are these fields blank? then try

if error1 <> "" and error2 <> "" then...

i always get confused with the proper syntax for .cells(nn,nn)
but i'm sure you can work that out

hth

 

[VbaNewbie08]

vzachin,

Thanks for the post. I think were moving in the right direction, but here's the scenario.

1. You enter data on screen 1 and hit enter
A. If there is no error it will go to the next screen so you can finsh keying the invoice.
B. If there is an error it will stay on the first screen and display an error at the bottom. If this happens i need it to copy the error message(s) to the same row in excel but in the error columns 1 & 2. Then it needs to skip that row and go to the next.

2. If there was no error on the first screen it will send you to the invoice detail screen to finish keying.
A. once finished hit enter if there is no error it will take you back to the first screen so you can key another invoice. Also there is a reference # at the bottom of this screen for the inv that was keyed, this needs to be copied to excel same row in the reference column. and continue to the next row.
B.If there is an error it needs to copy the error message(s) to error columns 3 & 4. Next it needs to send key "pause break". then skip the row and go to the next row.

 

[VbaNewbie08]

sorry i used the wrong url tag for the photo, here is the correct one.

 

[vzachin]

hi,

my assuption is:
1st screen = D A T A E N T R Y
2nd screen = CREDIT MEMO/PAYBACK

use the GetString to figure out which screen you're on.
GetString before ENTER & GetString after ENTER to compare.

code logic is something like this:
from the 1st screen, hit enter :
if screen <> CREDIT MEMO/PAYBACK then
there is an error because the screen did not change.
use GetString to get the error messages and pass it back to Excel.
if screen = CREDIT MEMO/PAYBACK then
there is no error on previous screen and it will continue keying on the 2nd screen.

from the second screen, repeat the logic from screen 1:
after enter:
if screen <> D A T A E N T R Y then
there is an error because the screen did not change.
use GetString to get the error messages and pass it back to Excel.
Then Send <"PauseBreak"> key
'my assumption is <"PauseBreak"> will call up screen 1.

what does your code look like so far?

hth

 

[VbaNewbie08]

Here is what my code looks like so far. The last thing i need help with is getting it to start the loop over on the next row in excel after it has found an error see #*#*#*# below

Sub Main()

'***************************
Dim warn As Integer ' No = 7 Yes = 6
warn = MsgBox("Are you sure you want to run this report?", vbCritical + vbYesNo + vbDefaultButton2, "Run Report")

If warn = 7 Then
Exit Sub
Else
End If
'***************************

'Extra Objects
Dim System As Object
Dim ExScreen As Object
Set System = CreateObject("EXTRA.System")
Set ExScreen = System.ActiveSession.screen

'Excel Objects
Dim appExcel As Object
Dim wbExcel As Object
Dim aSheet As Object

Set appExcel = CreateObject("Excel.Application")
Set wbExcel = ActiveWorkbook
Set aSheet = wbExcel.Sheets("Main")

Error1 = Trim(ExScreen.GetString(4, 16, 4))
Error2 = Trim(ExScreen.GetString(3, 2, 4))



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


ExScreen.PutString aSheet.Cells(x, 1).Text, 4, 34
ExScreen.PutString aSheet.Cells(x, 2).Text, 6, 34
ExScreen.PutString aSheet.Cells(x, 3).Text, 8, 34
ExScreen.PutString aSheet.Cells(x, 4).Text, 10, 34
ExScreen.PutString aSheet.Cells(x, 5).Text, 12, 34
ExScreen.PutString aSheet.Cells(x, 6).Text, 13, 34
ExScreen.PutString aSheet.Cells(x, 7).Text, 14, 34
ExScreen.PutString aSheet.Cells(x, 8).Text, 15, 34
ExScreen.PutString aSheet.Cells(x, 9).Text, 16, 34
ExScreen.SendKeys ("<Enter>")
ExScreen.WaitHostQuiet (1500)
'**************************
If Error1 = "DUNS" Then
aSheet.Cells(x, 28) = Trim(ExScreen.GetString(23, 12, 28))
aSheet.Cells(x, 29) = Trim(ExScreen.GetString(24, 12, 28))
aSheet.Cells(x, 30) = Trim(ExScreen.GetString(23, 47, 28))
aSheet.Cells(x, 31) = Trim(ExScreen.GetString(24, 47, 28))
ExScreen.SendKeys ("<Clear>")
ExScreen.PutString "01", 18, 46
ExScreen.SendKeys ("<Enter>")

#*#*#*# Here is where i need to to start the loop over on the next row in excel b/c the error will not let us progress to the next screen.#*#*#*#

End If
'**************************
ExScreen.PutString aSheet.Cells(x, 10).Text, 12, 10
ExScreen.PutString aSheet.Cells(x, 11).Text, 12, 18
ExScreen.PutString aSheet.Cells(x, 12).Text, 12, 31
ExScreen.PutString aSheet.Cells(x, 13).Text, 12, 47
ExScreen.PutString aSheet.Cells(x, 14).Text, 12, 53
ExScreen.PutString aSheet.Cells(x, 15).Text, 12, 60
ExScreen.PutString aSheet.Cells(x, 16).Text, 12, 72
ExScreen.PutString aSheet.Cells(x, 17).Text, 15, 2
ExScreen.PutString aSheet.Cells(x, 18).Text, 15, 11
ExScreen.PutString aSheet.Cells(x, 5).Text, 15, 20
ExScreen.PutString aSheet.Cells(x, 6).Text, 15, 33
ExScreen.PutString aSheet.Cells(x, 8).Text, 15, 44
ExScreen.PutString aSheet.Cells(x, 19).Text, 15, 66
ExScreen.PutString aSheet.Cells(x, 20).Text, 15, 73
ExScreen.PutString aSheet.Cells(x, 21).Text, 18, 2
ExScreen.PutString aSheet.Cells(x, 22).Text, 18, 10
ExScreen.PutString aSheet.Cells(x, 23).Text, 18, 16
ExScreen.PutString aSheet.Cells(x, 24).Text, 18, 30
ExScreen.PutString aSheet.Cells(x, 25).Text, 18, 41
ExScreen.PutString aSheet.Cells(x, 26).Text, 18, 51
ExScreen.SendKeys ("<Enter>")

'**************************
If Error2 = "DUNS" Then
aSheet.Cells(x, 32) = Trim(ExScreen.GetString(23, 12, 28))
aSheet.Cells(x, 33) = Trim(ExScreen.GetString(24, 12, 28))
aSheet.Cells(x, 34) = Trim(ExScreen.GetString(23, 47, 28))
aSheet.Cells(x, 35) = Trim(ExScreen.GetString(24, 47, 28))
ExScreen.SendKeys ("<Clear>")
Else
aSheet.Cells(x, 27) = Trim(ExScreen.GetString(24, 29, 8))

#*#*#*# Here is where i need to to start the loop over on the next row in excel b/c the error will not let us progress to the next screen.#*#*#*#

End If
'**************************

Next

appExcel.Quit

'***************************
MsgBox "Macro Finished"
'***************************
End Sub

 

[SkipVought]

Exactly what is the error message and on exactly what stement?

Skip,

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

 

[VbaNewbie08]

Skip,

im not recieving an error, just not sure how to write the loop situtaion into each if statement.

 

[SkipVought]

#*#*#*# Here is where i need to to start the loop over on the next row in excel b/c the [red]error[/red] will not let us progress to the next screen.#*#*#*#

im not recieving an error

Which VbaNewbie08 should i believe???

Skip,

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

 

[VbaNewbie08]

Skip,

I missed understood your previous statement. I guess you should not trust either one of me since i am no longer here mentally. Now back to subject, i will try again explain in detail.


Once you hit enter the if statement will check to see if extra proceded to screen 2. If it proceded to screen 2 it will keep keying. If it satyed on screen 1 it will pull the error message into excel. (this is error im refering too, not an actual macro error). Then is needs to start the loop over on the next row.

ExScreen.SendKeys ("<Enter>")
ExScreen.WaitHostQuiet (1500)
'**************************

If Error1 = "DUNS" Then
aSheet.Cells(x, 28) = Trim(ExScreen.GetString(23, 12, 28))
aSheet.Cells(x, 29) = Trim(ExScreen.GetString(24, 12, 28))
aSheet.Cells(x, 30) = Trim(ExScreen.GetString(23, 47, 28))
aSheet.Cells(x, 31) = Trim(ExScreen.GetString(24, 47, 28))
ExScreen.SendKeys ("<Clear>")
ExScreen.PutString "01", 18, 46
ExScreen.SendKeys ("<Enter>")

 

[SkipVought]

If YourErrorCondition Then x = x + 1

Skip,

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

 

[VbaNewbie08]

Skip/vzachin,

I just wanted to say thanks for all your help. I was able to complete and test the macro yesterday. The test was a complete success!!