Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Sydney, Australia
    Posts
    12

    Question Unanswered: Modifications to Table Export

    Hi All,

    Hoping some VBa experts out there could help me develop the code to perform the following function.

    Have a table in Access with the following information:

    Invoice Line
    -------- -----
    1 1
    1 2
    1 3
    1 4
    2 1
    2 2
    3 1
    4 1
    4 2
    4 3
    4 4


    Would like the above table to export in a tab delimited file with the following format:

    Invoice Line
    -------- -----
    1 1
    1 2
    1 3
    1 4

    2 1
    2 2

    3 1

    4 1
    4 2
    4 3
    4 4

    I have no problem automating a complete export of the file without any modifications, or even using excel VBA to perform the required function, but would really like to do it in Access. Any ideas anyone?

    All suggestions appreciated.

    Rael

  2. #2
    Join Date
    Apr 2002
    Posts
    139
    Hi Real,

    Think you could loop through the table using a record set, and write down each individual record in a text file.

    I haven't worked out the record set loop for you, but could give you an example of code that will output text and tabs to a file:

    Private Sub ButtonTest_Click()

    Dim MyIndex, FileNumber

    FileNumber = FreeFile ' Get unused file number.
    Open "C:\TestFile.txt" For Output As #FileNumber ' Create filename.
    For MyIndex = 1 To 5 ' Loop 5 times, or as many as you need.
    Write #FileNumber, "InvoiceNr" & vbTab & "LineNr" ' Output text.
    'You should check here for a new invoice number, and add an empty
    line using vbCrLf.
    Next MyIndex

    Close #FileNumber ' Close file.

    End Sub

    Run this code, check C:\testfile.txt, and play with it!

    hth

  3. #3
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81
    You can use a TEMPLATE to make it output the way you want. Make a report and set it up the way you want your outputted fiel to look. Then save it. Then make your code/macro look at that template when you export and it will expot the way you want it to.

Posting Permissions

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