Parsing Reports for Export into folders

[aholts]

I have a database that I have built where I have over 300 records and would like to automate the exporting of the data in the report format. I currently have it where it exports 1 file with the 300 records separated in that 1 file however I would like it to take parse out the records so that there are 300 files. This is the first challenge (or maybe not). The second thing that I would like to do is put these records in a specified location on a shared drive in folders generated by the, or a macro perhaps using the name of one of the field values like Fname or something like that. The reason for this is because not only do I need to parse records but I would like the files attached to that record be parsed to the correct folder location. So I will probably need that routine to dynamically recognize if there is a folder with a specified naming convention not to create a new one if its found and if its not found to put the contents i.e report and attachments (not necessarily have to be in the same routine or same time..) to that folder.</SPAN>

Below is the code that I have so far in regards to exporting the reports and exporting the attachments.</SPAN>

' Need to parse out individual files to individual folders</SPAN>
Sub ExportReportTest()</SPAN>
Dim db As DAO.Database</SPAN>
Dim rs As DAO.Recordset</SPAN>
Dim MyFileName As String</SPAN>
Dim mypath As String</SPAN>
Dim temp As String</SPAN>

mypath = "C:\Users\Documents\Sandbox\Reports"</SPAN>

Set db = CurrentDb()</SPAN>
Set rs = db.OpenRecordset("Tbl_MainRRD_Data", dbOpenTable) 'I chose to export directly from a table but perhaps I will need to export from a Qry?</SPAN>

' I tried to loop through but what I got was 300+ files of the 300+ records in each file. (not what I want)</SPAN>
'Do While Not rs.EOF</SPAN>
temp = rs("POMID")</SPAN>
MyFileName = rs("POMID") & ".PDF"</SPAN>

DoCmd.OpenReport "Copy Of Rpt_ViewMod_Reports", acViewReport</SPAN>
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName</SPAN>
DoCmd.Close acReport, "Copy Of Rpt_ViewMod_Reports"</SPAN>
'rs.MoveNext</SPAN>
Loop</SPAN>

Set rs = Nothing</SPAN>
Set db = Nothing</SPAN>
End Sub

'We got the files to export individually now you need to figure out if it can go to a specific folder.
Sub ExportAttachmentTest()

'Public Function SaveAttachmentsTest(strPath As String, Optional strPattern As String = "*.*") As Long
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rsA As DAO.Recordset2
Dim rsB As String
Dim fld As DAO.Field2
Dim OrdID As DAO.Field2
Dim strFullPath As String
Dim strPath As String
Dim strPattern As String

'Get the database, recordset, and attachment field
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Tbl_MainRRD_Data")
Set fld = rst("Attachments")
'Set OrdID = rst("OrderID")
strPath = "C:\Users\Documents\Sandbox\Reports" 
strPattern = "*.*"

'Navigate through the table
Do While Not rst.EOF

'Get the recordset for the Attachments field
Set rsA = fld.Value
'rsB = OrdID.Value

'Save all attachments in the field
Do While Not rsA.EOF
If rsA("FileName") Like strPattern Then

'To Export the data, use the line below
strFullPath = strPath & "\" & rsA("FileName")

'Make sure the file does not exist and save
If Dir(strFullPath) = "" Then
rsA("FileData").SaveToFile strFullPath
End If

'Increment the number of files saved
SaveAttachmentsTest = SaveAttachmentsTest + 1
End If

'Next attachment
rsA.MoveNext
Loop
rsA.Close

'Next record
rst.MoveNext
Loop
rst.Close
dbs.Close
Set fld = Nothing
Set rsA = Nothing
Set rst = Nothing
Set dbs = Nothing
End Sub

</SPAN>
I am open to alternate solutions as I am not married to this code however I have found that this code is partially what I need it to do. Thank you in advance for your help!

 

[ranman256]

Your output report query must be set to pull 300 records. Your output query needs output the 1 record. (I dont know what you're criteria is tho..break per Client, or Name..?)
The loop will be ALL records, but the output query will be 1 record.