Export multiple txt files from one table based on unique values in one field

[dean.brunne]

Hi,

I have a table with a Product field and need to export a txt for each
Product. I figure that I need to do the following:

Create select query
Create a loop through the unique values in Product field populated in
an array
Within loop set criteria of Select Query to array
Within loop use DoCmd.TransferText method to export query.

However I am a liitle confused with:

How to create the array (dynamic as values may change period to
period)
How to use the array value in the criteria field of the select query
How to use the Select query results in the TransferText method.

Any help is greatly appreciated.

Cheers,

Dean

 

[Allen Browne]

Dean, it might be easiest to do this by opening a recordset that gives you
the list of products to export, and then exporting a file for each one.

Create a query that the code can reuse for the export. (It doesn't matter
what's in to start with, as that will get overwritten.) Replace "Query1"
below with the name of your saved query.

Next you need a SQL statement for this query. This will be split into 2 at
the point where you need to insert the product number. strcStub is the first
part, and strcTail is the remainder of the SQL statement.

The code is untested, but the idea it so get a list of unique product codes,
and repeat the TransferText for each product, so as to generate a text file
for each one.

Function ExportProducts() As Long
'Purpose: Export a file for each product.
Dim db As DAO.Database 'This database
Dim rsProduct As DAO.Recordset 'Each product
Dim strSql As String 'SQL statement
Dim strFile As String 'Export file name
Dim lngCount As Long 'Number of products
Const strcPath = "C:\MyFolder\" 'Path to export to.
Const strcQuery4Export = "Query1" 'Name of query
Const strcStub = "SELECT Field1, Field2, ... " & _
"FROM Table1 WHERE (ProductID = "
Const strcTail = ") ORDER BY ProductID;"

'Initialize
Set db = CurrentDb()
strSql = "SELECT DISTINCT ProductID FROM Table1 " & _
"WHERE ProductID Is Not Null;"
Set rsProduct = db.OpenRecordset(strSql)

'Loop through distinct products, exporting each.
Do While Not rsProduct.EOF
strSql = strcStub & rsProduct!ProductID & strcTail
db.QueryDefs(strcQuery4Export).SQL = strSql
strFile = strcPath & rsProduct!ProductID & ".txt"
DoCmd.TransferText acExportDelim, , strcQuery4Export, strFile
lngCount = lngCount + 1
rsProduct.MoveNext
Loop

'Clean up
rsProduct.Close
Set rsProduct = Nothing
Set db = Nothing

'Return count of products exported.
ExportProducts = lngCount
End Function

 

[dean.brunne]

Dean, it might be easiest to do this by opening a recordset that gives you
the list of products to export, and then exporting a file for each one.

Create a query that the code can reuse for the export. (It doesn't matter
what's in to start with, as that will get overwritten.) Replace "Query1"
below with the name of your saved query.

Next you need a SQL statement for this query. This will be split into 2 at
the point where you need to insert the product number. strcStub is the first
part, and strcTail is the remainder of the SQL statement.

The code is untested, but the idea it so get a list of unique product codes,
and repeat the TransferText for each product, so as to generate a text file
for each one.

Function ExportProducts() As Long
'Purpose: Export a file for each product.
Dim db As DAO.Database 'This database
Dim rsProduct As DAO.Recordset 'Each product
Dim strSql As String 'SQL statement
Dim strFile As String 'Export file name
Dim lngCount As Long 'Number of products
Const strcPath = "C:\MyFolder\" 'Path to export to.
Const strcQuery4Export = "Query1" 'Name of query
Const strcStub = "SELECT Field1, Field2, ... " & _
"FROM Table1 WHERE (ProductID = "
Const strcTail = ") ORDER BY ProductID;"

'Initialize
Set db = CurrentDb()
strSql = "SELECT DISTINCT ProductID FROM Table1 " & _
"WHERE ProductID Is Not Null;"
Set rsProduct = db.OpenRecordset(strSql)

'Loop through distinct products, exporting each.
Do While Not rsProduct.EOF
strSql = strcStub & rsProduct!ProductID & strcTail
db.QueryDefs(strcQuery4Export).SQL = strSql
strFile = strcPath & rsProduct!ProductID & ".txt"
DoCmd.TransferText acExportDelim, , strcQuery4Export, strFile
lngCount = lngCount + 1
rsProduct.MoveNext
Loop

'Clean up
rsProduct.Close
Set rsProduct = Nothing
Set db = Nothing

'Return count of products exported.
ExportProducts = lngCount
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.












- Show quoted text -

Thanks Allen! Works like a charm and the structure of declaring the
constants have given me ideas for how to structure other code I have.
It is certainly easier to see the syntax when declaring the constants!

Thanks!