How to export the results of a table to multiple files with access VBA (1 Viewer)

[KapriRKarma]

Hello,

I have a table in access that contains multiple employee records. I created multiple files for each employee. I need to export each record into it's individual employee folder. Is there a VBA code for this?

 

[Ranman256]

make a list box in a form that uses a query of the data needed
a RUN button on the form to cycle thru a listbox exporting each record

sub btnSend_click()
Dim i As Integer
dim vItm, vDir, vFile

For i = 0 To lstBox.ListCount - 1
   vItm= lstBox.ItemData(i)     'get next item in list data
   lstBox = vItm                    'set listbox to the item

     'now get values from field columns
   vID = lstBox.column(0)       'in vb, columns start with zero
   vName = lstBox.column(1)   'get persons name from listbox ,col.2


      'do stuff with it here
   vDir = "c:\files\" & vName
   MakeDir vDir

     'combo box cboRpt has the name of the report to print
    vFile = vDir & "\" & cboRpt  & ".pdf"

      'output the report to pdf
    docmd.OutputTo acOutputReport , cboRpt ,acFormatPDF, vFile

      'or email if needed
   ' DoCmd.SendObject acSendReport, "rMyReport", acFormatPDF, vEmail, , , "Subject", "message"
Next
end sub

the report uses the LISTBOX key (in the report query) to only print that 1 persons data to the report.

Public Sub MakeDir(ByVal pvDir)
Dim fso
On Error GoTo errMake
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(pvDir) Then fso.CreateFolder pvDir     'MkDir pvDir
Set fso = Nothing
Exit Sub
errMake:
'MsgBox Err.Description & vbCrLf & pvDir, , "MakeDir(): " & Err
Set fso = Nothing
End Sub

 

[KapriRKarma]

make a list box in a form that uses a query of the data needed
a RUN button on the form to cycle thru a listbox exporting each record

sub btnSend_click()
Dim i As Integer
dim vItm, vDir, vFile

For i = 0 To lstBox.ListCount - 1
   vItm= lstBox.ItemData(i)     'get next item in list data
   lstBox = vItm                    'set listbox to the item

     'now get values from field columns
   vID = lstBox.column(0)       'in vb, columns start with zero
   vName = lstBox.column(1)   'get email from listbox ,col.2


      'do stuff with it here
   vDir = "c:\files\" & vName
   MakeDir vDir

     'combo box cboRpt has the name of the report to print
    vFile = vDir & "\" & cboRpt  & ".pdf"

      'output the report to pdf
    docmd.OutputTo acOutputReport , cboRpt ,acFormatPDF, vFile

      'or email if needed
   ' DoCmd.SendObject acSendReport, "rMyReport", acFormatPDF, vEmail, , , "Subject", "message"
Next
end sub

the report uses the LISTBOX key (in the report query) to only print that 1 persons data to the report.

Okay, thank you