XML Question

Hi,
Using Excel 2003 Pro.

I've figured out how to pull an XML file from a remote web server into Excel 
and map the 2 fields I'm interested in (ProductID and ProductName) to cells, 
so the data looks like this

ProductName        ProductID
Product 1              1234
Product 2              5678
etc......

However I want to reformat the results so I end up with this

"Product 1"    **    0.05    **    http://www.website.com/1234.aspx
"Product 2"    **    0.05    **    http://www.website.com/5678.aspx
etc.....

I need quotes around the product name, the 2 stars in columns B and D and 
the 0.05 figure in Column C in every row and in Column D add some text 
before and after the ProductID to form a url. I've studied help but can't 
see how to accomplish this.

Thanks,
Jon 


0
jons9339 (2)
12/6/2004 2:42:31 PM
excel 39879 articles. 2 followers. Follow

3 Replies
504 Views

Similar Articles

[PageSpeed] 2

Jon,

Not sure why you want quote marks around the column A data.  Can you 
elaborate?

This could be done with a formula in another column, presuming the product 
name in column A:
="""" & A2 & """"
The formula could be in another sheet, in column A, to get your layout.  It 
would look like:
="""" & Sheet1!A2 & """"

You can put the asterisks in columns B and D and copy down with the fill 
handle.

For the web address, presuming the data is in C2:
="http://www." & Sheet1!C2 & ".com"

This won't make a hyperlink of it.  You need to do Insert - Hyperlink on 
each cell for that.  A macro could go through the column and do that.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jon Spivey" <jons@mvps.org> wrote in message 
news:eb7eSH62EHA.1404@TK2MSFTNGP11.phx.gbl...
> Hi,
> Using Excel 2003 Pro.
>
> I've figured out how to pull an XML file from a remote web server into 
> Excel and map the 2 fields I'm interested in (ProductID and ProductName) 
> to cells, so the data looks like this
>
> ProductName        ProductID
> Product 1              1234
> Product 2              5678
> etc......
>
> However I want to reformat the results so I end up with this
>
> "Product 1"    **    0.05    **    http://www.website.com/1234.aspx
> "Product 2"    **    0.05    **    http://www.website.com/5678.aspx
> etc.....
>
> I need quotes around the product name, the 2 stars in columns B and D and 
> the 0.05 figure in Column C in every row and in Column D add some text 
> before and after the ProductID to form a url. I've studied help but can't 
> see how to accomplish this.
>
> Thanks,
> Jon
> 


0
nothanks4548 (968)
12/6/2004 6:16:00 PM
Hi Earl,

The spreadsheet is for a google adwords keywords list and has to be in this 
exact format, with the quotes astericks etc. I've got it working thanks to 
your reply however there's quite a lot of data involved, around 1000 
products and it needs updating every 3 days so ideally I'd like to automate 
the whole job - I'm thinking perhaps some kind of macro. I don't know Excel 
very much atall but I'm an asp.net/vb.net programmer so should be able to 
figure out the code if you can tell me 2 things
1/ would it be possible to write some kind of macro to do this job?
2/ where should I start looking to get started?

Thanks for your help,
Jon

"Earl Kiosterud" <nothanks@nospam.com> wrote in message 
news:OTWVm%2372EHA.1300@TK2MSFTNGP14.phx.gbl...
> Jon,
>
> Not sure why you want quote marks around the column A data.  Can you 
> elaborate?
>
> This could be done with a formula in another column, presuming the product 
> name in column A:
> ="""" & A2 & """"
> The formula could be in another sheet, in column A, to get your layout. 
> It would look like:
> ="""" & Sheet1!A2 & """"
>
> You can put the asterisks in columns B and D and copy down with the fill 
> handle.
>
> For the web address, presuming the data is in C2:
> ="http://www." & Sheet1!C2 & ".com"
>
> This won't make a hyperlink of it.  You need to do Insert - Hyperlink on 
> each cell for that.  A macro could go through the column and do that.
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Jon Spivey" <jons@mvps.org> wrote in message 
> news:eb7eSH62EHA.1404@TK2MSFTNGP11.phx.gbl...
>> Hi,
>> Using Excel 2003 Pro.
>>
>> I've figured out how to pull an XML file from a remote web server into 
>> Excel and map the 2 fields I'm interested in (ProductID and ProductName) 
>> to cells, so the data looks like this
>>
>> ProductName        ProductID
>> Product 1              1234
>> Product 2              5678
>> etc......
>>
>> However I want to reformat the results so I end up with this
>>
>> "Product 1"    **    0.05    **    http://www.website.com/1234.aspx
>> "Product 2"    **    0.05    **    http://www.website.com/5678.aspx
>> etc.....
>>
>> I need quotes around the product name, the 2 stars in columns B and D and 
>> the 0.05 figure in Column C in every row and in Column D add some text 
>> before and after the ProductID to form a url. I've studied help but can't 
>> see how to accomplish this.
>>
>> Thanks,
>> Jon
>>
>
> 


