doesn't read all data from cells into xml file

[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

 

[Joel]

try these changes

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)
StartRow = 1
StartCol = 1
EndRow = .Range("A" & Rows.Count).End(xlUp).Row
EndCol = .Cells(1, .Cells.Count).End(xlToLeft).Column

For ColNdx = StartCol To EndCol
For RowNdx = StartRow To EndRow
If .Cells(RowNdx, ColNdx).Value <> "" Then
CellValue = .Cells(RowNdx, ColNdx).Text
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
End If
Next RowNdx
MyPrint.WriteLine ("</EntrySet>")
Next ColNdx
End With
Next i

MyPrint.WriteLine ("</Dictionary>")
MyPrint.WriteLine ("</Grammars>")

MyPrint.Close

End Sub

 

[Barb Reinhardt]

I see a coupld of places where you just have

If Cells ... instead of
If .Cells