[Resolved] Excel website data import

[Gandalf]

Reference: http://www.worldcommunitygrid.org/te...rdsPerPage=100

I'm looking for someone smarter than me. That's the easy part. I'm using Microsoft Excel 2003, because I can't afford the newer versions. I am trying to do a Data > Import External Data > from the referenced link.
The data field starts with Member Name. I am importing the data into an empty worksheet named WCG. At most, the worksheet will have yesterday's manually download of data in it.

Here is the catch. It doesn't work, automatically. I am trying to get the spreadsheet to import the daily updated data every time I open the .xls file. When I do get something to import it puts unwanted data into the beginning columns before the wanted data is brought in/imported. With this unwanted data being brought in, I can't check to see if the wanted data is being used correctly, because all the wanted data is columns to the right of where the other worksheets are looking for the data to be.

Until this is corrected, I can't really determine of the import is occurring correctly each time I open the file. I suppose I could adjust the programming on the other sheets to compensate for the right shift in the wanted data location on the worksheet, but that feels like cheating. Besides, I'd like to know why Excel in importing the unrequested columns of data.

Any and all assistance in solving this exasperating issue is much appreciated.

Gandalf

P.S. I can manually import the data successfully via copy and paste techniques.
It's the automatic technique that I am sorely failing at.

[s1nykuL]

I had a look and Open Office does the same.

I also checked out the source code of the web page.

The web page content is organised into separate html tables for the title area and the content of the WCG results data.
It seems that the import inserts part of the first table the title area into the first column and the second table, the actual results, into the second and so on columns.

Without parsing the page through a script to dump the data into one html table or a csv file I cannot see how this can be automated unless WCG change the html coding of the page.

Are there any of your buddies doing WCG who can help.

I could write a php script to do this but you would then need to run a webserver in order to run the php script.

I would have to relearn .net or Windows command line scripting to create something that would automate the process in windows.
It has been many years since I did any windows coding

Sorry I cannot help further.

[Gandalf]

This is my posting to WCG as suggested...
http://www.worldcommunitygrid.org/fo...age,yes#411651

[pirogue]

I created a new excel file in Excel 2010 and it seems to work without moving columns around. Maybe using a file created in Excel 2010 will help??
www.wcgdaws.com/releases/testbook1.zip

[Gandalf]

I created a new excel file in Excel 2010 and it seems to work without moving columns around. Maybe using a file created in Excel 2010 will help??
www.wcgdaws.com/releases/testbook1.zip

Great! Now all you need to do is give me one of your Excel 2010 install licenses.

[pirogue]

I saved it in .xls (97-2003) format, so you should be able to open it.

[Gandalf]

I saved it in .xls (97-2003) format, so you should be able to open it.

I appreciate your forethought. Yes, I was able to open it. As you indicated, it looks good. How I'm going to beg, barrow or steal the 2010 version of Microsoft Excel is another question. The conclusion is, is there a 2003 patch that fixes the issue that obviously the 2010 version fixed.

[pirogue]

I was hoping that maybe using a file created in 2010 would solve the problem. Oh well.

[Gandalf]

I was hoping that maybe using a file created in 2010 would solve the problem. Oh well.

Pirogue,

You solved one problem and created another. Not your fault. Another MS money maker.
What's new. Now, if I can just find where I hid my piggy bank.

[wplachy]

There is another way to pull this using Excel 2003 that I've found works well and doesn't require Excel 2010.

Open a new workbook, open Visual Basic, insert a Module in the new workbook and add the following macro:

'---Start Macro---
Public Sub CapTeamStats()


Dim boSvWarn As Boolean 'Save Application Warning Value
Const coTSURL As String = "http://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?sort=points&teamId=TW5XW7P WT1&pageNum=1&numRecordsPerPage=100&xml=true"


Cells(1, 1).Select 'Select First Cell
boSvWarn = Application.DisplayAlerts 'Save Warning Setting
Application.DisplayAlerts = False 'Suppress Warnings
ActiveWorkbook.XmlImport URL:=coTSURL, ImportMap:=Nothing, _
Overwrite:=True, Destination:=Range("$A$1") 'Import XML File
Application.DisplayAlerts = boSvWarn 'Restore Warning Setting

End Sub
'---End Macro---

Then flip back to the first sheet in the new work book and run the Macro you just added.

The data I believe you want will be in the following columns:

