Export to text file with header

[hugparty]

Howdy. I am new to this so need some help figuring out how to export a table via command button into a fixed width text file. It will need a header inserted as the first line.

The table has five fields, "record_type", "claim_number", "lname", "fname", "birthday", and "gender".

The header file needs to contain "1", "HSOUEY", a space, "DJDHWEI", a space; and also a "batchnum" and "transactioncount" that willchange each time.

The file name will need to spit out as FilenameDateTimeBatchnum.txt

I have tried every effin way I can think of or find online to do this. Can anyone point me in some direction that makes sense? This is the last thing on this project and I want to finish it.

Thanks!
Alice

 

[DCrake]

Now then Alice, don't get frustrated, this is a doable task and just needs a little forethought.

First of all you need to create the name of the text file so access knows where to write the data to.

You say it comprises of 4 entities

1 Filename
2 Date
3 time
4 Batch no

So first we Dim a variable called StrFileName

Dim StrFileName As String

StrFileName = "Fred" & Format(Now(),"YYYYMMDDHNN") & "01" & ".Txt"

So StrfileName = "Fred20090317134501.Txt"

Next we need to tell access where it lives

StrPath = "C:\Temp"

Now we can open it

Open StrPath & "\" StrFileName For Output As #1

Next we inser the header details



Print #1, "1,HSOUEY DJDHWEI " & BatchNum & " " & TransactionCount

Next we can enter the row headings

Print #1, "record_type_______", "claim_number________", "lname___________", "fname_______", "birthday__", "gender"

(I have used underscores to simulate fixed with spacing)

Finally we can add the data to the file.

We do this by using a recordset from the underlying table ort query. In this case I am using a query that is sorted in the right order

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("TestQuery")

Do Until Rs.EOF
    Print #1, Rs("Record_Type")  & "," & Rs("claim_Number")  & "," & Rs("LName")  & "," & Rs("FName") & "," & Etc

    Rs.MoveNext
Loop
Close #1
Rs.Close
Set Rs = Nothing

What I have not done is to pad out each field with the correct number of trailing spaces to ensure that the fields are fixed width spaced. If you do not know how to do this then let me know.

When you open your test file I suggest you change the font to Courier which is an equispaced font. so you will be able to ensure that the date is spaced accordingly.

Having said all that I have just remember you can go through the export dta wizard and set up an eport specification using you table and settings.

Anyway you have both methods available to you.

David

 

[hugparty]

Thank you David!

I do get an error - it says "User Defined Type not defined" and highlights "Rs As DAO.Recordset"

Am I missing something?

 

[hugparty]

Ok I fixed that - I had to enable the dao thing in "References" in the tools. Now I just get bad file name or number.

 

[hugparty]

YAY! I made it work.

I changed

StrPath = "C:\Temp"
Open StrPath & "\" StrFileName For Output As #1

to

StrPath = "C:\Temp\"
Open StrPath & StrFileName For Output As #1


And it worked perfectly!!! THANK YOU DAVID! (insert offers of marriage to my hero here)

Alice