Macro for league projector excel sheet

DiabeticGuy · Macro for league projector excel sheet

I know it isn't a greasemonkey script but it is a handy timesaver anyway (if you use the league projector that is.).

NOTE: In order for my macro to work, the team names MUST be typed exactly as they are on the website. Even the long names. I happen to know some people shorten them for their own purposes, but if you want to run my macro you have to either make it right on your spreadsheet or edit the imported website data to match what you have every single time. It's your choice.

Download my macro file from this URL: http://www.filefactory.com/file/a3bd3d/n/Automate_bas

Next, in the menu go to Insert>Worksheet. You can rename it whatever you want but for now move it to the end so it is 7th from the left. Now go to Tools>Macro>Visual Basic Editor. Over on the left side of the screen is a list of all the worksheets. The new sheet you just created should say Sheet 7 and have the name you gave it in (). As soon as you've seen it as Sheet 7 on that page, it is Sheet 7 forever. I figured out that if you first open the VBA Editor with it in a different spot it will make it that spot forever instead, which means you'll have to edit the code to make it right. If this happens to anyone, post here and I'll walk you through it. Once you are sure it's Sheet 7 you can move it wherever you want and it'll still work.

Minimize the VBA Editor and select A1 on the blank spreadsheet. Then go to Data>Import External Data>New Web Query. An internet window should pop up. There's a URL box, so use it to find and log in to GLB. Go to the page of the league you are projecting (the one that lists all the game results, standings, and leaderboards). There should be a little yellow box with a black arrow in the top left corner. Click on that and it will become a check mark. Then you need to click on the options menu in the top right corner of the browser and disable date recognition. Once that's set, click Import and watch as a bunch of numbers fill up your previously blank worksheet.

Now if you scroll down you should see all the teams in your league listed in A44-A59 and C44-C59 with vs. in between them and the score off to the right. Leave that alone. Remember the Visual Basic Editor you minimized a while back? Bring it back up. In that menu go to File>Import File and find the macro you downloaded. Open it and over on the left under all the sheets a Module should pop up. That's the macro.

Now you can exit out of the Visual Basic Editor (don't worry, it saves along with your excel file). Speaking of which, you might as well save now. That's it, you're all set. All you have to do now is go to Tools>Macro>Macros and run "Automate."

mw54finest

This sounds really cool. I will try it when I have some time on my hands and post up some screen shots.
Thanks!

DiabeticGuy

NEW VERSION!

I added a few lines of code that check for errors. One common error I've encountered is that some teams have a random space at the end of their name on the site, so it causes problems when the string doesn't match the sheet, which ruins the macro (for that team). In the event that this happens, usually one team's score is not put in for a game but the other's is. This would cause the points for and points against totals to not match up. The new version of the macro checks that they are the same and a message box pops up if they aren't. If two teams have that problem and play each other it could go unnoticed because PF and PA would still match, so it also counts the number of scores it puts in while running and the same message box pops up if ALL the scores weren't put in.

The link is updated in the first post.

okole

i have the projector sheet, but haven't used it yet this season.

i want to get this straight -- to get your macro to work, i need to:
1- set up the teams (type them exactly as on GLB)
2- do i have to set up the schedule as well?

DiabeticGuy

Yes, you'd have to set up the schedule too. You'd also have to put all the scores from past games in by hand, but once you have those up to date you just have to refresh the data every 2 days and run the macro again. It saves me so much time that I actually use it for all 5 leagues with my teams in them and it takes only a couple minutes per day and I never have to get bored from typing in numbers.

Once you have the data from the web in place you just have to go to Data>Import External Data>Edit Query. The "error, you are signed out for inactivity" screen pops up, you sign in, exit out of the query editor and go to Data>Refresh Data and run the macro. Then if you have other leagues that have games done you can just refresh the data on those sheets because you're already signed in. HUGE time saver.

mw54finest

The macro works for last week, but I am getting

StazNasty

Nevermind. Wow, have to enter them all manually lol. past games suck balls.

bamaplaya1

now if we can get some1 to make a greasemonkey to automatically put a n umber beside each team on the schedules Razz