0
jons9339 (2)
12/7/2004 11:52:43 AM
Jon,

First, need to know your version of Excel.  You might want to take a look at 
"Excel and text files" at www.smokeylake.com/excel.  It talks of reading and 
importing text files with various versions of Excel.

The formulas I suggested could be replaced by macro code.  The reading or 
importing can be captured with the macro recorder.  Then switch to the VBE 
(Visual Basic Environment) and open the module and examine the generated 
code.  The other stuff will have to be written from scratch.  There are lots 
of books on VBA, most any of which will probably be useful.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jon Spivey" <jons@mvps.org> wrote in message 
news:OjxvENF3EHA.3504@TK2MSFTNGP12.phx.gbl...
> Hi Earl,
>
> The spreadsheet is for a google adwords keywords list and has to be in 
> this exact format, with the quotes astericks etc. I've got it working 
> thanks to your reply however there's quite a lot of data involved, around 
> 1000 products and it needs updating every 3 days so ideally I'd like to 
> automate the whole job - I'm thinking perhaps some kind of macro. I don't 
> know Excel very much atall but I'm an asp.net/vb.net programmer so should 
> be able to figure out the code if you can tell me 2 things
> 1/ would it be possible to write some kind of macro to do this job?
> 2/ where should I start looking to get started?
>
> Thanks for your help,
> Jon
>
> "Earl Kiosterud" <nothanks@nospam.com> wrote in message 
> news:OTWVm%2372EHA.1300@TK2MSFTNGP14.phx.gbl...
>> Jon,
>>
>> Not sure why you want quote marks around the column A data.  Can you 
>> elaborate?
>>
>> This could be done with a formula in another column, presuming the 
>> product name in column A:
>> ="""" & A2 & """"
>> The formula could be in another sheet, in column A, to get your layout. 
>> It would look like:
>> ="""" & Sheet1!A2 & """"
>>
>> You can put the asterisks in columns B and D and copy down with the fill 
>> handle.
>>
>> For the web address, presuming the data is in C2:
>> ="http://www." & Sheet1!C2 & ".com"
>>
>> This won't make a hyperlink of it.  You need to do Insert - Hyperlink on 
>> each cell for that.  A macro could go through the column and do that.
>> -- 
>> Earl Kiosterud
>> mvpearl omitthisword at verizon period net
>> -------------------------------------------
>>
>> "Jon Spivey" <jons@mvps.org> wrote in message 
>> news:eb7eSH62EHA.1404@TK2MSFTNGP11.phx.gbl...
>>> Hi,
>>> Using Excel 2003 Pro.
>>>
>>> I've figured out how to pull an XML file from a remote web server into 
>>> Excel and map the 2 fields I'm interested in (ProductID and ProductName) 
>>> to cells, so the data looks like this
>>>
>>> ProductName        ProductID
>>> Product 1              1234
>>> Product 2              5678
>>> etc......
>>>
>>> However I want to reformat the results so I end up with this
>>>
>>> "Product 1"    **    0.05    **    http://www.website.com/1234.aspx
>>> "Product 2"    **    0.05    **    http://www.website.com/5678.aspx
>>> etc.....
>>>
>>> I need quotes around the product name, the 2 stars in columns B and D 
>>> and the 0.05 figure in Column C in every row and in Column D add some 
>>> text before and after the ProductID to form a url. I've studied help but 
>>> can't see how to accomplish this.
>>>
>>> Thanks,
>>> Jon
>>>
>>
>>
>
> 


