Create .txt File from unbound Field

  • Thread starter Thread starter Justin
  • Start date Start date
Justin,
could you fill in the gaps for us volunteers please?
-details about the unbound control
-data type
-form or subform


Jeanette Cunningham -- Melbourne Victoria Australia
 
Justin
here is the basic idea.

Dim lngFileNum as long
Dim strExportFile as String 'name of txt file
Dim varData as Variant 'variable holds the contents of the control

'get file handle and open for output
lngFileNum = FreeFile()
Open strExportFile For Output As #lngFileNum

varData = me.txtUnboundControl

'write out the contents of the control
Print #lngFileNum, varData

Close #lngFileNum


Jeanette Cunningham -- Melbourne Victoria Australia
 
Jeanette,

I am trying to do something similar, except my output data will come from a
recordset, which is all set up and working fine, but it exports to a known
and exisitng filename; using your example, I want to be able to:

1. dynamically create the filename via a message box
2. have the filename export itself to a known path
3. place a suffix extension .kmz onto the filename that the user typed into
the messagebox.

how/where would I specify a filename and fixed path, such as:

D:\Data\Export\"filename.kmz"
 
I often times export a recordset to a text file. In my case, I
start at the top of the recordset (i.e., table) and write to a
text file as I access each record. I also handle overwriting an
existing file in a separate subroutine. Here's how I create the
text file once I've gotten through the filename part.

Assume the recordset's name is Table_xyz and the target filename
is D:\Data\Export\filename.kmz (your example)
Assume there are 3 fields in Table_xyz: F1, F2, and F3

Open "D:\Data\Export\filename.kmz" for Append As #1
Do While Not Table_xyz.EOF
Write #1, Table_xyz!F1, Table_xyz!F2, Table_xyz!F3
Loop
Close #1
Table_xyz.Close

This is the basic approach. If you want to add delimiters
between F1, F2, and F3 in filename.kmz (e.g., a Tab), then just
change the "Write #1" line to:

Write #1, Table_xyz!F1 & chr(9) & Table_xyz!F2 & chr(9) &
Table_xyz!F3

You will need to handle null fields and you will need to handle
cases when there are no records in Table_xyz.

I hope this helps.

Roger



message
Jeanette,

I am trying to do something similar, except my output data will
come from a
recordset, which is all set up and working fine, but it exports
to a known
and exisitng filename; using your example, I want to be able to:

1. dynamically create the filename via a message box
2. have the filename export itself to a known path
3. place a suffix extension .kmz onto the filename that the user
typed into
the messagebox.

how/where would I specify a filename and fixed path, such as:

D:\Data\Export\"filename.kmz"
 
In looking at what I sent (see below), I realized that it would
never work as shown. Here's the better version... same approach:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set cn = CurrentProject.Connection
rs.Open "Table_xyz", cn, adOpenKeyset, adLockOptimistic

Open "D:\Data\Export\filename.kmz" for Append As #1
Do While Not rs.EOF
Write #1, rs!F1, rs!F2, rs!F3
rs.movenext
Loop

Close #1
rs.Close
cn.close

This assumes that recordset is contained in the current project
along with the other assumptions stated below. There may be some
formatting that has to be done in the code above in cases where
F1, F2, or F3 are other than text fields.

Roger



I often times export a recordset to a text file. In my case, I
start at the top of the recordset (i.e., table) and write to a
text file as I access each record. I also handle overwriting an
existing file in a separate subroutine. Here's how I create the
text file once I've gotten through the filename part.

Assume the recordset's name is Table_xyz and the target filename
is D:\Data\Export\filename.kmz (your example)
Assume there are 3 fields in Table_xyz: F1, F2, and F3

Open "D:\Data\Export\filename.kmz" for Append As #1
Do While Not Table_xyz.EOF
Write #1, Table_xyz!F1, Table_xyz!F2, Table_xyz!F3
Loop
Close #1
Table_xyz.Close

This is the basic approach. If you want to add delimiters
between F1, F2, and F3 in filename.kmz (e.g., a Tab), then just
change the "Write #1" line to:

Write #1, Table_xyz!F1 & chr(9) & Table_xyz!F2 & chr(9) &
Table_xyz!F3

You will need to handle null fields and you will need to handle
cases when there are no records in Table_xyz.

I hope this helps.

Roger



message
Jeanette,

I am trying to do something similar, except my output data will
come from a
recordset, which is all set up and working fine, but it exports
to a known
and exisitng filename; using your example, I want to be able to:

1. dynamically create the filename via a message box
2. have the filename export itself to a known path
3. place a suffix extension .kmz onto the filename that the user
typed into
the messagebox.

how/where would I specify a filename and fixed path, such as:

D:\Data\Export\"filename.kmz"
 
Back
Top