XML Question

[Jon Spivey]

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

 

[Earl Kiosterud]

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.

 

[Jon Spivey]

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]

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.