Name2=WCG Member Name
MemberId=WCG Member ID
JoinDate=Date Joined Team (m/d/y)
RunTime=Team Runtime in seconds
Points=WCG Points Awarded
Results=WCG WUs Completed
Url3=URL to Member Stats
Description4=WCG Member Name Repeated
RetireDate=Date Left Team

As for importing the daily updated data every time you open the .xls file you have to setup the workbook to autoexec the macro on open.

Good luck and let me know if this gets what you're looking for.

[Gandalf]

There is another way to pull this using Excel 2003 that I've found works well and doesn't require Excel 2010.
...

As for importing the daily updated data every time you open the .xls file you have to setup the workbook to autoexec the macro on open.

Good luck and let me know if this gets what you're looking for.

wplachy - Thank you for your recommendation. However, upon applying your macro, the following resulted...





Since I copy and Pasted your macro, I know it's not a typo on my part.

[wplachy]

wplachy - Thank you for your recommendation. However, upon applying your macro, the following resulted...





Since I copy and Pasted your macro, I know it's not a typo on my part.

There is a space in the URL string for teamID= remove it and it works fine.

You have:

---------------------------------------------------------------------------------------------------------------------------V--------------------------------------------------
Const coTSURL As String = "http://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?sort=points&teamId=TW5XW7P WT1&pageNum=1&numRecordsPerPage=100&xml=true"
Remove the space between teamID=TW5Xw7P WT

It should read teamID=TW5XW7PWT1 (no space between P and W)

It looks like the forum software is inserting it. Just change it in the Const= string and try again.

Bill P

[Gandalf]

There is a space in the URL string for teamID= remove it and it works fine. Bill P

To begin with, thank you Bill for your support.
I never was a good programmer. Engineering is much easier and wizardry just comes naturally to an engineer.

Rather than try to explain in words the results I saw, I think a picture of what I need will explain things far better.



How do we fix the macro to provide this result?

[wplachy]

Gandalf, I'm confused as to what you are looking for. The speadsheet that pirogue provided (www.wcgdaws.com/releases/testbook1.zip) gives you the data you are looking for in the format you want with the 1 minor exception of splitting the heading into 2 rows. The macro I provided pulls the data in a different format but provides the same information.

pirogue's spreadsheet works fine in Excel 2003 and you do not need 2010 to use it. Everytime you open the spreadsheet file it will refresh the data. The only thing you would need 2010 for is to build a new/different import query, as that doesn't seem to work correctly in 2003.

Can you help me understand the problem you have with using the data imported by piroque's spreadsheet or my macro?

Bill P

[Gandalf]

Gandalf, I'm confused as to what you are looking for. The speadsheet that pirogue provided (www.wcgdaws.com/releases/testbook1.zip) gives you the data you are looking for in the format you want with the 1 minor exception of splitting the heading into 2 rows. The macro I provided pulls the data in a different format but provides the same information.

pirogue's spreadsheet works fine in Excel 2003 and you do not need 2010 to use it. Everytime you open the spreadsheet file it will refresh the data. The only thing you would need 2010 for is to build a new/different import query, as that doesn't seem to work correctly in 2003.

Can you help me understand the problem you have with using the data imported by piroque's spreadsheet or my macro?

Bill P

Sorry. The data is there, I just didn't expect all that other import. I can reprogram from here. Thanks.

[Gandalf]

There is another way to pull this using Excel 2003 that I've found works well and doesn't require Excel 2010.

Good luck and let me know if this gets what you're looking for.

wplachy - Have incurred a Run-time error 1004.
The operation cannot be completed because the XML list is bound to a different XML map.

If you want, I'll attach a .zip of the .xls file.

[wplachy]

wplachy - Have incurred a Run-time error 1004.
The operation cannot be completed because the XML list is bound to a different XML map.

If you want, I'll attach a .zip of the .xls file.

Yes, attach a copy of the .xls with the macro(s) intact. While I have never run into that problem I suspect Excel thinks you are trying to re-map a XML map. Also, please describe what steps you take to introduce the error so I may duplicate it. I'll be out this afternoon and will look at it this evening or tomorrow AM and post back

Bill P

[Gandalf]

Yes, attach a copy of the .xls with the macro(s) intact. While I have never run into that problem I suspect Excel thinks you are trying to re-map a XML map. Also, please describe what steps you take to introduce the error so I may duplicate it. I'll be out this afternoon and will look at it this evening or tomorrow AM and post back

Bill P

When the Macro query pops up, if I say NO, no error message.
When I say YES, error message.

Is someone modifying your File Upload Manager?
It says Click Add Files, but there is no Add Files button.


