paper: New Scouting Database from Team 2834

[Ed Law]

Thread created automatically to discuss a document in CD-Media.

New Scouting Database from Team 2834 by Ed Law

[Ed Law]

I try to upload the Scouting Database but it says my file is too big. I will try to reduce the size but I will have to delete some of the data.

[Ed Law]

I split the database into two Excel file. You can stitch it back together if you want or just use part 1 without part 2.

[billbo911]

Originally Posted by Ed Law

I split the database into two Excel file. You can stitch it back together if you want or just use part 1 without part 2.

What size would the entire file be if you zipped it first?

[R.C.]

Originally Posted by billbo911

What size would the entire file be if you zipped it first?

If you email it to me I could upload it for you

[Ed Law]

Originally Posted by billbo911

What size would the entire file be if you zipped it first?

Thanks for reminding me of that option. It reduced it down to 1.4 Mb. I uploaded the zip file but now I can not delete the two files that were split into part 1 and part 2.

[Clinton Bolinger]

Very nice database of OPR and CCWM.

Got a few suggestions that might improve your excel file.

In order to make the getpicture sub to work every time a new query is requested, change your getpicture sub to the following functions:

Function getpicture(teamnum As String) As Boolean
    Dim filen as String
    
    If ActiveSheet.Name = "Query" Then
   
    Else
     GoTo Done
    End If

    Dim AC As Range
    Static P As Shape
    On Error GoTo Done
    Set AC = Application.Caller
    If PicExists(P) Then
    P.Delete
    Else
    'look for a picture already over cell
    For Each P In ActiveSheet.Shapes
    If P.Type = msoLinkedPicture Then
    If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
    If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
    P.Delete
    Exit For
    End If
    End If
    End If
    Next P
    End If
    filen = CurDir + "\" + Format(teamnum) + ".jpg"
    Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.75, AC.Top + 5, 329.25, 247.5)
    getpicture  = True
    Exit Function
Done:
    getpicture  = False
End Function

Function PicExists(P As Shape) As Boolean
    'Return true if P references an existing shape
    Dim ShapeName As String
    On Error GoTo NoPic
    If P Is Nothing Then GoTo NoPic
    ShapeName = P.Name
    PicExists = True
NoPic:
    PicExists = False
End Function

As for calling the function add the following to "I5":

=getpicture(B2)

Finally to make the query worksheet more user friendly, I would "unlock" cell B2. You can do this by right clicking on B2, Format Cells..., Click Protection, and uncheck Lock. Then you will want to protect the worksheet by going to Tools>Protection>Protect Sheet.. then add a password if you like and uncheck select locked cells. That way the only selectable cell would be "B2" on the Query worksheet.

Hope that helps and Thanks for the information.
-Oris-

[Ed Law]

Originally Posted by Oris

Very nice database of OPR and CCWM.

Got a few suggestions that might improve your excel file.

In order to make the getpicture sub to work every time a new query is requested, change your getpicture sub to the following functions:

Function getpicture(teamnum As String) As Boolean
    Dim filen as String
    
    If ActiveSheet.Name = "Query" Then
   
    Else
     GoTo Done
    End If

    Dim AC As Range
    Static P As Shape
    On Error GoTo Done
    Set AC = Application.Caller
    If PicExists(P) Then
    P.Delete
    Else
    'look for a picture already over cell
    For Each P In ActiveSheet.Shapes
    If P.Type = msoLinkedPicture Then
    If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
    If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
    P.Delete
    Exit For
    End If
    End If
    End If
    Next P
    End If
    filen = CurDir + "\" + Format(teamnum) + ".jpg"
    Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.75, AC.Top + 5, 329.25, 247.5)
    getpicture  = True
    Exit Function
Done:
    getpicture  = False
End Function

Function PicExists(P As Shape) As Boolean
    'Return true if P references an existing shape
    Dim ShapeName As String
    On Error GoTo NoPic
    If P Is Nothing Then GoTo NoPic
    ShapeName = P.Name
    PicExists = True
NoPic:
    PicExists = False
End Function

As for calling the function add the following to "I5":

=getpicture(B2)