0
nothanks4548 (968)
12/8/2004 4:45:09 AM
Reply:

Similar Artilces:

XML Header
Hi, I have some XML doc loaded in a C# Web Service. XmlDocument doc = new XmlDocument(); doc.LoadXml(myDoc); Code Missing to manipulate header (I would like to replace or manupulate all these XXXXX): <mso-XXXXXX productversion="XXXXXX"> <mso-application progid="XXXXX"?> doc.Save(myNewDoc); Any idea on how to access and manipulate data within the doc header? Thanks vbdev vb6dev2003 wrote: > I have some XML doc loaded in a C# Web Service. > XmlDocument doc = new XmlDocument(); > doc.LoadXml(myDoc); > > Code Missing to manipulate header ...

Newbie Questions
This must be incredibly easy, but I've poked around in most of the chart options I can find and haven't been able to see it. I have a sheet that looks something like: 6/11/05 6/12/05 6/13/05 ... Inventory Totals A 419 431 432 ... B 145 145 144 ... I'm charting only the values in the A row, 419, 434, etc. An appropriate range is shown on the Y axis. I have the X axis formatted as dates, but I get the dates 1/1, 1/2, etc. How do I tie the X axis labels to the dates in my data? Another question: This data will grow indefini...

Licensing question Terminal Services
Hey, I've got the following setup: - Windows SBS 2008 OEM on two machines (one AD/DC and one with the MS SQL 2008 server on it) - Windows Server 2008 Standard on a third machine acting as the Terminal Server I've purchased five TS User CALs and installed them on the TS machine - working, four used. I'm not very sure about two things though. If I buy new TS CALs do I have to buy five CALs for the SBS, too? And if, wouldn't a Windows Server CAL instead of the more expensive SBS CAL be enough? Thank you in advance -- pannal --------------------------...

Read XHTML into XML
Hi all, I need to read/parse XHTML aspx pages and look for certain tokens and content. How can I use a XmlTextReader for this? If not, any other ideas? Thanks in advance, JA Reyes. Jose Antonio Reyes wrote: > I need to read/parse XHTML aspx pages and look for certain tokens and > content. How can I use a XmlTextReader for this? If not, any other ideas? If the pages are well-formed XHTML then it is possible to use XmlReader (in .NET 2.0/3.0) or XmlTextReader (in .NET 1.x) to parse the XHTML documents. You can also use the other XML APIs .NET provides so using XPathNavigator and/o...

How to invoke the xml editor?
I installed vs2005 on a new xp pro machine, and installed everything except C++. When I click on a file with a ".xml" extension it is opened with IE. When I click file/Edit with XML Editor, it opens it with Notepad. If I click File/Open With and select XML Editor, it opens it with IE. So, where is the xml editor? Can it be invoked from withing VS? Where? Bill * billsahiker@yahoo.com wrote in microsoft.public.dotnet.xml: >I installed vs2005 on a new xp pro machine, and installed everything >except C++. When I click on a file with a ".xml" extension it is >opened ...

os upgrade questions
The backroom system at one of our stores was still on Windows 98. I wanted to upgrade to to XP sp2. I installed the upgrade but when the system booted up the sql server would not connect. It said that it was not a installed service. After many new grey hairs I was able to unistall the XP and get the system up and running with only 15 minutes to spare before the store opened. Do I need to reinstall the server inorder to do this upgrade? Could someone please give me some assistance on this? I am using the sql version that came with the retail management software. On Jan 12, 9:17...

I want to use excel to keep track of my book collection, but I have a question...
Hi, I want to create a database of sorts using excel to keep track of my book collection, but I have a question. Essentially for each book listed on a row I want a column for each of the following: book title, author, publisher, year, and genre. I've used Excel before and have seen a spreadsheet with a worksheet like the above which had a second worksheet that had a button marked "Add book details" When you clicked it you filled the info above into a few boxes and then it would actually add the info you typed in, into the first worksheet and even sorted it into the corr...

