doesn't read all data from cells into xml file
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 |
doesn't read all data from cells into xml file
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 "Co" wrote: 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 |
doesn't read all data from cells into xml file
I see a coupld of places where you just have
If Cells ... instead of If .Cells -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Co" wrote: 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 |
All times are GMT +1. The time now is 03:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com