Talk about making a simple thing difficult.
Will upload my .xls file as soon as I can get the up loader to work.
That deleting of the upload within an hour kind of stinks.

[wplachy]

Talk about making a simple thing difficult.
Will upload my .xls file as soon as I can get the up loader to work.
That deleting of the upload within an hour kind of stinks.

No need to upload the file as I have identified the problem. I have a fix and will in addition supply you with the code to format the data the way you want to see it. Give me a few minutes to finsh writing and testing it and then I'll post the macros here.

Bill P

[Gandalf]

The first Members in the "thisweek" worksheet now calls up =IF(WCG!N87<>"",WCG!K87,"").
The data row starts at WCG!A2. Row 85 is the last Members data row in the WCG imported data.
Therefore, all the data transfer in the "thisweek" worksheet is blank.

=IF(WCG!N87<>"",WCG!K87,"") should read =IF(WCG!N2<>"",WCG!K2,"")

"The second is a new macro that will add a new sheet and build a formatted team stats list ..."
No new sheet is created. I'm assuming the new Sub was meant to be added to Module1 just after the original Sub.

"If you want to you can set CapTeamStats() to call this new one after it completes the XML load"
I do not know where/how to do this. I think you are assuming I am smarter than I am. What I have seen in other scripts is a line of programming put in it's proper place but commented out so the user can activated the line simply by removing the comment marks. That I can do!

[Gandalf]

Yes, attach a copy of the .xls with the macro(s) intact. While I have never run into that problem I suspect Excel thinks you are trying to re-map a XML map. Also, please describe what steps you take to introduce the error so I may duplicate it. I'll be out this afternoon and will look at it this evening or tomorrow AM and post back. Bill P

This should be the .zip file. It's my first try, so I hope it works.
http://www.mediafire.com/download.php?hnp6348i3jhmeb4

[wplachy]

The problem is that when Excel updates the XML sheet ("WCG") it changes the cell references in the "lastweek" sheet. If I get some time I'll see what has to be done to stop the cell reference updates. Until then I changed the code to get around the problem.

I updated the spreadsheet you posted so that it works the way you want it to. The updated spreadsheet is at

http://d01.megashares.com/dl/GcKRgri/Test1Rev2.zip

When you click on the link you'll get a choice of "regular" or "Hi-Speed Download", select the regular. Ignore anything on the next page about "Reactivating Your Passport". You don't need to do that. Just click on the "Download File" Button and save the file on your PC. You may have to do it a couple of times. When I tested the download I got a Web Site not found. I just went back to the link and then it worked fine.

When you open the updated spreadsheet it will pull the team stats and update the "thisweek" sheet.

The changes I made were to modify the CapTeamStats() macro, delete the FormatTeamStats() macro and removed the references in "thisweek" to the "WCG" sheet. The macro will fill in the Member and current points and results in the "thisweek" sheet.

Bill P

[Gandalf]

When I click on the link, then click on the REGULAR DOWNLOAD button, I get a DOWNLOAD FILE button.
When I click on the DOWNLOAD FILE button, it puts me back to the REGULAR DOWNLOAD button.
Nothing gets downloaded.

Edit: I signed up with your Megashares and now have the file.

[Gandalf]

Talk about timing. Yesterday WCG had some downtime with the upload/download of their tasks. Sunday is our team's normal Stat Report posting day.
http://www.pchelpforum.com/xf/thread...-10-13.147024/
You'll notice my again was about 1.2 million points for a week's worth of crunching.

Well, when I ran your revision of the Stat Report, it showed I have over one million points gain for one day. Now, my machines work hard, but not that hard. So obviously I figured you have slipped up with your cell movement. Sorry, but that is what I thought. So, I rechecked everything and your revision is spot on. Apparently, unless WCG takes back the extra points, they must have recalculated their counting protocol or their verification techniques have been tweaked. Anyway, the timing was eerie between whatever they did and my downloading of your new revision. I'll be very interested in seeing what the numbers say tonight after the Sunday/Monday calculation day. Also, tomorrow will verify the automatic feature you programmed in.

I noticed you put in a warning in the event that we get more new members than we have provided rows to account for them. Wise precaution. I'll post again tonight after I confirm the automatic data download feature. WCG only updates their numbers once a day.

[Gandalf]

... WCG only updates their numbers once a day.

The WCG daily stats are out and your programming worked like a charm, but you already knew that didn't you.


Thanks for all the help.
Gandalf