Hi Sam,
try pasting the following code into a module mate (then run the
routine called write_xml_style):
'-- code start --
Private Function return_xml_style(ByVal query_name As String) As
String
'Author: Duane Hennessy
'Company: Bandicoot Software, Australia
'Date: 3/01/2006, 12:56 PM
'Description: Returns the passed in query's/table's data as a
xml style string.
'Notes: notes_here
Const strcProcedureName As String = "return_xml_style"
On Error GoTo err_
Dim rst As ADODB.Recordset
Dim fld As Field
Dim data As String
Dim counter As Long
data = "<data>" & vbCrLf
data = data & "<variable name = " & Chr(34) & query_name & Chr
(34) & vbCrLf
Set rst = New ADODB.Recordset
rst.Open query_name, CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly
With rst
If Not .BOF And Not .EOF Then
.MoveFirst
Do Until .EOF
data = data & "<row>" & vbCrLf
'I use a counter here so we can add as many fields
we want without re-writing the code.
'!!! NOTE: Apparently the recorset's fields are a
collection but for whatever reason _
they start from an index of zero and not one. :-)
Thanks Microsoft!
For counter = 0 To rst.Fields.Count - 1
data = data & "<column>" & rst.Fields
(counter).Value & "</column>" & vbCrLf
Next counter
data = data & "</row>" & vbCrLf
.MoveNext
Loop
End If
.Close
End With
data = data & "</variable>"
data = data & "</data>"
return_xml_style = data
exit_routine:
Exit Function
err_:
MsgBox Err.Number & vbCrLf & Err.Description, 0 + 48,
strcProcedureName
GoTo exit_routine
End Function
Public Sub write_xml_style()
'Author: Duane Hennessy
'Company: Bandicoot Software, Australia
'Date: 3/01/2006, 12:55 PM
'Description: You can add as many queries here as you want. I
just used a constant for the _
example.
'Notes: notes_here
Const strcProcedureName As String = "write_xml_style"
On Error GoTo err_
Dim file_number As Integer 'Holds FreeFile number
Const file_path As String = "c:\myQuery.txt"
Const query_name As String = "MyQuery"
Dim xml_string As String
xml_string = return_xml_style(query_name)
'Create file
file_number = FreeFile(0)
Open file_path For Append As #file_number 'write to file
Print #file_number, xml_string
Close #file_number
exit_routine:
Exit Sub
err_:
MsgBox Err.Number & vbCrLf & Err.Description, 0 + 48,
strcProcedureName
GoTo exit_routine
End Sub
'-- code end --
Duane Hennessy.
Bandicoot Software
Tropical Queensland, Australia
(ABN: 33 682 969 957)
Want Increased Productivity?
http://www.bandicootsoftware.com.au
--- In AccessDevelopers@yahoogroups.com, sujith samuel
<[EMAIL PROTECTED]> wrote:
>
> I have a query(MyQuery) which gets the following, need
> help in writing
> code to convert them to the below structure and saving
> as text file. Is
> this possible? Any help would be highly appreciated.
> Thanks,
> Sujith
>
> Query:
>
> Name A B C
> ASD 20 11 1-Jan-05
> BSD 20 5 2-Jan-05
> CSD 0 0 3-Jan-05
> EFD 20 3 4-Jan-05
> GFD 10 22 5-Jan-05
>
>
> Required Structure on text file:
>
> <data>
> <variable name = "MyQuery" >
> <row>
> <column>Name</column>
> <column>A</column>
> <column>B</column>
> <column>C</column>
> </row>
> <row>
> <column>ASD</column>
> <column>20 </column>
> <column>11 </column>
> <column>01 Jan 05</column>
> </row>
> <row>
> <column>BSD</column>
> <column>20 </column>
> <column>5 </column>
> <column>02 Jan 05</column>
> </row>
> <row>
> <column>CSD</column>
> <column>0 </column>
> <column>0 </column>
> <column>03 Jan 05</column>
> </row>
> <row>
> <column>EFD</column>
> <column>20 </column>
> <column>3 </column>
> <column>04 Jan 05</column>
> </row>
> <row>
> <column>GFD</column>
> <column>10 </column>
> <column>22 </column>
> <column>05 Jan 05</column>
> </row>
> </variable>
> </data>
>
>
>
>
>
>
> __________________________________________
> Yahoo! DSL Something to write home about.
> Just $16.99/mo. or less.
> dsl.yahoo.com
>
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
