Copy certain values from MyExtra to Excel - newbie help

[pedroj012]

I'm completely new to macros and myExtra, but I just started this temp job and I'm sure it can be automated.

I type in a UPC into myExtra and a bunch of columns and rows come back with price data.

A column on the left has a price code and it corresponds with a certain type of store. The actual price of the item at that store is in the same row but further to the right.

ex.


UPC 0 245768 39348

LEVEl LVL KEY M Sell price
950 000 404 5.99
850 000 111 6.99
850 000 112 5.98
950 000 454 4.98

So I want all the "sell price" values that are at LEVEL 850 and they need to be pasted into an ecxel file with spaces that correspond to the "LVL KEY" value (LVL KEY 111 has a specific spot in the excel file along with 112, 113...etc).

I programmed in java a long time ago and it seems like something like this would work, but I don't know a whole lot about how to program macros for myextra. I'm just wondering if this is the best strategy and if this will work, and some strategies I should use....

obviously this is just pseudo java. gimme a break it was like 6 years ago.




int UPC = (input UPC);
int i = 0;

while (locationofUPC = UPC) {

//if the LEVEL on that line is 850
if (line[i, sell price] == 850) {
copy sell price to excel file spot line[i, LVL KEY];

//copy the sell price to the LVL KEY spot in excel
}
i++;

}

This also goes on for multiple screens - I don't know if I have to deal with that in the macro...

Thanks for any help

 

[SkipVought]

hi,

Extra code is a version of BASIC.

Check out the FAQs for fundamentals.

This also goes on for multiple screens - I don't know if I have to deal with that in the macro...

Use the SendKeys method.

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

 

[pedroj012]

Hey thanks for the tip. Spent a couple hours working on it today and it actually worked the first time it compiled, which blew me away.

The one issue I'm having is what you actually mentioned -
getting extra to go to the next screen.

F8 goes to the next screen in extra, and in the help file it says to enclose function keys in braces like this: {F8}, but when I did that it just typed "{F8}" into extra instead of executing the command.

I read on here that you need to put pF8 or something, but that didn't work either.

Session.Screen.SendKeys Pf8

This is the whole macro right now...

Sub Main

Dim TheUPC As Integer
Dim CurrentUPC as Integer
Dim CurrentRow as Integer
Dim Session as Object
Dim System as Object
Dim Screen as Object
Dim ExcelRow as Integer

Dim Excel As Object, ExcelWorkbook As Object
Set Excel = GetObject(, "Excel.Application")
With Excel.Worksheets("Sheet1")

ExcelRow = 167

Set System = CreateObject("EXTRA.System") 'sets all this screen shit up..like the endline macro
Set Session = System.ActiveSession
Set Screen = System.ActiveSession.Screen

TheUPC = Session.Screen.GetString (4,35,5) 'set to current UPC being worked on

CurrentUPC = TheUPC 'store it in buffer variable

while TheUPC = CurrentUPC

For CurrentRow = 8 to 18 step 1 'go through all lines of the screen

If Session.Screen.GetString (currentRow, 9, 3) = 850 then

PricePull = Screen.Getstring(currentRow, 41, 5) 'what do the options mean?
LvlPull = Screen.Getstring(currentRow, 16, 3) 'pull 111, 112, whatever

For ExcelRow = 167 to 220 step 1

if LvlPull = .Cells(ExcelRow,"B").Value then
.Cells(ExcelRow, "F").Value = PricePull '**This will place information from Extra to Excel
end if
next ExcelRow
End If

next CurrentRow 'looping of for loop

Session.Screen.SendKeys Pf8 'go to next page...might not be right syntax
CurrentUPC = Session.Screen.GetString (4,35,5) 'after next page - check UPC, ends the loop

Wend 'end of while loop

Msgbox "Macro Done." 'types this out to the user

End With

End Sub

 

[SkipVought]

Session.Screen.SendKeys("<Pf8>")  'go to next page...might not be right syntax

How do you know that there's MORE?
How do you know you are on the LAST screen?


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

 

[pedroj012]

Thanks for the reply.

That's already taken care of with the while loop. Even if that particular UPC is only one page long there's always another UPC on the next page (the database is massive and all the UPC's are one after the other). So the while loop condition just checks to see if the UPC at the top of the page matches the original UPC and cuts out of the loop if it doesn't.

The only issue I'm having is getting the screen to the next page (some UPC's span multiple pages) which just appears to be a syntax issue with F8 and Sendkeys.

I'll probably figure it out tomorrow, but if you happen to see this tonight and know the right syntax that'd save me a little time.

Thanks!

 

[SkipVought]

??? You did not read my post ???

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