Ex 5.5 question
I currently have an Ex5.5 SP4 server running on a Domain Controller that is part of ForestA/DomainA. I need to demote the domain controller (dcpromo) and then promote it again into it's OWN forest and domain as a standalone DC. The question is what will this do to Exchange 5.5 on the server? (when I re-promote the DC I plan to give it the same computer/domain name). Thanks. Exchange 5.5 is a self contained directory so the act of demotion should have no impact. What ADCs? Do you have any? How are you managing users? You also need to make sure any name resolution for your Exch...

Immediate Window question
hi a) how can I open the Immediate Window from code? (not <ctrl> G) b) can I 'clear' the Immediate Window from code? (like <ctrl> A, delete) Thank you -- cinnie "cinnie" <cinnie@discussions.microsoft.com> wrote in message news:545437CA-4488-451E-BE7E-B5EC43606102@microsoft.com... > hi > a) how can I open the Immediate Window from code? (not <ctrl> G) > b) can I 'clear' the Immediate Window from code? (like <ctrl> A, delete) > Thank you > -- > cinnie Neither of those actions are available in VBA. Your...

show XML schema and values
I have an XML document that looks like the following: <record> <Field1>value1</Field1> <Field2>value2</Field2> <Field3>value3</Field3> </record> What I'm aiming to do is to show the data in a report as in the following example: record: Field1 value1 Field2 value2 Field3 value3 The "Field1-3"-elements can have any name, so I'm trying to get a table that has in one result column the element name, and in the second colum...

Range Sum Question
Ok, I have a open question on summing a range. Here is my situation, jus need to know if there is a formula that I can apply to make my lif simple. I have months going across a row (Jan through Dec) and my categorie down the columns. This is current month data and I want to have column off to the right of my data where I can quickly determine th YTD values. Therefore, if I have data from Jan through Sep, and I wan to see what the YTD is through August for my data. Is there a way t have a "key" that I can input into to adjust the sum function to su just though August? Any s...

Printing questions (partII)
I have this spreadsheet with a calendar for a whole year. Each month is on a separate tab and covers a whole page when printed. I would like these features if they are possible. 1- have a button on each page that prints the actual month (page that is been looked at), and the following month. So if somebody is looking at the month of May, and clicks the button, automatically the June, July, August, Sep... and so on must be printed. This means that the button has to be changed for every month, but that's no problem. I need a macro that prints more than one sheet. 2- this one is tricky: if...

Vlookup Question 04-21-10
Hi Every One, I hope you all are doing well, I required your help to resolve my issue in excel. On single worksheet i have two different sheet name as 1.IP Range 2. Result. Question: EG:IP Range Start IP End IP Area 192.168.1.4 195.182.254.254 AG 10.15.33.10 10.18.56.254 EMEA 10.128.33.5 10.132.40.60 AP Here it will Continued as well. On Result sheet i required result like: IP Area 192.170.30.30 AG 194.168.10.20 AG 10.131.37.20 EMEA 10.170.255.255 NA 10.129.36.8 AP I required result on area column. Here we have more than 50000 IP's but very difficul...

Offline synch using XML web-services
This is a bit long-winded. Can somebody read thru these 2 use cases, and tell me if it is feasible ? :- Use Case : Replication before Offline Synch Scenario 1 : Oracle Server , MSDE client 1. User clicks on 'Go Offline'. System creates a MSDE SQL database in laptop (client), gets the schemas of the tables in Oracle, creates corresponding tables in client's MSDE SQL (mapping Oracle datatypes to SQL datatypes). 2. Offline replication program reads in the tables of the server into a typed datasets, converts the typed datasets into XML, and stores the XML in the client. (XML serial...

Text Formula Question
In COL A, I have a list of 2 and 3 digit charactors. I would like to seperate each string into the three adjacent columns. KQ AJs TT 89s KQ K Q AJs A J s TT T T 89s 8 9 s I know that I can use the Left function for the first col, but cant figure out how to get just the middle or third letters. Thanks in advance, Andrew Check your last post! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------...

Beginner question #2
Please forgive what might be a beginner question. I have a spread sheet that is made up of 10 columns. I would like to make it so that each time I change a number in these columns, the sub-total at the bottom of each column changes automatically AND so that the sub-total in the far right column changes automatically. Is there a template for this?? Thanks so much!!! You would use a function for that, lets say you have values in A1 to A10, then in A11 place this =SUM(A1:A10), or =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10. got the idea?? "jenny" <jenny@hotmail.net> skrev i melding ne...

