Best Way to Populate Excel 2000 worksheet, XML? 1

[trayb]

(Previously posted in the Office forum, by mistake)

I'm looking for the best approach to find a way to export data from a working copy spreadsheet to a formatted output worksheet, both of which are Excel 2000.

Realizing this would be a lot easier if I could use Excel XP, but I'm stuck with having to use Excel 2000 for now.

So far I've used a VBA routine to identify named ranges within the working copy and generate XML data for each range. Now I'm stuck.

I've been all over the web looking for solutions and the more I search the more I get confused. I've found many solutions for creating a brand new worksheet from XML data but with my limited knowledge of XML, XLST, etc., I need to find a way to populate an existing, formatted sheet, somehow mapping the input fields to the output fields.

I'm not sure if I should create some type of dataset first then read that into the template? How? VBA/ADO?

Or is there an easier way to import the data directly into the template using the raw XML data? Is there a more appropriate/easier datasource I could use?

To complicate matters, both the input and output workbooks contain multiple sheets and multiple column headings representing relevant sections of data. The output also contains summary data from the input sheet.

Any guidance at all on this issue would be greatly appreciated!

 

[AnotherHiggins]

Can you just use the PasteSpecial method?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[trayb]

I guess you're right. I suppose I was over-complicating things. Thanks for the nudge in the right direction. I found something on another thread that suggested doing something like:

'Open source workbook
Workbooks.Open...

'Select range and paste to target workbook:
Sheets("Sheet1").Activate
Range(rangeName).Select
Selection.Copy

'Activate target workbook
ThisWorkbook.Activate
ActiveSheet.Range(rangeName).Select
Selection.PasteSpecial xlValues

Am I on the right track?

 

[AnotherHiggins]

Yes. But the macro will run much quicker if you avoid using Select.

Try something like:

Sub CopyValues()
'Select range and paste to target workbook:
 Sheets("Sheet1").Range("a1").Copy

'Activate target workbook
  Sheets("Sheet2").Range("b2").PasteSpecial xlValues
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Grr. I just left the comments that you had posted. That second comment really doesn't make sense in my code. It should look like this:

'Select range from source workbook:
Sheets("Sheet1").Range("a1").Copy

'Paste Values to target workbook
Sheets("Sheet2").Range("b2").PasteSpecial xlValues

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.