indyng
asked on
Code to export to a text file from access
Hi Experts,
I want to write to a text file either one line at a time or, all at once from a table using code.
How can this be done?
Thanks
I want to write to a text file either one line at a time or, all at once from a table using code.
How can this be done?
Thanks
ASKER
That included the headers in the export, I am using the export file to populate fields in a WMS and the header is causing problems. I also get the first column which I don't want.
Is there a way to limit what gets exported?
Is there a way to limit what gets exported?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ohh.. first row.. yes, you can use file IO
exaple
Add a refrence to Microsft DAO Obj. Library 3.?? - in VB window select Tools Refrences and tick it from the list
Function ExportMyFile()
Dim rs as dao.recordset
Set rs = db.openrecordset("tblMyTab leName") 'open recordset of table
open "c:\myText.txt" for output as #1 'open text file for output of results
If rs.recordcount = 0 then ' no records found exit
exit function
end if
'make sure at first
rs.movefirst
'goto second record
rs.movenext
while not rs.eof
Print #1, rs(0) & ", " & rs(1) & ", " rs(2) '... copy for each field for exaple if tblMyTable has 5 fields use rs(0) ... rs(4)
rs.movenext
wend
Close #1
rs.close
set rs = nothing
End function
Hope this is ok, did it without vb.
Dave
exaple
Add a refrence to Microsft DAO Obj. Library 3.?? - in VB window select Tools Refrences and tick it from the list
Function ExportMyFile()
Dim rs as dao.recordset
Set rs = db.openrecordset("tblMyTab
open "c:\myText.txt" for output as #1 'open text file for output of results
If rs.recordcount = 0 then ' no records found exit
exit function
end if
'make sure at first
rs.movefirst
'goto second record
rs.movenext
while not rs.eof
Print #1, rs(0) & ", " & rs(1) & ", " rs(2) '... copy for each field for exaple if tblMyTable has 5 fields use rs(0) ... rs(4)
rs.movenext
wend
Close #1
rs.close
set rs = nothing
End function
Hope this is ok, did it without vb.
Dave
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm using this solution
DoCmd.TransferText acExportDelim, "", "Print", "c:\import.txt", 0, ""
where Print is a query to avoid the problem of extra columns.
Simple seems better as everything else gave me various problems.
SAP our WMS default imports from c:\import.dat
Can I export a .dat file, or change the .txt to a .dat automatically after exporting. if I get a yes or no answer I'll post another question on how to do it so that points can be awarded to the initial question and this one.
Thank you all
DoCmd.TransferText acExportDelim, "", "Print", "c:\import.txt", 0, ""
where Print is a query to avoid the problem of extra columns.
Simple seems better as everything else gave me various problems.
SAP our WMS default imports from c:\import.dat
Can I export a .dat file, or change the .txt to a .dat automatically after exporting. if I get a yes or no answer I'll post another question on how to do it so that points can be awarded to the initial question and this one.
Thank you all
use
DoCmd.TransferText acExportDelim, "", "Print", "c:\import.txt", 0, ""
'wait for it
Doevents
Rename "c:\import.txt", "c:\import.dat"
Dave
DoCmd.TransferText acExportDelim, "", "Print", "c:\import.txt", 0, ""
'wait for it
Doevents
Rename "c:\import.txt", "c:\import.dat"
Dave
https://www.experts-exchange.com/questions/21088090/Automatic-export-of-table-to-txt-file.html
Dave