How To Write A Recordset To File With VBA

In this video you’ll find out how to write a
recordset to a file with VBA using the Microsoft
Scripting Object.

You’ll find out how to take your
table data, and export it to a csv
formatted file so you can share it
with other applications.

Use this method to get your data
out of the normal data and use it
or upload it somewhere else.

As you may or may not know, the CSV
format is a recognizable format by nearly
every application, Apple, Linux or Windows.

I use the csv format in ASP and PHP web applications
so the viewer can export the contents to
a spreadsheet format on their computer.


'*********************************
'LoeblCom Services
'713-409-7041
'http://loeblcomservices.com
'https://vbahowto.com
'*********************************

Option Compare Database

Private Sub btnWrite_Click()
    Dim strSQL As String
    Dim rst As Recordset
    Dim strLines As String
    Dim fsoFile As Object
    Dim strPath As String
    
    
    Set fsoFile = CreateObject("Scripting.FileSystemObject")
    Dim objFile As Object

    strSQL = "SELECT * FROM tblProject"
    
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    Do While Not rst.EOF
        strLines = strLines & rst.Fields("proj_Wage") & ","
        strLines = strLines & rst.Fields("proj_TotalMaterial") & ","
        strLines = strLines & rst.Fields("proj_UsedCost") & ", "
        strLines = strLines & rst.Fields("proj_Cost") & ", "
        strLines = strLines & rst.Fields("proj_SupplierCost") & ", "
        strLines = strLines & rst.Fields("proj_Unit") & ", "
        strLines = strLines & rst.Fields("proj_SupplierZip") & ", "
        strLines = strLines & rst.Fields("proj_ProjectType") & ", "
        strLines = strLines & rst.Fields("proj_Hours") & vbCrLf
        
        
        rst.MoveNext
    Loop
    
    strPath = CurrentProject.Path & "\project_data.csv"
    
    Set objFile = fsoFile.CreateTextFile(strPath)
    
    objFile.WriteLine strLines
    objFile.Close
    
    Set fsoFile = Nothing
    Set objFile = Nothing
    
    
    Set rst = Nothing
    
    MsgBox "Done!"

End Sub



Click here for the file:
write-recordset-to-file-with-vba.mdb

applications.


Comments are closed.