Whoever can figure this out... (revised)

[Guest]

Hi everyone!

I am creating the following sample form called "Apples."

File Name = Iloveapples
apple 1 = excellent
apple 2 = good
apple 3 = bad

There are multiple records to this form.

QUESTION: Am I able to export a TEXT file PER record in Access 2003??

I have the code below in my code for my command button called "ReporttoFile"
and am able to generate separate text files for EACH record. However, the
content in the text files come in the XML format which is not very readable.
This is because in my code it says "Application.ExportXML."

Here's my code:

Private Sub ReporttoFile_Click()
Dim rsR As DAO.Recordset

Set rsR = CurrentDb.OpenRecordset("Apples", dbOpenSnapshot)

Do Until rsR.EOF
Application.ExportXML acExportForm, "Apples", _
"C:\Temp\Metadata\" & rsR.Fields("File Name =").Value & ".txt", , , ,
, , _
"[File Name =] = '" & rsR.Fields("File Name =").Value & "'"
rsR.MoveNext
Loop

End Sub



This is how my text file looks like:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlnsd="urn:schemas-microsoft-comfficedata"
generated="2007-10-04T13:37:29">
<_Apples>
<File_x0020_Name_x0020__x003D_>Iloveapples</File_x0020_Name_x0020__x003D_>
<apple1_x0020__x003D_>excellent</apple1_x0020__x003D_>
<apple2_x0020__x003D_>good</apple2_x0020__x003D_>
<apple3_x0020__x003D_>bad</apple3_x0020__x003D_>


and so forth...


QUESTION2: How can I get rid of the XML format, specifically the
"x0020_x003D" and transform it into text??


I would greatly appreciate any advice on this!

Thank you!

 

[pietlinden]

So what are you really trying to do? Export each individual record to
a separate text file?

 

[Guest]

Yes, that's correct!

 

[Guest]

Hi everyone!

I am creating the following sample form called "Apples."

File Name = Iloveapples
apple 1 = excellent
apple 2 = good
apple 3 = bad

There are multiple records to this form.

QUESTION: Am I able to export a TEXT file PER record in Access 2003??

I have the code below in my code for my command button called "ReporttoFile"
and am able to generate separate text files for EACH record. However, the
content in the text files come in the XML format which is not very readable.
This is because in my code it says "Application.ExportXML."

Here's my code:

Private Sub ReporttoFile_Click()
Dim rsR As DAO.Recordset

Set rsR = CurrentDb.OpenRecordset("Apples", dbOpenSnapshot)

Do Until rsR.EOF
Application.ExportXML acExportForm, "Apples", _
"C:\Temp\Metadata\" & rsR.Fields("File Name =").Value & ".txt", , , ,
, , _
"[File Name =] = '" & rsR.Fields("File Name =").Value & "'"
rsR.MoveNext
Loop

End Sub



This is how my text file looks like:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlnsd="urn:schemas-microsoft-comfficedata"
generated="2007-10-04T13:37:29">
<_Apples>
<File_x0020_Name_x0020__x003D_>Iloveapples</File_x0020_Name_x0020__x003D_>
<apple1_x0020__x003D_>excellent</apple1_x0020__x003D_>
<apple2_x0020__x003D_>good</apple2_x0020__x003D_>
<apple3_x0020__x003D_>bad</apple3_x0020__x003D_>


and so forth...


QUESTION2: How can I get rid of the XML format, specifically the
"x0020_x003D" and transform it into text??


I would greatly appreciate any advice on this!

Thank you!


Hi, I don't know if this is helpful, but I have written some code to send banking data to a text file. In my case I get data from 3 different tables into ONE text file, but if you were to bring the opening / closing of each file into the loop, maybe that would work. To delete previously created files, "Kill FilePathAndName can be coded in... Even if it does not help directly it might give you an idea...

Private Sub SubCreateTextFile()
'=======================
Dim MyDB As Database
Dim MyRS As Recordset
Dim FilePathAndName as string

Set MyDB = CurrentDb 'Define Database

'First Step: Select Export Table for Header
Set MyRS = _
MyDB.OpenRecordset("TblTxtExportToBank1", dbOpenDynaset, dbSeeChanges)

'Open File ready to write to
FilePathAndName = "C:\MyFolder\MyFileName.txt"
Open FilepathAndName For Output As #1

'Write Statement Header to File
MyRS.MoveFirst
Print #1, MyRS![RecordType] & ",,,," & _
MyRS![MyAccountNo] & "," & _
MyRS![BatchType] & "," & _
MyRS![BatchDueDateYYMMDD] & "," & _
MyRS![TodayYYMMDD] & "," 'Indicator blank field as end
MyRS.Close 'End Header Record

'2nd Step Select Transaction Table for Transaction Records
Set MyRS = _
MyDB.OpenRecordset("TblTxtExportToBank2", dbOpenDynaset, dbSeeChanges)


'Write Transactions to File
MyRS.MoveFirst
Do Until MyRS.EOF
Print #1, MyRS![RecordType] & "," & _
MyRS![AccNo] & "," & _
MyRS![TransactionCode] & "," & _
MyRS![Amount] & "," & _
MyRS![AccountName] & "," & _
MyRS![Reference] & "," & _
MyRS!

 & ",," & _
MyRS![Particulars] & "," & _
MyRS![SubscriberName] & ",," & _
MyRS![CMIRef] & "," & _
MyRS![CMIPart]
MyRS.MoveNext
Loop

MyRS.Close 'End Transaction Records

'3rd Step Select Summary Table for Control Records
Set MyRS = _
MyDB.OpenRecordset("TblTxtExportToBank3", dbOpenDynaset, dbSeeChanges)

MyRS.MoveFirst
Print #1, MyRS![RecordType] & "," & _
MyRS![BatchTotal] & "," & _
MyRS![CountRec] & "," & _
MyRS![HashTotal]
MyRS.Close 'End Control Record

Close #1 'Close Text file
Set MyDB = Nothing

End Sub