Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to read all lines from a text file into cells C10:Cxxx ? | Excel Discussion (Misc queries) | |||
How to read data from binary file? | Excel Programming | |||
Read data from an htm file | Excel Programming | |||
How read a csv file to load named cells | Excel Programming | |||
VBA to read data from XL and import into another XL file | Excel Programming |