Export form data to text file

[Jeff Hunt]

Is there a way to export data from form fields or from code directly into a
text file? I’ve looked at the DoCmd.TransferText acExportDelim option, but
that appears to need the data to be in a table, which I’d rather avoid. I am
trying to feed commands to one of our programs, and they have to be done as a
comma-delimited text file. I don’t want to use a table, because I want to
change the number and order of the fields on the fly, which from what I
gather would require a slew of tables for the number of options I need. Is
it possible to export a string variable directly to a text file? I think
that would work best for me.

 

[Nicholas Scarpinato]

Why not just make a table that encompasses all the possible fields you would
need and export the table as a CSV? As far as answering your other question,
it's theoretically possible to do so, but it would require a lot of code to
get it to work right, assuming you have multiple different exports for each
option.

Maybe if I had an example of the import required for the program, I could be
of more assistance.

 

[Ron2006]

You can write directly to a text file. Here is an example. I perform
this routine on button click.


Open hldFileName For Output As #1
Print #1, "KAPS Loaded for DRT: " & Me.DRTNO
Print #1, "KAPS Loaded for TDD: " & Me.TDDNumber
Print #1, "KAPS Loaded By.....: " & Forms![hiddenkey]!
[HKLoginName]
Print #1, "KAPS Loaded On.....: " & Now()
Print #1, " "
Print #1, "Circuit Edit Log File
Contents..........................: "
Print #1,
"........................................................."
Print #1, "Status..: " & ![Status]
Print #1, "System..: " & ![System]
Print #1, "Message.: " & ![TestDesc]
Close #1

Multiple fields can go on the same record by including a comma and
fieldname/value for each additional field.
To have fields enclosed in quotes on the output you need to generate
the quotes as part of the output.

Ron

 

[Jeff Hunt]

I appreciate the response, but this would not work in my case. My output
needs to change the order and number of fields, and may need to do so on each
line in the file. Line 1 of the file may output as NAME(data1,data2,data3)
but Line 2 would need to output CASH(data1,,,,data4,,data5,data2). From how
I see it, outputting a table to a CSV would result in a lot of post-export
editing of the file.

Thanks for the comment though.

 

[Nicholas Scarpinato]

I see what you mean. Is the format for each exported command the same every
time, i.e., do you have "commands" that are fed into the program using the
same lines and structure every time they're preformed, using different data
each time, or is each command fluid as far as the import file is concerned?
It sounds to me like you need specific export definitions for each "command",
like NAME, CASH, etc., that your underlying code could use to determine what
export needs to be used, and then act accordingly. For example (air code):

Dim ExportString as String, i as Integer, CommandsCount as Integer
Open hldFileName For Output As #1
{Get the count of how many lines will be required here}
For i = 1 to CommandsCount
If Me!CommandName = NAME Then
ExportString = "NAME( '" & Me!data1 & "', '" & Me!data2 & "', '" & Me!data3
& "')
Elseid Me!CommandName = "CASH" Then
ExportString = "CASH('" & Me!data1 & "',,,, '" & Me!data4 & "',, '" & data5
& "', '" & data2 & "')
Elseif...
.... (Add more ExportString lines here)
....
Print #1, ExportString
Next i
Close #1

How are you tellinmg the database what command is being performed? Is it
being entered into the form somewhere or are you entering lines on the fly?

 

[Jeff Hunt]

Took me a bit of playing to figure out how this worked, but I think it is
what I was looking for. Thanks for the tip!

 

[Jeff Hunt]

This is pretty close to the way I think I will do it. Ron's code from below
got me going on this path, but you have it more the way I will actually
implement.

Since you asked, the data is being entered on a form, and most will come
from a single table. The table has quite a few columns, some used on each
command string but no command has all of them, and the order changes. Since
more than one command can exist in a single file, I will probably have a
series of checkboxes to determine which commands need to be generated, and
then have a sub for each export string, with a final write or append
statement for putting all the commands into the file.

Thanks for following up on this.

 

[Nicholas Scarpinato]

The checkbox idea sounds like the right way to go. Your code could check each
checkbox and build the export strings accordingly using an array, something
like this:

Dim ExportString() as String, i as Integer, CommandsCount as Integer
If Me!Checkbox1 = Yes Then CommandsCount= CommandsCount + 1
If Me!Checkbox2 = Yes Then CommandsCount= CommandsCount + 1
If Me!Checkbox3 = Yes Then CommandsCount= CommandsCount + 1
.... ' etc. for each checkbox, to build the count for the lines in the file
Redim ExportString(CommandsCount)
For i = 1 to CommandsCount
If Me!Checkbox1 = Yes Then
ExportString(i) = "NAME( '" & Me!data1 & "', '" & Me!data2 & "', '" &
Me!data3
& "')
Me!Checkbox1 = No
Goto NextCommand
End If
If Me!Checkbox2 = Yes Then
ExportString(i) = "CASH('" & Me!data1 & "',,,, '" & Me!data4 & "',, '" &
data5
& "', '" & data2 & "')
Me!Checkbox2 = No
Goto NextCommand
End If
.... (Add more ExportString(i) lines here)
....
NextCommand:
Next i
Open hldFileName For Output As #1
For i = 1 to CommandsCount
Print #1, ExportString(i)
Next i
Close #1

This will cycle through all the checkboxes and grab each command in an array
(ExportString(i)), which will allow you to do the loop of commands at the end
easily.