Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Text File (2000)

    <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?
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Text File (2000)

    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.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Text File (2000)

    Okay, are there any pointers on this?
    Best Regards,

    Luke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Text File (2000)


  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Text File (2000)

    Thanks so far.

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

    Luke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Text File (2000)

    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.

  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Text File (2000)

    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?
    Best Regards,

    Luke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Text File (2000)

    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.

  9. #9
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Text File (2000)

    Managed to sort it out now
    Best Regards,

    Luke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •