Custom Text File (2000)

[Lstclair55]

<big>Edited by HansV to break ridiculously long line. Please show SOME consideration for others!</big>

Hi,

Working closely with my other post about renaming and moving of image files, I need to achieve the following.

I have a query that exports the required information to a txt file in the right format. This is fine so far. However it needs to be more complex than a standard exported text file.

The file needs to be be split into the following:

#HEADER#
Version : 3 (fixed value)
EOF : '^' (fixed value)
EOR : '~'
Property Count : 'x' (total amount of records contained with the the query)
Generated Date : dd-mmm-yyyy hh:mm (Date stamp the file is generated)

#DEFINITION#
agent_ref^Address_1^Address_2^Address_3^Address 4^Town
^Postcode1^Postcode2^Feature1^Feature2^Feature3^Fe ature4^Feature5
^Feature6^Feature7^Feature8^Feature9^Feature10^Sum mary^Description
^Branch_ID^STATUS_ID^BEDROOMS^PRICE^Price_Qualifie r^Prop_Sub_ID
^Create_Date^Update_Date^Display_Address^Published _Flag
^Let_Date_Available^Let_Bond^Let_Type_ID^Let_Furn_ ID
^Let_Rent_Frequency^tenure_type_id^Trans_Type_ID^N ew_home_flag
^Media_Image_<00>^Media_Image_01^Media_Image_02^Me dia_Image_03
^Media_Image_04^Media_Image_05^Media_Image_06^Medi a_Image_07
^Media_Image_08^Media_Image_09^media_image_60
^media_image_text_60^media_image_61^media_image_te xt_61
^media_document_01^media_document_50^media_documen t_text_50^~

#DATA#
Result of the query are placed here and if possible a line space between each record

#END#

The file needs to be a text file, but with the extension of BLT / BLM

Is there a way of compiling this with vba?

[HansV]

You could open a text file for output, write the fixed lines to it (using the Print # instruction), then open a DAO recordset on the query, loop through the records and write a line for each record. Finally close the recordset, write the final lines to the text file and close the text file.

[Lstclair55]

Okay, are there any pointers on this?

[HansV]

See:
Open Statement
Print # Statement
Close Statement

[Lstclair55]

Thanks so far.

Just stuck on being able to open a DAO Recordset and looping through the query line by line. any hints

[HansV]

See the code that I wrote for you yesterday, in the attachment to <post:=708,638>post 708,638</post:>. There are many other examples in this forum, search for openrecordset movenext.

[Lstclair55]

Think I have come up with another solution.

I can export the query to a text file first of all and in the relevant section of the print line of creating the original text file I could merge the contents of the query exported text file.

How do you merge the contents of one text file into the relevant section of a Print line?

[HansV]

How often will you have to do this? If you're not up to writing the code, it might be easier to copy and paste the contents of the exported query into the text file.

[Lstclair55]

Managed to sort it out now