Apply a transform on export with Excel

[prgmrgirl]

Howdy everyone,

We have a spreadsheet with lots of data. It has a schema and can be imported/exported just fine. However, I would like to apply a transform to the data on export. Basically, I'd like to pull only some of the records into xml based on a criteria I define. For example, on export I want to only export records where State='WI'.

Is there a way to transform the data before export?

Thanks!
prgmrgirl

 

[SkipVought]

Hi,

MS Query via Data/Get External Data...

Using MS Query to get data from Excel faq68-5829

Skip,

_[red][/red]

 

[prgmrgirl]

Hi Skip,

Unfortunately, the way that this data is set up doesn't meet the criteria for using MSQuery. The data is in an Excel list and has an attached schema. We want to be able to pull this data out as XML for reuse in other areas. What I would like to be able to do is apply an xslt to the xml data on export. I have the xslt written and ready to boogie. Just not sure if there's a way to apply it, either using the regular menus in Excel or via vba in the BeforeXMLExport event.

Thanks guys!
prgmrgirl

 

[SkipVought]

Well then, how is the data set up?

Skip,

_[red][/red]

 

[prgmrgirl]

Wow, that was fast! LOL

Well, the data is in an Excel list (that is mapped to a schema). However, there are other things on the spreadsheets that we don't want to pull. In order to use MSQuery, I believe the data has to be structured as a table with nothing else on the sheet but the data.

I was really hoping there was some easy way of doing this,like

Sheet(1).ApplyTransform("myTransform.xslt")

I don't suppose there's any built in method in Excel like that? LOL