Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > doesn't read all data from cells into xml file
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Co Co is offline
external usenet poster
  
Posts: 4
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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 With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to read all lines from a text file into cells C10:Cxxx ? Claudia d'Amato Excel Discussion (Misc queries) 1 September 22nd 09 01:11 PM
How to read data from binary file? Valery Excel Programming 4 July 21st 08 04:59 PM
Read data from an htm file Mayank Gupta Excel Programming 1 March 22nd 07 10:01 AM
How read a csv file to load named cells John Excel Programming 0 March 1st 06 10:36 PM
VBA to read data from XL and import into another XL file Steve D[_4_] Excel Programming 0 August 28th 03 04:34 PM


All times are GMT +1. The time now is 05:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement