Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-06-2013, 01:02 PM
Gandalf's Avatar
Gandalf Gandalf is offline Data query import Windows 7 64bit Data query import Office 2003
Novice
Data query import
 
Join Date: Feb 2013
Location: California, USA
Posts: 9
Gandalf is on a distinguished road
Default Data query import

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 and 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 manual 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 really determine of the import is occurring correctly each time I open the file. I suppose I could reprogram the other worksheet to compensate for the right shift, but that seems like cheating and I would like to know what I am doing wrong.

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.
Reply With Quote
  #2  
Old 02-06-2013, 01:25 PM
OTPM OTPM is offline Data query import Windows 7 32bit Data query import Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
Are you able to post a copy with your VBA?
Tony
Reply With Quote
  #3  
Old 02-06-2013, 01:59 PM
Gandalf's Avatar
Gandalf Gandalf is offline Data query import Windows 7 64bit Data query import Office 2003
Novice
Data query import
 
Join Date: Feb 2013
Location: California, USA
Posts: 9
Gandalf is on a distinguished road
Default

Hi Tony,

Thank you for your timely response to my first thread/post in this forum.

I am not using VBA. This is a much more simple application than that. I'll upload my .xls file, if I can figure out how.

Strike one for the wizard. I had to download the WinZip program, first, but you should have a copy of my amateurish attempt at creating an .xls file. It's what I get for volunteering.

You will note that the third sheet named WCG is where the imported data is stored. There is a lastweek's sheet which is just a text copy of last week's Stat Report. Then, there is the thisweek's sheet where the logic retrieves the data from the other two sheets to create the new week's Stat Report.

A lot of this is accomplished manually. I am trying to learn enough to make it a little more automatic. This attempt to retrieve the website data is my first attempt to do just that.

Sincerely,
Gandalf
Attached Files
File Type: zip Stat Report.zip (71.8 KB, 14 views)
Reply With Quote
  #4  
Old 02-07-2013, 06:11 AM
OTPM OTPM is offline Data query import Windows 7 32bit Data query import Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
Now I understand what you are trying to achieve (I think ).
If you are trying to automate the import process then have you tried recording a macro whilst you do the manual process. You can then use the macro for future imports and make it automatic.
Is this what you want?
Tony
Reply With Quote
  #5  
Old 02-07-2013, 09:46 AM
Gandalf's Avatar
Gandalf Gandalf is offline Data query import Windows 7 64bit Data query import Office 2003
Novice
Data query import
 
Join Date: Feb 2013
Location: California, USA
Posts: 9
Gandalf is on a distinguished road
Default

Quote:
Originally Posted by OTPM View Post
Hi
Now I understand what you are trying to achieve (I think ).
If you are trying to automate the import process then have you tried recording a macro whilst you do the manual process. You can then use the macro for future imports and make it automatic.
Is this what you want?
Tony
That doesn't tell me why what is happening is happening, but it does give me a new direction to try. While I've never done it before, it's worth giving it a try. I'll let you know how it works, once I figure out how to do it.

Thanks
Reply With Quote
  #6  
Old 02-07-2013, 11:05 AM
Gandalf's Avatar
Gandalf Gandalf is offline Data query import Windows 7 64bit Data query import Office 2003
Novice
Data query import
 
Join Date: Feb 2013
Location: California, USA
Posts: 9
Gandalf is on a distinguished road
Default

Quote:
Originally Posted by OTPM View Post
Hi
Now I understand what you are trying to achieve (I think ).
If you are trying to automate the import process then have you tried recording a macro whilst you do the manual process. You can then use the macro for future imports and make it automatic.
Is this what you want?
Tony
Tony,

When I manually try to perform a Web Query, the unrequested data is still imported.
1. Create an .xls file.
2. Place the cursor at cell A1.
3. Select Data > Import External Data > New Web Query...
4. Address: http://www.worldcommunitygrid.org/te...rdsPerPage=100
5. Click on Arrow associated with Member Name.
6. Click on Import button.
7. Click on OK button.

The following is imported:
_____________________________

Download Software
Do you want to download the software again? Click here to visit the Download page.
Visit the Forums
If you need technical assistance beyond what's available in Help, please visit the forums to post your questions and get answers on how others are using World Community Grid.
Tell A Friend
Help accelerate humanitarian research even further by telling your friends about World Community Grid.
Related Resources System Requirements Points System Member Policy
Powered by IBM
_____________________________

This is NOT what was selected for import.
Rather the site is defined as secure or not the same data is imported.

If the manual technique is flawed, the macro will also be flawed. Also, I believe a shortcut key is needed to run the macro, but on this point I am not real sure, since I didn't get that far in my trial run.

Last edited by Gandalf; 02-07-2013 at 11:06 AM. Reason: Fix typo
Reply With Quote
  #7  
Old 02-07-2013, 11:35 AM
Gandalf's Avatar
Gandalf Gandalf is offline Data query import Windows 7 64bit Data query import Office 2003
Novice
Data query import
 
Join Date: Feb 2013
Location: California, USA
Posts: 9
Gandalf is on a distinguished road
Default

Quote:
Originally Posted by OTPM View Post
Hi
Now I understand what you are trying to achieve (I think ).
If you are trying to automate the import process then have you tried recording a macro whilst you do the manual process. You can then use the macro for future imports and make it automatic.
Is this what you want?
Tony
I just received an answer from the xtremesystems.org forum.
The information is very interesting.

http://www.xtremesystems.org/forums/...=1#post5169313



As posted on the xtremesystems.org forum, I have queried the WCG forum for their feedback. Fingers crossed.
Reply With Quote
  #8  
Old 02-08-2013, 03:48 AM
Gandalf's Avatar
Gandalf Gandalf is offline Data query import Windows 7 64bit Data query import Office 2003
Novice
Data query import
 
Join Date: Feb 2013
Location: California, USA
Posts: 9
Gandalf is on a distinguished road
Default

I have been informed by xtremesystems that my Web Query issue does not appear in the 2010 version of Microsoft Excel.

I wonder, is there a 2003 patch for this problem or did they simply put the fix into subsequent versions of Excel? As far as I know, I have all the upgrades/patches for my Microsoft Office 2003.
Reply With Quote
  #9  
Old 02-11-2013, 07:29 PM
Gandalf's Avatar
Gandalf Gandalf is offline Data query import Windows 7 64bit Data query import Office 2003
Novice
Data query import
 
Join Date: Feb 2013
Location: California, USA
Posts: 9
Gandalf is on a distinguished road
Default

A friend at extremesystems helped me put together a workaround for this issue. I have attached the workaround for any who might be interested. It was accomplished by VB.
Attached Files
File Type: zip Test1Rev2.zip (26.5 KB, 9 views)
Reply With Quote
  #10  
Old 02-12-2013, 03:03 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Data query import Windows 7 64bit Data query import Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Please read the paper referred to in my sig about cross-posting.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Query Text in MS-Excel 2010 or Import Data & Query outside of Excel? mag Excel 0 10-18-2012 11:15 AM
Import excel data in to SQL Server DavidBrown Excel 0 08-08-2011 04:49 AM
How to Import Notes Field data along with attachments? Prabhakar Project 0 04-08-2011 04:46 AM
Import Pics and Excel Data into PP? jawillyams PowerPoint 0 03-13-2011 01:03 PM
Data query import Outlook data import Cunner Outlook 2 08-20-2010 02:03 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft