如何将.xlsx保存到.csv或从访问VBA.txt

Question

我已经成功地使用 Access VBA 将查询导出到.xlsx，

并且我已经使用 VBA 打开了.xlsx文件，但现在我需要执行"另存为"以将文件转换为.csv，或者如果可能的话，.txt。这是包含数千个文件的大型自动化过程的一部分，所以我真的不能有任何手动步骤。我需要从查询到.txt的过程在 Access VBA 中完全自动化。这是我当前的代码，它成功打开了我创建的文件：

Sub Export_Reduced_Inforce()
    Dim Dest_Path, Dest_File As String
    Dim xlApp As Object
    Dest_Path = "C:Inforce_ReductionResult 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

Answer 1

您可以

根据需要调整以下代码行：

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

xl2= 这是您想要保存的 Excel 文件，因此请使用 xlApp 或您声明的 Excel 文件进行更改

Answer 2

如果您想

扩展您的想法并将数据库中的所有对象导出到文本文件，请运行下面的脚本。

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:Usersrs17746DesktopText_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

这是另一个版本。 这会将每个查询的结果导出到单独的文本文件中。

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

好的，好吧，你可以使用它来打印实际的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:Usersrs17746DesktopText_Filessample.txt" For Append As fh
    Print #fh, OutputString
    Close fh
End Sub