C
Co
Hi all,
I'm trying to read data from cells into an xml file.
The code cycles trhough the active sheet instead of reading all the
tabs.
What's wrong?
Sub GenerateXML()
Dim oWorkbook As Workbook
Dim oSh As Object
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim i As Integer
Set fs = CreateObject("Scripting.FileSystemObject")
Set MyPrint = fs.CreateTextFile("J:\CLASSINT\SENID
\TempKL1\mil_equipment.xml", True)
MyPrint.WriteLine ("<?xml version=" & Chr(34) & "1.0" & Chr(34) & "
encoding=" & Chr(34) & "ISO-8859-1" & Chr(34) & "?>")
MyPrint.WriteLine ("<Grammars>")
MyPrint.WriteLine ("<Dictionary name=" & Chr(34) & "Mil_Equipment" &
Chr(34) & ">")
Set oWorkbook = ActiveWorkbook
For i = 1 To Sheets.Count
With Sheets(i).UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
For ColNdx = StartCol To EndCol
For RowNdx = StartRow To EndRow
If Cells(RowNdx, ColNdx).Value = "" Then
GoTo empty_cells
Else
CellValue = Cells(RowNdx, ColNdx).Text
End If
If RowNdx = StartRow Then
MyPrint.WriteLine ("<EntrySet name=" & Chr(34) &
CellValue & Chr(34) & " case=" & Chr(34) & "off" & Chr(34) & ">")
Else
MyPrint.WriteLine ("<Entry headword=" & Chr(34) &
CellValue & Chr(34) & ">")
End If
empty_cells:
Next RowNdx
MyPrint.WriteLine ("</EntrySet>")
Next ColNdx
Next i
MyPrint.WriteLine ("</Dictionary>")
MyPrint.WriteLine ("</Grammars>")
MyPrint.Close
End Sub
Regards
Marco
I'm trying to read data from cells into an xml file.
The code cycles trhough the active sheet instead of reading all the
tabs.
What's wrong?
Sub GenerateXML()
Dim oWorkbook As Workbook
Dim oSh As Object
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim i As Integer
Set fs = CreateObject("Scripting.FileSystemObject")
Set MyPrint = fs.CreateTextFile("J:\CLASSINT\SENID
\TempKL1\mil_equipment.xml", True)
MyPrint.WriteLine ("<?xml version=" & Chr(34) & "1.0" & Chr(34) & "
encoding=" & Chr(34) & "ISO-8859-1" & Chr(34) & "?>")
MyPrint.WriteLine ("<Grammars>")
MyPrint.WriteLine ("<Dictionary name=" & Chr(34) & "Mil_Equipment" &
Chr(34) & ">")
Set oWorkbook = ActiveWorkbook
For i = 1 To Sheets.Count
With Sheets(i).UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
For ColNdx = StartCol To EndCol
For RowNdx = StartRow To EndRow
If Cells(RowNdx, ColNdx).Value = "" Then
GoTo empty_cells
Else
CellValue = Cells(RowNdx, ColNdx).Text
End If
If RowNdx = StartRow Then
MyPrint.WriteLine ("<EntrySet name=" & Chr(34) &
CellValue & Chr(34) & " case=" & Chr(34) & "off" & Chr(34) & ">")
Else
MyPrint.WriteLine ("<Entry headword=" & Chr(34) &
CellValue & Chr(34) & ">")
End If
empty_cells:
Next RowNdx
MyPrint.WriteLine ("</EntrySet>")
Next ColNdx
Next i
MyPrint.WriteLine ("</Dictionary>")
MyPrint.WriteLine ("</Grammars>")
MyPrint.Close
End Sub
Regards
Marco