Save .xlsx to .csv or .txt from Access VBA

Question

I've successfully used Access VBA to export a query to .xlsx, and I have used VBA to open the .xlsx file, but now I need to do "save as" to convert the file to a .csv or, if possible, .txt. This is part of a large automated process with thousands of files, so I really can't have any manual steps. I need the process from query to .txt to be totally automated within Access VBA. Here is my current code, which successfully opens the file I've created:

Sub Export_Reduced_Inforce()

Dim Dest_Path, Dest_File As String

Dim xlApp As Object

Dest_Path = "C:\Inforce_Reduction\Result Files\" Dest_File = "Test1"

DoCmd.TransferSpreadsheet acExport, 10, _ "0801_Reduce Inforce", Dest_Path & Dest_File, True

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True xlApp.Workbooks.Open Dest_Path & Dest_File & ".XLSX", True, False

End Sub

Thanks for any help!

Answer 1

you can adapt this line of code to your needs:

xl2.ActiveWorkbook.SaveAs ThisWorkbook.Path & "/" & "name your file" & ".csv"

xl2= it's the excel file you wana save it so change that with xlApp or what you have declare your excel file

Answer 2

Just in case you want to expand you idea and export ALL objects in your DB to Text files, run the script below.

Private Sub Command4_Click()


On Error GoTo Err_ExportDatabaseObjects

    Dim db As Database
    'Dim db As DAO.Database
    Dim td As TableDef
    Dim d As Document
    Dim c As Container
    Dim i As Integer
    Dim sExportLocation As String

    Set db = CurrentDb()

    sExportLocation = "C:\Users\rs17746\Desktop\Text_Files\" 'Do not forget the closing back slash! ie: C:\Temp\

    For Each td In db.TableDefs 'Tables
        If Left(td.Name, 4) <> "MSys" Then
            DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".txt", True
        End If
    Next td

    Set c = db.Containers("Forms")
    For Each d In c.Documents
        Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" & d.Name & ".txt"
    Next d

    Set c = db.Containers("Reports")
    For Each d In c.Documents
        Application.SaveAsText acReport, d.Name, sExportLocation & "Report_" & d.Name & ".txt"
    Next d

    Set c = db.Containers("Scripts")
    For Each d In c.Documents
        Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" & d.Name & ".txt"
    Next d

    Set c = db.Containers("Modules")
    For Each d In c.Documents
        Application.SaveAsText acModule, d.Name, sExportLocation & "Module_" & d.Name & ".txt"
    Next d

    For i = 0 To db.QueryDefs.Count - 1
        Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
    Next i

    Set db = Nothing
    Set c = Nothing

    MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation

Exit_ExportDatabaseObjects:
    Exit Sub

Err_ExportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects

End Sub

Answer 3

Here is one more version for you. This will export the results of each query, each to a separate text file.

Private Sub Command0_Click()


Dim qdf As QueryDef
Dim strFileName As String
For Each qdf In CurrentDb.QueryDefs
If Left(qdf.Name, 1) <> "~" Then

'you need to figure out TransferText command. Maybe
'you won't be lazy and expect people to read it to
'you and tutor you on how it works.
strFileName = qdf.Name

'Docmd.TransferText ....
DoCmd.TransferText transferType:=acExportDelim, TableName:=strFileName, FileName:="C:\test\" & strFileName & ".txt", hasfieldnames:=True

End If
Next qdf
MsgBox "Done"


End Sub

Answer 4

Ok, well, you can use this, to print the actual SQL.

Private Sub Command2_Click()

Dim db As Database
Dim qr As QueryDef

Set db = CurrentDb

For Each qr In db.QueryDefs
  TextOut (qr.Name)
  TextOut (qr.SQL)
  TextOut (String(100, "-"))
Next

End Sub

Public Sub TextOut(OutputString As String)

    Dim fh As Long

    fh = FreeFile
    Open "C:\Users\rs17746\Desktop\Text_Files\sample.txt" For Append As fh
    Print #fh, OutputString
    Close fh

End Sub