From Access to Excel: Loading an XML File into an Excel Workbook : Excel to XML « Excel « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Excel
3. Excel to XML

From Access to Excel: Loading an XML File into an Excel Workbook

 
Sub OpenAdoFile() 
    Dim myRecordset As ADODB.Recordset 
    Dim objExcel As Excel.Application 
    Dim myWorkbook As Excel.Workbook 
    Dim myWorksheet As Excel.Worksheet 
    Dim StartRange As Excel.Range 
    Dim h as Integer 

    Set myRecordset = New ADODB.Recordset 

    myRecordset.Open "C:\data.xml", "Provider=MSPersist" 

    Set objExcel = New Excel.Application 
    Set myWorkbook = objExcel.Workbooks.Add 
    Set myWorksheet = myWorkbook.ActiveSheet 
    objExcel.Visible = True 
        For h = 1 To myRecordset.Fields.Count 
            myWorksheet.Cells(1, h).Value = myRecordset.Fields(h - 1).Name 
        Next 
    Set StartRange = myWorksheet.Cells(2, 1) 
    StartRange.CopyFromRecordset myRecordset 
    myWorksheet.Range("A1").CurrentRegion.Select 
    myWorksheet.Columns.AutoFit 
    myWorkbook.SaveAs "C:\ExcelReport.xls" 

    Set objExcel = Nothing 
    Set myRecordset = Nothing 
End Sub 

 

Related examples in the same category