Exporting query into multiple text files 1

[Jeremiah31]

I use this VB Code below to export a query as a text file into a local network directory folder. The code works great, but I was presented with a task that I don't know how or if it can be accomplished. In the query, a have a column called Ship-To that has multiple values (ie 130, 132, etc..) that represent stores numbers. My boss would like for me to export a seperate text file into the same directory folder for each seperate ship-to value. Can this be done? I really want to use VBA because if I have to create a sperate query for each store, I'm looking at 120 queries. Below I'm trying to figure out how to apporach this, but I don't know if I"m starting the code out correctly.

< Original Code >

Public Function Export_Tab_Delimited(qryAGRSalesProjection As String, FileNameAndPath As String)
'This uses the DAO database reference. IN the VB window (Ctrl+G) go to _
Tools --> References and select the Microsoft DAO 3.6 Library

Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim I As Integer
Dim FileNum As Integer
Dim FileNameAndPath As String
Dim OutputLine As String
FileNum = FreeFile()
FileNameAndPath = "F:\Supply Chain\Team Folders\Metrics\AGR Sales Projection\tblAGRSalesProjection.txt"
Set DB = CurrentDb() 'Use the current database
Set rs = DB.OpenRecordset("qryAGRSalesProjection") 'actually open the recordset
If rs.EOF = False Then
rs.MoveFirst
Else
MsgBox "No Data", vbExclamation, "Exiting Fuction"
Set rs = Nothing
Set DB = Nothing
Exit Function
End If
'Open the file for output
Open FileNameAndPath For Output Access Write Lock Write As FileNum
I = 0
OutputLine = ""
'Output the field names as a header
For I = 0 To rs.Fields.Count - 1
If I > 0 Then
OutputLine = OutputLine & Chr(9) & rs.Fields(I).Name
Else
OutputLine = rs.Fields(I).Name
End If
Next I
Print #FileNum, OutputLine
Debug.Print OutputLine
I = 0
OutputLine = ""
'start outputting the data
Do Until rs.EOF
For I = 0 To rs.Fields.Count - 1
If I > 0 Then
OutputLine = OutputLine & Chr(9) & rs.Fields(I).Value
Else
OutputLine = rs.Fields(I).Value
End If
Next I
Print #FileNum, OutputLine
Debug.Print OutputLine
OutputLine = ""
rs.MoveNext
Loop
Close #FileNum
Set rs = Nothing
Set DB = Nothing
End Function

< Modified Code >

Public Function MSAExport_Tab_Delimited(qryMSAAGRSalesProjection As String, FileNameAndPath As String)
'This uses the DAO database reference. IN the VB window (Ctrl+G) go to _
Tools --> References and select the Microsoft DAO 3.6 Library

Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim I As Integer
Dim FileNum As Integer
'Dim FileNameAndPath As String
Dim OutputLine As String
Dim rsShipTo As DAO.Recordset
Dim rs1 As DAO.Recordset

Set rsShipTo = CurrentDb.OpenRecordset("select distinct [ship-to] from tblAGRSalesProjectionUpdate")

FileNum = FreeFile()
'FileNameAndPath = "F:\Supply Chain\Team Folders\Metrics\AGR Sales Projection\tblAGRSalesProjection1.txt"
Set DB = CurrentDb() 'Use the current database
Set rs = DB.OpenRecordset("qryMSAAGRSalesProjection") 'actually open the recordset
If rs.EOF = False Then
rsShipTo.MoveFirst
Do Until rsShipTo.EOF
Set rs = CurrentDb.OpenRecordset("select * from tblAGRSalesProjectionUpdate where [ship-to]=" & rsShipTo![ship-to])
Else
MsgBox "No Data", vbExclamation, "Exiting Fuction"
Set rs = Nothing
Set DB = Nothing
Exit Function
End If
'Open the file for output
Open FileNameAndPath For Output Access Write Lock Write As FileNum
I = 0
OutputLine = ""
'Output the field names as a header
For I = 0 To rs.Fields.Count - 1
If I > 0 Then
OutputLine = OutputLine & Chr(9) & rs.Fields(I).Name
Else
OutputLine = rs.Fields(I).Name
End If
Next I
Print #FileNum, OutputLine
Debug.Print OutputLine
I = 0
OutputLine = ""
'start outputting the data
Do Until rs.EOF
For I = 0 To rs.Fields.Count - 1
If I > 0 Then
OutputLine = OutputLine & Chr(9) & rs.Fields(I).Value
Else
OutputLine = rs.Fields(I).Value
End If
Next I
Print #FileNum, OutputLine
Debug.Print OutputLine
OutputLine = ""
rsShipTo.MoveNext
Loop
Close #FileNum
Set rs = Nothing
Set DB = Nothing
End Function

 

[MichaelRed]

way much more than I can / will read through now.

I would suggest that you use two queries against the same record set:

first is just the unique set of "Ship To" entries.

second is a parameter query (w/ the parameter the list of "ship to" values from the first rs.

A simple loop to generate the 'exportable' rs substituting to ship to parameter for each.

Just export to what ever - be sure to change the export name for each.



MichaelRed

 

[SBelyea]

Jeremiah -

Just wanted to give a shout out - your code helped me solve a problem I had (with a few modications). Thanks!