jag ska öppna ett excell dokument och presentera data från en xml fil i denna. vet någon hur man gör detta eller om det finns någon sida som tar upp liknande problem?

Tacksam för all hjälp!

Johan

- Spara XML-filen till CSV-filformatet och läsa in den i XL.
- Använda sig av XL 2002 / XP
- http://www.xmlspy.com/
- http://monarch.datawatch.com/

eller börja med att studera följande case och skriva själv:

<code>

'<?xml version="1.0"?>
'<book-list >
' <book >
' <title>The Brethren</title>
' <author>John Grisham</author>
' <format pages="366">Hardcover</format>
' </book>

' <book >
' <title>Harry Potter Book 4</title>
' <author>J.K. Rowling</author>
' <format>Hardcover</format>
' <availability>Not Yet Published</availability>
' </book>

' <book >
' <title>The Naked Chef</title>
' <author>Jamie Oliver</author>
' </book>
'</book-list>



' XML import for Excel,
'
' Original copyright © 2000, by Codesmiths,
' posted into the public domain 20/2/2000
'
'

Option Explicit


Const NODE_ELEMENT = 1


' User interface sheet
Dim mwkbControl As Excel.Workbook
Dim mwksControl As Excel.Worksheet


' Better to IDispatch these by declaring them as Objects,
' as we don't want to get a versioning problem in the future
Dim mobjXMLDOM As MSXML.DOMDocument ' Object
Dim mobjXMLDocument As MSXML.IXMLDOMNode
Dim mobjXMLListElement As MSXML.IXMLDOMNode


' This type controls how XML gets loaded into Excel
' This example does a trivial auto-generate of them,
' my more sophisticated example loads them from
' an extension to the schema.

Private Type mtypColumnConfig
ColumnCaption As String
ColumnIndex As Integer
ElementName As String
End Type

Private Type marytypColumnConfig
ColumnsCount As Integer
TheArray() As mtypColumnConfig
End Type

Private maryColumnConfig As marytypColumnConfig



Private Sub SetStatusBarMsg(pstrMsg As String)

mwksControl.Range("Out_StatusMsg").Cells(1, 1).Value = pstrMsg
End Sub


Private Function OpenXMLDOM() As Object

Dim lstrMsg As String
Dim mobjXMLDOM As Object

Set OpenXMLDOM = Nothing

' Fire up the XML environment
' (do this before checking params, because it's a major system /
installation foulup if it doesn't work)
Set mobjXMLDOM = CreateObject("Microsoft.xmldom")
If mobjXMLDOM Is Nothing Then
lstrMsg = "Can't create XML DOM object"
lstrMsg = lstrMsg & vbCrLf & vbCrLf & "Error " & CStr(Err.Number)
& " : " & Err.Description

MsgBox lstrMsg, VbMsgBoxStyle.vbOKOnly Or
VbMsgBoxStyle.vbCritical, "Error"
Exit Function
End If
mobjXMLDOM.async = False

Set OpenXMLDOM = mobjXMLDOM
End Function




Private Function GetColumn(pElement As MSXML.IXMLDOMElement,
pColumnConfig As mtypColumnConfig) As Boolean
' Get hold of the appropriate column descriptor, making a new one if
needed
' Returns True if it was already there, false if added afresh

Dim i As Integer
Static ColumnCount As Integer

GetColumn = False

i = maryColumnConfig.ColumnsCount
Do While (i > 0)
If (maryColumnConfig.TheArray(i).ElementName = pElement.nodeName)
Then
pColumnConfig = maryColumnConfig.TheArray(i)
GetColumn = True
Exit Function
End If

i = i - 1
Loop

' Not there, so add a new one
ColumnCount = ColumnCount + 1

maryColumnConfig.ColumnsCount = maryColumnConfig.ColumnsCount + 1
ReDim Preserve
maryColumnConfig.TheArray(maryColumnConfig.ColumnsCount)