stupid question #2
i have a spreadsheet that the last person working on it put last name and first name in the same cell i would like it to have a last name column and a first name column. is it possible to split the contents with out having to retype it all. P.S. there are over 1000 entries ;O) Select the cells with names in and then:- data|text to columns and use the wizard Mike "Kimberly" wrote: > i have a spreadsheet that the last person working on it put last name and > first name in the same cell i would like it to have a last name column and a > first name column. > is it p...

unauthorized password and security question change
I lost ability to log into my Windows Live hotmail acct last night due to my password being changed without my knowledge. I was not able to reset my password because my security question was also eliminated and an alternative email address that belongs to an unknown person was entered instead. So now I cannot request password info without a total stranger receiving it. I need this fixed and have spent all night trying to find a real person to talk to, to help me with this. I had to set up a second email acct just to engage in this discussion, but it does not help me gain contro...

XML Serialization ... how to truncate <?xml...
While xml serializing object how can I truncate <?xml ...?> this line alone? I need to send the serialized xml data to a web method ... since SOAP already have <?xml tag there was some confusion Any help would be greatly appreciated Thank you Regards Raj Raj wrote: > While xml serializing object how can I truncate <?xml ...?> this line alone? Serialize to an XmlWriter created with XmlWriterSettings where you set OmitXmlDeclaration to true. -- Martin Honnen --- MVP XML http://msmvps.com/blogs/martin_honnen/ ...

String to xml document
Hi there, I was hoping someone can help me with a little problem I can't seem to find any answers to. I've got a form wich will be posted. On receiving the post I would like to parse a formvar to a xml document. The contents of this formvar (string) will be a valid xhtml document. Can someone give me a startertip (or more :-)) on how to achieve this? tnx in advance, James van der Veen Sjeems wrote: > I was hoping someone can help me with a little problem I can't seem to find > any answers to. > > I've got a form wich will be posted. On receiving the p...

Re: Noobish question about DBGrid and .mdb files
Sorry I forgot - DBGrid controls are for RDO - DataGrid controls are for ADO - in my experience DataGrid controls are better. "Clive Minnican" <clive@mail.com> wrote in message news:... > You need to create an ADO Data Control first... See the doc below taken from > Visual C++ 6 online help (Using ADO Databinding in Visual C++). I started > off using the DBGrid control but I now prefer to populate a CListCtrl > manually from a CRecordset because you get a lot more control. It's not as > hard as it sounds either - just read up on CDatabase and CRecordset and ...

Beginner of XML
I am a beginner of XML. I want to group some files together with a xml file. (it includes a text file, and an image file and a video file) Can someone show me some guidelines? Please help. ...

Valid xml to read from an xml file?
I'm using vb.net, i get an error at the reader line saying the remote server can't be found. Is the below even valid? dim reader as XmlTextReader reader = new XmlTextReader ("http://www.mysite.com/my.xml") do while (reader.Read()) 'do some action here response.write(reader) loop thanx chumley XmlTextReader uses System.Net.WebRequest under the covers -- see if you can figure out how to create a connection that way. Usually there's some proxy server information missing that you can enable if you use WebRequest directly, and then pass the result of GetResponse()...

Questions on Activities
We have a few questions on Activities and History in CRM - 1) Our major issue is the fact we can't customise this section at all :o( Will it be possible to customise the layouts and database schema for activities in the Intl. version of CRM? The major problem with this is the fact that the Subject field is free text - we want to restrict this to a picklist. We're surprised that Category and Sub Category are also free text :o( 2) There doesn't seem to be a way of knowing whether an activity had been completed or cancelled in the UI! 3) Why can't you create an appointment in w...

Data vs. forms & Unload/reload questions
A .mdb file seems to encompass both the database and all the 'code' (forms, modules, and logic) used to CRUD the data in the tables. 1) Is there any way to separate the database from the code into separate files, eg. the db is mydata.mdb and the 'code is in a separate file, eg. code.mdb? 2) Say you have v1.0 of an app and the tables and attributes are populated with data. You then go and build v2.0 of the app, adding tables and columns to what already existed in v1.0, and perhaps moving some of the v1.0 attributes into different tables. What is the recommended way to unload/reloa...