Whoever can figure this out... (revised)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andromeda031 - 05 Oct 2007 01:22 GMT

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 xmlns:od="urn:schemas-microsoft-com:officedata"
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!

Reply to this Message

pietlinden@hotmail.com - 05 Oct 2007 23:02 GMT

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

Reply to this Message

Andromeda031 - 07 Oct 2007 03:16 GMT

Yes, that's correct!

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

Reply to this Message

Lara - 15 Oct 2007 06:52 GMT

> Hi everyone!
>
[quoted text clipped - 52 lines]
>
> 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![Code] & ",," & _
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

>
>
>

Reply to this Message