กระทู้เก่าบอร์ด อ.สุภาพ ไชยา
241 2
URL.หัวข้อ /
URL
Automate Exporting
ผมเพิ่งไปอ่านคำถามที่
http://board.quicktechusa.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001193
เขาถามว่า
Hi. Can you export to separate .txt files based on criteria? For instance, in my table called tblRecords,
can I export to different .txt files based on the value of the field CountyCode? If CountyCode = 1,
export to text file Dade01.txt? I need to do that for 67 counties and I'm not sure if I can loop that,
or what I should do? I am not sure what would be the most efficient way. Thanks!
ผมเลยตอบเขาไปว่า
I am not sure if this the most efficient way. But you can try the code below.
Sub Xport2TextFile2()
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef, I As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select Distinct CountryCode From Table1")
Set qdf = dbs.QueryDefs("Query1")
' Query1's SQL = "SELECT * FROM Table1 WHERE CountryCode=1;"
If Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
For I = 1 To rst.RecordCount
qdf.SQL = "Select * From Table1 Where CountryCode=" & rst(0)
DoCmd.OutputTo acOutputQuery, "Query1", acFormatTXT, "I:/Dade" & Format(rst(0), "00") & ".txt"
rst.MoveNext
Next I
End If
rst.Close
qdf.Close
dbs.Close
End Sub
ตัวอย่างอยู่ที่
http://agserver.kku.ac.th/basiceng/export2textfile.zip ครับ
http://board.quicktechusa.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001193
เขาถามว่า
Hi. Can you export to separate .txt files based on criteria? For instance, in my table called tblRecords,
can I export to different .txt files based on the value of the field CountyCode? If CountyCode = 1,
export to text file Dade01.txt? I need to do that for 67 counties and I'm not sure if I can loop that,
or what I should do? I am not sure what would be the most efficient way. Thanks!
ผมเลยตอบเขาไปว่า
I am not sure if this the most efficient way. But you can try the code below.
Sub Xport2TextFile2()
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef, I As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select Distinct CountryCode From Table1")
Set qdf = dbs.QueryDefs("Query1")
' Query1's SQL = "SELECT * FROM Table1 WHERE CountryCode=1;"
If Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
For I = 1 To rst.RecordCount
qdf.SQL = "Select * From Table1 Where CountryCode=" & rst(0)
DoCmd.OutputTo acOutputQuery, "Query1", acFormatTXT, "I:/Dade" & Format(rst(0), "00") & ".txt"
rst.MoveNext
Next I
End If
rst.Close
qdf.Close
dbs.Close
End Sub
ตัวอย่างอยู่ที่
http://agserver.kku.ac.th/basiceng/export2textfile.zip ครับ
2 Reply in this Topic. Dispaly 1 pages and you are on page number 1
1 @R00353
อ.สุภาพ ครับ
ผม D/L File ของ อ.สุภาพ มาแล้วครับ
รบกวน อ.สุภาพ แนะนำวิธีการใช้ Funtion ด้วยครับ
หรือถ้าผมสร้าง Command Bottom ที่ Form
ผมจะใช้ Funtion อย่างไรครับ รบกวน อ.สุภาพ อีกครั้งครับ
ขอบพระคุณมากๆ ครับ
Suchat
2 @R00354
ก็ตัดเอาส่วน Dim dbs As ... จนถึง dbs.Close ไปใช้กับ Command Button ได้เลย ก็จะได้
Private Sub Command1_Click()
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef, I As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select Distinct CountryCode From Table1")
Set qdf = dbs.QueryDefs("Query1")
' Query1's SQL = "SELECT * FROM Table1 WHERE CountryCode=1;"
If Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
For I = 1 To rst.RecordCount
qdf.SQL = "Select * From Table1 Where CountryCode=" & rst(0)
DoCmd.OutputTo acOutputQuery, "Query1", acFormatTXT, "I:/Dade" & Format(rst(0), "00") & ".txt"
rst.MoveNext
Next I
End If
rst.Close
qdf.Close
dbs.Close
End Sub
Time: 0.0464s
