×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Copy certain values from MyExtra to Excel - newbie help

Copy certain values from MyExtra to Excel - newbie help

Copy certain values from MyExtra to Excel - newbie help

(OP)
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
 

RE: Copy certain values from MyExtra to Excel - newbie help



hi,

Extra code is a version of BASIC.

Check out the FAQs for fundamentals.

Quote:

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Copy certain values from MyExtra to Excel - newbie help

(OP)
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
 

RE: Copy certain values from MyExtra to Excel - newbie help



CODE

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Copy certain values from MyExtra to Excel - newbie help

(OP)
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!

RE: Copy certain values from MyExtra to Excel - newbie help




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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close