Loading two XML files (Excel)

[Alex Shirley]

Hi

I'm trying to load two XML files into a OWC Excel Spreadsheet (ver
10.0.6619).

This will load will load one of the files:

oSS.XMLURL = "filepath"
set oSheet = oSS.Worksheets("bulkimport")

Add this code and the second worksheet will load, but will wipe out
all trace of the data in the first worksheet

oSS.Worksheets.Add oSS.Worksheets(1), ,1
oSS.ActiveSheet.Name = "Countries"
oSS.XMLURL = ("another filepath")
Set oValidate = oSS.Worksheets("Countries")

What am I doing wrong?

Many thanks

Alex

 

[Alex Shirley]

OK I see now that doing this will simply point the spreadsheet to
another spreadsheet file (therefore the 1st spreadsheet will
definately disappear).

So does anybody know now to get two seperate XML files into two
seperate worksheet as part of one spreadsheet?

Many thanks

Alex

 

[Guest]

what exactly are you trying to do?

OK I see now that doing this will simply point the spreadsheet to
another spreadsheet file (therefore the 1st spreadsheet will
definately disappear).

So does anybody know now to get two seperate XML files into two
seperate worksheet as part of one spreadsheet?

Many thanks

Alex

(e-mail address removed) (Alex Shirley) wrote in message

 

[Andy Tischaefer [MSFT]]

There are a couple of ways to do this. One would be to merge your two XML
documents into one, new XML Spreadsheet file that creates two sheets, one
for each original XML Document.

The second would be to use the Range.Value property, but this is going to be
a little messy. You can do this by setting the Value property of the range
on the spreadsheet that you want to put the XML data into. Range.Value
takes an optional parameter that allows you to specify that the data you
want in the range will be in XMLSpreadsheet format.

spreadsheet.sheets(1).range("A14").value(spreadsheet.constants.xlRangeValueXMLSpreadsheet)
= <xml from the file1>
spreadsheet.sheets(2).range("A14").value(spreadsheet.constants.xlRangeValueXMLSpreadsheet)
= <xml from the file2>

The downside of this is that you either have to know how much data you want
to put in your empty sheets 1 and 2 (this is easy if the file you are
loading always looks the same, but complicated if you don't). If you don't
do this you'll either end up with a truncated spreadsheet (because you
didn't specify a big enough range) or you'll end up with a bunch of #N/As in
cells (because you specified too big of a range).

I hope this is helpful, though I realize it isn't optimal.

- Andy Tischaefer
Test Lead, Microsoft Office

This posting is provided as is, and confers no rights

 

[postings]

Thanks Andy.

I shall try this.

Alex

There are a couple of ways to do this. One would be to merge your two XML
documents into one, new XML Spreadsheet file that creates two sheets, one
for each original XML Document.

The second would be to use the Range.Value property, but this is going to be
a little messy. You can do this by setting the Value property of the range
on the spreadsheet that you want to put the XML data into. Range.Value
takes an optional parameter that allows you to specify that the data you
want in the range will be in XMLSpreadsheet format.

spreadsheet.sheets(1).range("A14").value(spreadsheet.constants.xlRangeValueXMLSpreadsheet)

= <xml from the file1>
spreadsheet.sheets(2).range("A14").value(spreadsheet.constants.xlRangeValueXMLSpreadsheet)

= <xml from the file2>

The downside of this is that you either have to know how much data you want
to put in your empty sheets 1 and 2 (this is easy if the file you are