Writing to position x, y in a text file

  • Thread starter Thread starter KC8DCN
  • Start date Start date
K

KC8DCN

Hi,

I have a table that has specific information that needs to be written to a
file. Each row is going to be it's own separate page, due to it being a load
file for another database.

What I need to do is write out the information at specific points in a text
file. For instance, I have columns name, address, and city. The information
needs to appear as:
Name Address City
with the exact spacing for each item. I can do everything else but the
spacing part. Is there an ideal way to get Name, Address and City to appear
at an exact with the exact spacing without calculating it every time?

Thanks!
 
On Fri, 18 Jul 2008 05:25:01 -0700, KC8DCN

That's called a fixed-width file, and you may be able to use the
regular export facility to create it. Step through the export wizard,
and use the Advanced options. It will allow you to specify the width
of each output field.

-Tom.
Microsoft Access MVP
 
Hi Tom,

I'm actually looking to do it programmatically because:
1. Each record must be in its own file
2. Each file must have a specific naming convention
3. This is going to be a constant ongoing process with a lot of data
4. It would be easier to have Access create the files and write out the
individual record information instead of me exporting an entire table and
then creating the individual files from the records. (I think...)

That's why I'm looking for a VB solution. :-)
 
I would define a string variable and then use the MId function to assign the
values, e.g. if Name always starts in column 1, Address in col 20, City in
col 40, then do:

Dim myString As String * 128 'or however long it needs to be

myString = name
mid(myString,20,len(address)) = address
mid(myString,40, len(city)) = city

Then write that string to the text file however you were going to do that
(I've never worked with text files in Access, other than importing them or
exporting to them).
 
On Fri, 18 Jul 2008 06:17:03 -0700, KC8DCN

You can do that with DoCmd.TransferText. Use an export spec like I
suggested in first instance, and for the TableName argument use a
query (I call it qryDataToBeExported below) that "looks back" on a
field on a form to pick up a primary key value:
select * from customers
where CustomerID = Forms!SomeForm!SomeField

Then your code could be something like:
dim rs as dao.recordset
set
rs=currentdb.openrecordset("qryCustomersToBeExported",dbOpenSnapshot)
while not rs.eof
Forms!SomeForm!SomeField = rs!CustomerID
DoCmd.TransferText acExportFixed, "SomeSpec", "qryDataToBeExported",
"c:\test" & rs!CustomerID & ".txt"
wend

-Tom.
 
Back
Top