Editing Data source from imported web query

Hi,

I've currently got an Excel sheet with a table I've imported from a
web page I've created.  This was done using the Data->Import External
Data->New Web Query.  The page itself is a CGI script and as such
takes arguments in the address.

I was wondering if anyone knows how I can alter that URL from a cell
in Excel.  Specifically I want to be able to change the hostname in a
cell in Excel and the corresponding ?hostname='blah' to change.

Any ideas would be greatly appreciated.

Jim
0
7/17/2003 4:47:23 PM
excel 39879 articles. 2 followers. Follow

1 Replies
651 Views

Similar Articles

[PageSpeed] 24

It seems to me that you'll need a macro to do what you're trying to
accomplish.

Let's say I have inserted a new web query on Sheet1.  The data source is
[http://cobrands.hoovers.com/global/cobrands/ap/factsheet.xhtml?Ticker=orcl]
and I name the query "MyWebQuery" (in Data -> Import External Data -> Data
Range Properties...).

In cell H1, I insert "ORCL".

Now, right-click Sheet1 and click View Code.  Insert the following:

Sub UpdateData()
  Sheet1.QueryTables.Item("MyWebQuery").Connection = _

"URL;http://cobrands.hoovers.com/global/cobrands/ap/factsheet.xhtml?Ticker="
& _
    Sheet1.Cells(1, 8)
    Sheet1.QueryTables.Item("MyWebQuery").Refresh
End Sub

Run UpdateData whenever you want to refresh your data.  Enter different
stock symbols (e.g. MSFT) in H1 and run the macro again to test.

An more elegant solution may involve creating an event handler to handle
changes in H8.

/i.

"Orisopha" <jameskirby@hotmail.com> wrote in message
news:80da20be.0307170847.44bc5924@posting.google.com...
> Hi,
>
> I've currently got an Excel sheet with a table I've imported from a
> web page I've created.  This was done using the Data->Import External
> Data->New Web Query.  The page itself is a CGI script and as such
> takes arguments in the address.
>
> I was wondering if anyone knows how I can alter that URL from a cell
> in Excel.  Specifically I want to be able to change the hostname in a
> cell in Excel and the corresponding ?hostname='blah' to change.
>
> Any ideas would be greatly appreciated.
>
> Jim


0
ismits (18)
7/20/2003 11:31:00 PM
Reply:

Similar Artilces:

Query About the Certification
Hi, I plan to do some certification with regard to the microsoft packages. I'm the Application developer having some knowledges in mfc's and sdk, Which type of certication is possible to do by me. pls give me ur helpfull sugession to me. I heard some thing about MCAD. But i don't know how things are related to vc++ in that, that mostly given for vb. net and c#.net. In most places requirements to C# is high as compared to ours(vc). Is that c# places important role than vc?. If so pls give me how easy/possible of me to migrate from vc to vc#. Thanks Srii ...

Estimated Data size for 3-5 years
Dears, I was looking for a document that give me an idea or talking about how to estimate Microsoft Dynamics GP database Growth and size. Any one could help me in this? I found an article on MSDN but it is about SQL server and it needs to go throw all GP tables and finde the size of each field and calulate that number. I have the following GP system: POP module FA module And Financial Module Any help? Best regards, Hi Hasan, It really depends on the number of transactions that go through your system. If I had to guess at an average, I would say a database in use for 3-5 years would ...

Compiling dataset using an XSD that has import and include tags
Hi,I am trying to create a DATASET object using an .xsd file that has an import tag. The imported .xsd in turn has an include tag for another .xsd. Is it possible to create a dataset based on such an xsd using the XSD.exe or are there any other tools available for this? FYI, the xsd tool works for this schema creating classes if the the path of the IMPORTED xsd is explictly given on the command line. But if I run it with the '/d' directive, it fails saying that the SchemaLocation attribute has an invalid URI. Please let me know any information on this. Thanks Rupa have you tri...

IIF function help in query
Hello, I have the following field in the query to look at a field PartDesc, if any of the text have a *BUSH* word, return yes, otherwise no. BUSH: IIF(PartDesc= Like *BUSH*, "yes","no") Why the Like *BUSH* not working? Thanks Because = and LIKE are two operators, a little bit like: 4 * / 3 would mean what (multiply followed by divide)? Probably better to remove the =, in this case: BUSH: IIF(PartDesc Like *BUSH*, "yes","no") Vanderghast, Access MVP "Cam" <Cam@discussions.microsoft.com> wrote in ...

Help with macro looping and color query function
I have been struggling to get a macro together to perform some tricky coloring functions. With the kind help of a number of people in the 'Excel Worksheet Forum' I have got close to achieving something which works but need help to get the macro to run. Here is where I have got to: The following is an example macro, kindly provided to me by Paul who has been helping me in the worksheet forum: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 19/05/2005 ' ' mycell = Range("A1").Value mycolor = Range("C1").Value Sheets("Sheet2...

Data base
I need to know as the table is called where registo keeps from the TPV arelysas HI I think you are talking about the Configuration table which store the information of store also there is table name called "register" please describe your main purpose and what exactly you are asking. Hope this will help you. "arelysas" wrote: > I need to know as the table is called where registo keeps from the TPV > > arelysas ...

Query Running Balance
I am using the following query to create running balance at every change of Invoice Number but no luck, here Invoice Number is a text field SELECT [Inventory Movement Extended].InventoryCode, [Inventory Movement Extended].InvoiceDate, [Inventory Movement Extended].InvoiceNumber, [Inventory Movement Extended].[Company Name], [Inventory Movement Extended].InventoryName, [Inventory Movement Extended].Purchase, [Inventory Movement Extended].Sale, DSum("[Purchase]","Inventory Movement Extended","[InvoiceNumber]<='" & [InvoiceNumber] & &q...

mysterious edits
Hello: I'm trying to understand a form of mistake in documents that have been edited by other people. The column will be mostly text, such as: Sent to Smith for approval in September. And somehow an error can get in there, going undetected for awhile, which includes a cell reference, just as regular text: Sent to Smith for approval in September+G123. Can you think of a sequence of actions which could lead to this? Some kind of automation? Or some kind of personal options someone could have set? Thanks in advance. Most likely an errant user edited the cell. Cheers "Al...

Problems importing pst file...
One of the users in our office tried to open Outlook & recieved an error message that indicated that the pst was gone or corrupt. After running the Inbox repair tool to no avail, as well as picking up a copy of their pst from yesterday's backup, I'm still unable to do anything with it! When trying to import it, I get the message "Properties for this information service must be defined prior to use" & after hitting ok, I get a message that the file isn't a personal folders file! I have read several long-winded posts in other forums, which basically have given me no...

How to Trap Rogue Data ?
I have an application that generates hourly system performance logfiles which I graph to look for long term trending. The metric I use gradually varies from 1% to about 15% depending on various external factors - such as time of day and day of week. My problem is that the logfiles sometime hiccup and generate bad data resulting is huge spikes in my curve. I have trapped for the big ones > 20% in my source data but I need something smarter so I can catch large deviations from the curve. Unfortunately I do not have the option to fix the application that generated the bad data. Are ther...

Track source of Netsky in mail?
Hello! I have a client that uses Outlook 2000 and 2003 with POP accounts from their ISP. They have about three users who keep getting Netsky stopped by Symantec Antivirus Corporate Edition version 8.1 on their desktops. They get at least a dozen a day on some users. Is there any way to track down the sending user or computer? I suspect it is not on their network, but possibly an unprotected home user with their address book. Thank you for the help! Gregg Hill No, otherwise, this problem would have been nipped in the bud a long time ago. --� Milly Staples [MVP - Outlook] Post all repl...

Rota spreadsheet
Hope someone can help as I'm at a total loss. I've got a rota that shows all personnel details, with shift patterns: 9am - 3pm 8am - 4pm 10pm - 4am etc (shift patterns subject to change and are individual) I have a column that shows them 'clocked on' and another for 'clocked off'. Now for legal purposes I need to know who is 'on' at regular intervals: 7 am 7.10 am 7.20 am 7.30 am so thinking logically I'd say if for the 7 am count if they are 'clocked in' but not 'clocked off' then count that person but I can't seem to find the r...

XLS2000 IE6 No Data Sort
I've got users with IE6 and Office 2000 who are opening XLS sheets from the web. From the IE browser the Data menu appears but is not functional. I attemped to unlock the toolbar and move it so that none of the menus were covered up thinking this might help. Office XP (2002) with IE 6 works just fine, problem is I have 1500+ users on Office 2000. Can anyone offer any suggestions? I've check permissions on file, users rights on PC and Office/OS updates. Craig ...

Combining Queries 01-02-08
To explain my problem, I'll use clothes as an example. Imagine a case where I have 40 hats, 35 coats, and 50 shoes which are distributed among three rooms. Each item has a unique identifier, ie H000 thru H039 for hats, C000 thru C034 for coats, etc. Each of the types of clothing have linked tables for model types, etc. I can make a report that lists which hats are in each of the three rooms based on a query that combines the linked tables. I can do the same for the other types. How do I make a report that shows all items, with fields from their linked tables, in each room regar...

Can't see any data in Workflow Monitor
Hey there, Have been finalising my server install and re-adding Opportunities to the newer Workflow. But as doing this I now cannot see any data records whatsoever in the Workflow Monitor. It tells me how many items are Active/Paused ..etc in the Summary creen, but when I got to have them listed no data appears and claims that there are no Active/Paused ..etc records? No idea as to why???????? ...

Journal Entry Data Dump
Our auditors want a data dump (into Excel preferably) of all Journal Entries made during our fiscal year. Does anyone know of a way to accomplish this? Thanks! Kurt, You can do this in SmartList, under Account Transactions. Limit the transaction date range to get just your fiscal year. Remember to change the number of transactions SmartList brings back, since by default it's only 1000. Also, if you get more than about 65,000 transactions back, you will run into Excel limitations, so you may want to break this up further - maybe one export per month. Hope that helps. -- Vict...

Bug: Cannot import brokerage statement
I just installed Money 2005 and then added my 401k account (Prudential Investments). When it asked me what transactions I wanted to download, I selected all. After the download finished I go to the Review investment activity page. I see a list of all the investment activity. I click the Next button to update Money to this statement. I get a dialog stating that a security was found where it already exists but under a different name. There are two radio buttons, the first to confirm that it is the same security, the second to add it as a new investment. I select the first(default) and the...

Importing Data from Access -- Parameter Errors
I am attempting to import a query from access. I get to the point of view or saving query in excel and get message "Too few parameters, expected 4." There are several parameters in my query. How do I avoid this message and get my query into excel? Does your query work properly if you invoke it directly in Access? And must the user supply the values of the parameters directly, or can those values be picked up from a worksheet? I assume you're using Microsoft Query to return the data from Access. Microsoft Query is not as powerful as Access (and thus any other true relationa...

Restricting query data
I have a query set so I pull DISTINCT dates in the SQL from 1/1/08 thru 12/31/08 to take out duplicate accounts, this works great when I run the report quarterly or yearly but I need to run this report monthly. I have 3 tables that work together and these dates are coming from my Activities table, I keep track of when I have had contact with them. I am looking to only count these people 1 time in a year (filter by start date does not work is it is driven by actibvity date) for example if I work with them in 1/08 run my report in 1/08 they should show up. If I work with them in 2/0...

How do I count unique items in a pivottable data field.
I have a pivottable and I am trying to setup a data field that will count unique values. ie. Listing customers as the row and setting up a field to count the number of sales people that sold to the customer. The base data has multiple rows identifying the customer, sale id, $ and sales person. If I have 100 rows with sales person A, B & C each selling multiple orders to several customers I want the field counter to tell me if 1,2 or 3 sales people sold to each customer. Not the most elegant answer, but I'd probably create my pivot with Customer in the row field, sales rep...

Importing PST 2003 into Outlook 2002(XP)
This does not seem to work, is there a workaround FlopGu ----------------------------------------------------------------------- Posted via http://www.mcse.m ----------------------------------------------------------------------- View this thread: http://www.mcse.ms/message263775.htm Save your data in an "Outlook 97-2002" PST. Open that in Outlook 2002. Do not export. -- Russ Valentine [MVP-Outlook] "FlopGun" <FlopGun.zrkih@mail.mcse.ms> wrote in message news:FlopGun.zrkih@mail.mcse.ms... > > This does not seem to work, is there a workaround ? > > &...

how to compile emule source code?
Hi, when I compile the emule source , the result is MSVCRTD.lib(MSVCR71D.dll) : error LNK2005: __CrtDbgReport already defined in libcmtd.lib(dbgrpt.obj) MSVCRTD.lib(MSVCR71D.dll) : error LNK2005: __resetstkoflw already defined in libcmtd.lib(resetstk.obj) MSVCRTD.lib(MSVCR71D.dll) : error LNK2005: _wcscpy already defined in libcmtd.lib(wcscat.obj) LINK : warning LNK4098: defaultlib 'LIBCD' conflicts with use of other libs; use /NODEFAULTLIB:library LINK : warning LNK4098: defaultlib 'MSVCRTD' conflicts with use of other libs; use /NODEFAULTLIB:library LINK : warning LNK4098...

32,000 data points error message
I keep getting the following pop up error message as I'm creating a line chart. The source data includes two colums with nearly 900 rows of data. Basically the data is temperature readings from two probes (coulmn A&B) at 1 second intervals (900 rows = 15 minutes of data) There's also a 3rd column that has 60 points of data, but this colums in only used to provide the data for the X axis labels. So by my calculation, I have less than 2000 data points, correct? Why the error message? This is what the message says; "The maximum number of data points you can use in a da...

error when importing identity from Entourage X to 2008
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop I installed office 2008 and am getting error messages when I try to import my identity. I get two errors: <br><br>Import from Entourage X could not be completed because an error (-40) occurred. <br><br>Import from Entourage X could not be completed because an error (2) occurred. <br><br>I can't find any information on what these error codes mean. Any idea on how to resolve this? <br><br>Also, I can get it to import my email addresses from a txt fi...

Query occasionally misses orders
Hello. Access 2003 I'm in the testing phase of a new database with tables Customers, Orders, and Payments. There is a one-to-many relationship from Customers to Orders and a one-to-many relationship from Customers to Payments. I created a report that shows each order and each payment that a customer has made, as well as a running balance. The report is based on a union query. I have a command button on a form (frmCreateReports) that creates the report based on the customer selected from a dropdown menu. It all works great, EXCEPT........ Occasionally, the report misses complete orders that...