ManAlz
asked on
Hor to Write Text Files in Access using VBA?
Have you ever tried writing to a text file in Access? I'm using the Write statement in VBA to write to a text file. I can't use Transfertext because the file contains a header, details and a footer.
However, the Write statement forces the output to be according to the data type (ie, a date field will output as #2001-07-08# while a string field will write as "Hello Word") Do you know a way to force Access to write data without the # and the "? Thanks.
However, the Write statement forces the output to be according to the data type (ie, a date field will output as #2001-07-08# while a string field will write as "Hello Word") Do you know a way to force Access to write data without the # and the "? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use tranfer text
DoCmd.TransferText acExportFixed, "YourSpecification", _
"tableName", strPath & "filename.txt", False, ""
The False allows export without headers. This is a fixed field export. I have done it once with the wizard to set up a Specification. I have my path as a string variable but you could easy hard code it into this statement. See VBA help on DoCmd.TransferText for a fuller explanation of your options.
If you are having trouble with the syntax, try setting up a macro (and then converting to code if you want).
Hope this helps,
Jack
DoCmd.TransferText acExportFixed, "YourSpecification", _
"tableName", strPath & "filename.txt", False, ""
The False allows export without headers. This is a fixed field export. I have done it once with the wizard to set up a Specification. I have my path as a string variable but you could easy hard code it into this statement. See VBA help on DoCmd.TransferText for a fuller explanation of your options.
If you are having trouble with the syntax, try setting up a macro (and then converting to code if you want).
Hope this helps,
Jack
ASKER
It worked! I didn't use Print before because the help file said it wrote date values in the short date format which was not what I wanted. To correct that, I used the format function to change it to string. Thanks for the tip.
The code I used was:
Print #1, "D,CDI,"; ![Cat]; ","; ![ProductID]; ","; Format(ord!ReturnDate, "yyyy-mm-dd")
It now outputs to the text file wonderfully as:
D,CDI,10,10519001,2001-07-
The code I used was:
Print #1, "D,CDI,"; ![Cat]; ","; ![ProductID]; ","; Format(ord!ReturnDate, "yyyy-mm-dd")
It now outputs to the text file wonderfully as:
D,CDI,10,10519001,2001-07-
Try using the Print statement instead of Write.