1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
| ...
fichierxml = "j:\test\11.xml"
XSLT_StyleSheet = "<xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"">" & vbCrLf & _
" <xsl:output method=""text"" indent=""no"" encoding=""ISO-8859-1""/>" & vbCrLf & _
" <xsl:template match=""/"">" & vbCrLf & _
" <xsl:for-each select=""/billets/billet"">" & vbCrLf & _
" <xsl:value-of select=""PN""/><xsl:text>	</xsl:text>" & vbCrLf & _
" <xsl:value-of select=""SD""/><xsl:text>	</xsl:text>" & vbCrLf & _
" <xsl:value-of select=""MC""/><xsl:text>	</xsl:text>" & vbCrLf & _
" <xsl:value-of select=""TN""/><xsl:text>	</xsl:text>" & vbCrLf & _
" <xsl:value-of select=""LS""/><xsl:text>	</xsl:text>" & vbCrLf & _
" <xsl:text>
</xsl:text>" & vbCrLf & _
" </xsl:for-each>" & vbCrLf & _
" </xsl:template>" & vbCrLf & _
"</xsl:stylesheet>"
Dim StyleSheet
Set StyleSheet = CreateObject("Msxml2.DOMDocument.4.0")
StyleSheet.async = False
StyleSheet.LoadXML XSLT_StyleSheet
Dim fichier
Set fichier = CreateObject("Msxml2.DOMDocument.4.0")
fichier.async = False
fichier.Load fichierxml
Dim data
data = fichier.transformNode(StyleSheet)
Dim objFSO, objFile, appliExcel
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("j:\test\test.xls")
objFile.Write data
objFile.Close
Set appliExcel = GetObject("", "excel.application")
appliExcel.Visible = True
appliExcel.Workbooks.Open ("j:\test\test.xls")
i = CLng(appliExcel.Worksheets(1).UsedRange.Rows.Count)
SortColumnA = "A1:A" & i
SortColumnB = "B1:B" & i
SortColumnC = "C1:C" & i
SortColumnA_E = "A1:E" & i
appliExcel.Worksheets(1).Range(SortColumnA_E).Select
With appliExcel
ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range(SortColumnA), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range(SortColumnB), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range(SortColumnC), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(1).Sort
.SetRange Range(SortColumnA_E)
.Header = Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes
.MatchCase = False
.Orientation = Excel.Constants.xlTopToBottom
.SortMethod = Excel.XlSortMethod.xlPinYin
.Apply
End With
End With
... |
Partager