Would like the above table to export in a tab delimited file with the following format:
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?
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.
Close #FileNumber ' Close file.
Run this code, check C:\testfile.txt, and play with it!
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.