Export 1 record at a time as .TXT

[Andromeda031]

Hi everyone!

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

File Name = _______
apple 1 = _______
apple 2 = _______
apple 3 = _______

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("3D PDF Metadata", dbOpenSnapshot)

Do Until rsR.EOF
Application.ExportXML acExportForm, "Apples", _
"C:\Temp\Metadata\" & rsR.Fields("Apple Name =").Value & ".txt", , , ,
, , _
"[Apple Name =] = '" & rsR.Fields("Apple 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_>aaa</File_x0020_Name_x0020__x003D_>
<apple1_x0020__x003D_>PDF</apple1_x0020__x003D_>
<apple2_x0020__x003D_>Maintenance</apple2_x0020__x003D_>
<apple3_x0020__x003D_>EAR</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!

 

[Albert D. Kallal]

Check out the transfertext command in the help.....
(you use docmd.TransferText)

I mean, if you going to use a xml export command, it stands to reason that
your going to get xml for output.....

Also, it not clear at all how your want your resulting text file to be.

Are you looking a resulting tab delimited file? perhaps a CSV file?

do you need multiple lines for *one* record? (if yes, then you likely can't
use transfertext).

 

[Andromeda031]

Where and how would I place the docmd.TransferTest code? Please advise on
this as I'm not a super coder.

Also, the result of my text file for 1 record will look like this:

apple1 = excellent
apple2 = good
apple3 = bad

 

[Albert D. Kallal]

Also, the result of my text file for 1 record will look like this:

apple1 = excellent
apple2 = good
apple3 = bad

ah, ok, so the above requirement does change things
note from my last post:

do you need multiple lines for *one* record? (if yes, then you likely can't

use transfertext).

In fact, there is no built in ability to export data as a single text file
with multiple lines of text.

So, we can't use transfer text here as that ONLY exports data from a record
in one line..and usually the data will be comma, or tab delimited.

So, we can't really use any of the built in export features.

What this means is that we have design, build and code a custom export
routine. This not too difficult..but, it will take extra code.

So, we create a standard code module. Call the module basExportCode

Now, inside of this module, we place some code:

The code will look much like:

Public Sub MyExport()

Dim strFile As String
Dim intF As Integer

Dim strDir As String
Dim rsR As DAO.Recordset

strDir = "C:\Temp\Metadata\"
intF = FreeFile()

Set rsR = CurrentDb.OpenRecordset("3D PDF Metadata")

Do Until rsR.EOF = True

Debug.Print "Process = " & rsR![Apple name]
strFile = strDir & rsR![Apple name]

Open strFile For Output As #intF
Print #intF, "Apple1 = " & rsR!Apple1
Print #intF, "Apple2 = " & rsR!Apple2
Print #intF, "Apple3 = " & rsR!Apple3
Close intF

rsR.MoveNext
Loop

rsR.Close


End Sub

I would save the above, and then run the code (place cursor in the
code...hit f5, or simply from the deb ug window type in MyExport

The above code likey needs some tweaking...but, it should give you much the
idea...

 

[Andromeda031]

Hi Albert,

THANKS A LOT! But I have a few questions about this:

1. How/Where do I paste your code into the previous code I had using a
button? Can I even use a button or should I use the Export function under
File?
2. How/Where do I "call" the module.
3. Whenever I try to write the field names, VB changes them.
For example:
Print #intF, "Apple1 = " & rsR!Apple1 changes to
Print #intF, "Apple1 = " & rsR!Apple_1 Or vice versa.

Also, the result of my text file for 1 record will look like this:

apple1 = excellent
apple2 = good
apple3 = bad

ah, ok, so the above requirement does change things
note from my last post:

do you need multiple lines for *one* record? (if yes, then you likely can't

use transfertext).

In fact, there is no built in ability to export data as a single text file
with multiple lines of text.

So, we can't use transfer text here as that ONLY exports data from a record
in one line..and usually the data will be comma, or tab delimited.

So, we can't really use any of the built in export features.

What this means is that we have design, build and code a custom export
routine. This not too difficult..but, it will take extra code.

So, we create a standard code module. Call the module basExportCode

Now, inside of this module, we place some code:

The code will look much like:

Public Sub MyExport()

Dim strFile As String
Dim intF As Integer

Dim strDir As String
Dim rsR As DAO.Recordset

strDir = "C:\Temp\Metadata\"
intF = FreeFile()

Set rsR = CurrentDb.OpenRecordset("3D PDF Metadata")

Do Until rsR.EOF = True

Debug.Print "Process = " & rsR![Apple name]
strFile = strDir & rsR![Apple name]

Open strFile For Output As #intF
Print #intF, "Apple1 = " & rsR!Apple1
Print #intF, "Apple2 = " & rsR!Apple2
Print #intF, "Apple3 = " & rsR!Apple3
Close intF

rsR.MoveNext
Loop

rsR.Close


End Sub

I would save the above, and then run the code (place cursor in the
code...hit f5, or simply from the deb ug window type in MyExport

The above code likey needs some tweaking...but, it should give you much the
idea...