| Index | Recent Threads | Unanswered Threads | Who's Active | Guidelines | Search |
 |
World Community Grid Forums
Category: Support
Forum: Suggestions / Feedback
Thread: Microsoft Excel Web Query results [Resolved] |
| No member browsing this thread |
|
Thread Status: Active Total posts in this thread: 4
|
|
| Author |
|
|
Former Member
Cruncher Joined: May 22, 2018 Post Count: 0 Status: Offline |
Reference source: http://www.xtremesystems.org/forums/showthrea...amp;p=5169306#post5169306
----------------------------------------In my efforts as the PCHF Team's weekly Stat Report member, I queried the Xtremesystems.org forum concerning the import of data from http://www.worldcommunitygrid.org/team/viewTe...amp;numRecordsPerPage=100 The following is the query and the resulting answer... Query: _______________________________________________________________________ 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. _______________________________________________________________________ Answer: 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. _______________________________________________________________________ Well, as the last line indicates, I need help from my buddies at WCG. [Edit 2 times, last edit by Former Member at Feb 12, 2013 2:22:54 AM] |
||
|
|
Former Member
Cruncher Joined: May 22, 2018 Post Count: 0 Status: Offline |
I'm doing it [remember the yellow arrow picking, where the selected elements turn green?], someone else does too... see the other thread you started, no xml, html or csv file toiling, simply a direct query from the spreadsheet.
----------------------------------------(I'll ask the admin to move this to chat as this is not the right forum for this, if at all website support would be the closest fitting location.) [Edit 1 times, last edit by Former Member at Feb 7, 2013 6:48:04 PM] |
||
|
|
wplachy
Senior Cruncher Joined: Sep 4, 2007 Post Count: 423 Status: Offline |
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/viewTe...ge=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. (Also posted here )
Bill P
 |
||
|
|
Former Member
Cruncher Joined: May 22, 2018 Post Count: 0 Status: Offline |
The fix is in, for my Stat Report. That doesn't mean WCG doesn't have a glitch in it's HTML programming, but due to a extreme brain at extremesystems.org I now have an Excel work around.
|
||
|
|
|