Untitled

 avatar
unknown
plain_text
5 months ago
1.8 kB
3
Indexable
Sub ExportToCSVWithCustomHeaders()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim csvFilePath As String
    Dim csvFile As Object
    Dim fso As Object
    Dim headers As String
    Dim recordData As String

    ' Set the output file path
    csvFilePath = "C:\Path\To\Your\File.csv" ' Change this to your desired file path
    
    ' Open the database and query
    Set db = CurrentDb
    Set rs = db.OpenRecordset("YourQueryName") ' Replace with your query name
    
    ' Create a FileSystemObject to write to the CSV file
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set csvFile = fso.CreateTextFile(csvFilePath, True)
    
    ' Write the custom headers
    headers = "PO Number,Order Number,MAMF Quote Number,Estimated Ship Date,Is Shipped?,Actual Ship Date,Paint Code,Current Status"
    csvFile.WriteLine headers
    
    ' Write the data, mapping query fields to custom header order
    Do While Not rs.EOF
        recordData = _
            Nz(rs("YourQueryFieldForPONumber"), "") & "," & _
            Nz(rs("YourQueryFieldForOrderNumber"), "") & "," & _
            Nz(rs("YourQueryFieldForMAMFQuoteNumber"), "") & "," & _
            Nz(rs("YourQueryFieldForEstimatedShipDate"), "") & "," & _
            Nz(rs("YourQueryFieldForIsShipped"), "") & "," & _
            Nz(rs("YourQueryFieldForActualShipDate"), "") & "," & _
            Nz(rs("YourQueryFieldForPaintCode"), "") & "," & _
            Nz(rs("YourQueryFieldForCurrentStatus"), "")
        csvFile.WriteLine recordData
        rs.MoveNext
    Loop
    
    ' Close everything
    csvFile.Close
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set csvFile = Nothing
    Set fso = Nothing
    
    MsgBox "CSV Export Completed!", vbInformation
End Sub
Editor is loading...
Leave a Comment