' Here we could become much smarter, if we wished to -- maybe read
the Schema
With maryColumnConfig.TheArray(maryColumnConfig.ColumnsCount)
.ElementName = pElement.nodeName
.ColumnIndex = ColumnCount + 1
.ColumnCaption = pElement.nodeName
End With

pColumnConfig =
maryColumnConfig.TheArray(maryColumnConfig.ColumnsCount)
End Function


Public Sub cmdImport_Click()


On Error GoTo Erh_cmdImport_Click


Dim lstrInputFilename As String
Dim lstrOutputFilename As String
Dim lstrOutputName As String

Dim lwkbImport As Excel.Workbook
Dim lwksImport As Excel.Worksheet
Dim lrngImport As Excel.Range
Dim lrngImportRecord As Excel.Range

Dim lRowIndex As Integer
Dim lColIndex As Integer
Dim i As Integer


Dim lobjXMLList As MSXML.IXMLDOMNode
Dim lobjXMLItem As MSXML.IXMLDOMNode
Dim lobjXMLItemDefinition As MSXML.IXMLDOMNode
Dim lobjXMLField As MSXML.IXMLDOMNode
Dim lobjXMLFieldDefinition As MSXML.IXMLDOMNode

Dim lColumnConfig As mtypColumnConfig



Set mobjXMLDOM = OpenXMLDOM()
If mobjXMLDOM Is Nothing Then Exit Sub


' These are read from a worksheet that has already been opened
lstrInputFilename =
Trim(mwksControl.Range("In_InputFilename").Cells(1, 1).Text)
lstrOutputFilename =
Trim(mwksControl.Range("In_OutputFilename").Cells(1, 1).Text)
lstrOutputName = lstrOutputFilename

On Error Resume Next
Set mwkbImport = Excel.Workbooks(lstrOutputName)
If mwkbImport Is Nothing Then
Set mwkbImport = Excel.Workbooks.Add(xlWBATWorksheet)
End If
On Error GoTo Erh_cmdImport_Click
lwkbImport.SaveAs (lstrOutputFilename)
Set lwksImport = mwkbImport.Sheets(1)
Set lrngImport = mwksImport.Cells

lRowIndex = 2
lColIndex = 1

If mobjXMLDOM.Load(lstrInputFilename) Then

Set mobjXMLDocument = mobjXMLDOM.documentElement

' Find the root element that contains the "list"
Set lobjXMLList = mobjXMLDocument
' Loop over the 2nd level of the hierarchy that represent the
"records"
Set lobjXMLItem = lobjXMLList.firstChild
Do Until (lobjXMLItem Is Nothing)

Set lrngImportRecord = lrngImport.Rows(lRowIndex)

' Loop over the 3rd level for the "fields"
Set lobjXMLField = lobjXMLItem.firstChild
Do Until (lobjXMLField Is Nothing)

' Get hold of the appropriate column descriptor, making a new
one if needed
GetColumn lobjXMLField, lColumnConfig

' Use .nodeTypeValue here - a little risky in this
unsophisticated example. Use .nodeValue if you're not typing with
Schemas, but don't use .Text if you have elements nested beneath !
lrngImportRecord.Cells(1, lColumnConfig.ColumnIndex).Value =
lobjXMLField.nodeTypedValue

Set lobjXMLField = lobjXMLField.nextSibling
Loop

lRowIndex = lRowIndex + 1
Set lobjXMLItem = lobjXMLItem.nextSibling
Loop

' Label the columns
For i = 1 To maryColumnConfig.ColumnsCount
With maryColumnConfig.TheArray(i)
lrngImport.Cells(1, .ColumnIndex) = .ColumnCaption
End With
Next i

mwkbImport.Save
End If


' Thankyou and goodnight
Set mobjXMLDOM = Nothing

If mblnInvokedOffControl Then mwksControl.Activate

Exit_cmdImport_Click:
Exit Sub



Erh_cmdImport_Click:
MsgBox "Error " & CStr(Err.Number) & " : " & Err.Description & " at
" & Err.Source, _
vbOKOnly Or vbCritical, "Import to Excel from XML "
GoTo Exit_cmdImport_Click

End Sub

</code>