Finally to make the query worksheet more user friendly, I would "unlock" cell B2. You can do this by right clicking on B2, Format Cells..., Click Protection, and uncheck Lock. Then you will want to protect the worksheet by going to Tools>Protection>Protect Sheet.. then add a password if you like and uncheck select locked cells. That way the only selectable cell would be "B2" on the Query worksheet.

Hope that helps and Thanks for the information.
-Oris-

Hi Oris,

Wow, thanks for the code. I spent a little bit of time but couldn't figure out how to do it yet. I will try it out this weekend and repost the database.

Does anybody know why I can not delete the two files that I don't need any more now that I am zipping the file to make it smaller?

Ed

[Clinton Bolinger]

Forgot one thing about protecting the worksheet, you will also have to check the "Edit Objects" box (scroll down 2nd from the bottom). This will allow for the "I8" robot picture to update correctly.

Look forward to seeing the new version.
-Oris-

[Ed Law]

Originally Posted by Oris

Forgot one thing about protecting the worksheet, you will also have to check the "Edit Objects" box (scroll down 2nd from the bottom). This will allow for the "I8" robot picture to update correctly.

Look forward to seeing the new version.
-Oris-

Hi Oris,

It is working now. Thank you. The picture automatically changes when the user type in a new team number in cell B2. One thing I have to change to get it to work is teamnum as Integer instead of teamnum as String.

The picture is okay if the aspect ratio is 4:3 but it get distorted if it is not. How can I maintain the aspect ratio? I tried to do it but was not successful. Can you take a look at the program and see what I did wrong?

I have decided not to protect the worksheet for now since I am still developing it.

Ed

[Clinton Bolinger]

After:

Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.5, AC.Top + 2.5, 329, 250)

Add:

    P.ScaleHeight 1, True
    P.ScaleWidth 1, True
    P.Height = 250

That should keep the aspect ratio of the pictures.

-Oris-

[Ed Law]

Originally Posted by Oris

After:

Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.5, AC.Top + 2.5, 329, 250)

Add:

    P.ScaleHeight 1, True
    P.ScaleWidth 1, True
    P.Height = 250

That should keep the aspect ratio of the pictures.

-Oris-

Hi Oris,

I tried it and it worked. However when I tried it on Excel 2007, I think it changes it back to its original size and then stretch it to Height = 250 without maintaining the aspect ratio. I tried a few things but none seems to work. Can anybody help? I would like to make this work for everybody as more and more people will be using Excel 2007. Thanks.

Ed

[ObedJ]

This is Obed, a rookie member of 354, wondering how do you put information the scouting database.

[Ed Law]

The 2010 Scouting Database from Team 2834 is out. I added links for Week One only. I have to do it manually and it is very time consuming. I wrote a program last summer to add links automatically but it is not working. With the changes in scoring this year, I don't know if they are changing the format. I am going to wait until past Week One before I do the rest. We are going to a Week One competition also so I will not have time to re-publish it on Friday if it does not work. I apologize in advance.

A few words about OPR and CCWM this year. There were some discussions about whether OPR and CCWM have any meaning this year. So far I stayed out of that discussion partly because I was busy working with my team to build our robot. Also I do not want to get into a debate. If you do not think OPR and CCWM is useful this year, don't read on. I am not trying to convince you.

So here is my thought on OPR and CCWM. The number of goals scored this year is meaningless because teams who have a big lead will try to score for their opponents. The winning margin is therefore meaningless also. Unlike last year where the offensive score was based on how many moon rocks and supercells were scored, this year the reported score is not based on goals. The reported score is actually the seeding score. Since teams will try to maximize their seeding score, they will try their best.

The interpretation of OPR this year has to be changed slightly. It is not a measure of how strong their offensive power or how many balls the team can score. It is a measure of how successful the team is to get a high seeding score for their alliance. This is why I think OPR is still meaningful but we have to interpret it differently.

On the other hand, I think CCWM has no meaning this year because of the way the coopertition points are given out. However, my program will still calculate and report CCWM. I do not want to change the program just for this year.

[Ed Law]

Originally Posted by ObedJ

This is Obed, a rookie member of 354, wondering how do you put information the scouting database.

I am sorry but you need to clarify what you would like to ask. I am not sure what you mean.

All the data came from usfirst.org. I wrote a number of macros and use Excel functions where convenient to organize the data. You can PM me if you would like more information.