vba - 另存为 .CSV 和 .txt

Question

我确信这很简单，但让我难以置信。我写了下面的代码。您可以在某些地方看到我将“我的最终付款”和“非我的最终付款”保存为 CSV 文件。有没有办法让我也可以将文件另存为 txt 文件。如果可能的话，最好是在同一位置？一如既往，我们非常感谢您的帮助。

 Option Explicit

 Sub BACSConversion()

 Dim MyNewBook As String
 Dim MySaveFile As String
 Dim fileToOpen As Variant
 Dim fileName As String
 Dim sheetName As String
 Dim rCopy As Range

 'Turn off display alerts
 Application.DisplayAlerts = False
 'Turn off screen updates
 Application.ScreenUpdating = False

 'Ensures that the file open directory is always the same
  ChDir "S:\MERIT OUTPUTS FOLDER\MSI Recruitment Limited\"

 'Opens the folder to location to select txt file
  fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If fileToOpen <> False Then
    Workbooks.OpenText fileName:=fileToOpen, _
    DataType:=xlDelimited, Tab:=True
    End If
 'Creates the file name based on txt file name
  fileName = Mid(fileToOpen, InStrRev(fileToOpen, "\") + 1)
 'Creates the sheet name based on the active txt file
  sheetName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)

 'Save active file as...
  ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
  Limited\BACS File Original\" & _
  fileName & ".CSV")

 'Selects all data in column A and copies to clipboard
  Set rCopy = Range("A1", Range("A1").End(xlDown))

 'Open the original document where the BACS file is located
   Workbooks.Open "S:\Accounts (New)\Management Information 
 (Analysis)\Phil Hanmore - Analysis\bacs conversation calc.xlsx"
 'Selects the worksheet called "Original"
  Sheets("Original").Range("A:A").ClearContents

 'Paste selected values from previous sheet
  rCopy.Copy
  Sheets("Original").Range("A1").PasteSpecial Paste:=xlPasteValues

 'Selects appropriate worksheet - Non-MyPayFINAL
   Sheets("Non-MyPay FINAL").Select

  'Selects all data in column A and copies to clipboard
   Range("A1", Range("A1").End(xlDown)).Select
  Selection.Copy

  'Add a new workbook
   Workbooks.Add
 'Paste selected values from previous sheet
  Selection.PasteSpecial Paste:=xlPasteValues

 'Build SaveAs file name
  MySaveFile = Format(Now(), "DDMMYYYY") & "NonMyPayFINAL" & ".CSV"
 'Save template file as...
  ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
  Limited\" & MySaveFile)
 'Close the new saved file
   ActiveWorkbook.Close

 'Selects appropriate worksheet - MyPayFINAL
   Sheets("MyPay FINAL").Select

 'Selects all data in column A and copies to clipboard
 Range("A1", Range("A1").End(xlDown)).Select
 Selection.Copy

 'Add a new workbook
  Workbooks.Add
 'Paste selected values from previous sheet
 Selection.PasteSpecial Paste:=xlPasteValues

 'Build SaveAs file name
  MySaveFile = Format(Now(), "DDMMYYYY") & "MyPayFINAL" & ".CSV"
 'Save template file as...
  ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
  Limited\" & MySaveFile)
  'Close the new saved file
   ActiveWorkbook.Close
 'Close original source workbook (template)
  Workbooks("bacs conversation calc").Close

 'Turn on display alerts
Application.DisplayAlerts = True
 'Turn on screen updates
Application.ScreenUpdating = True

 End Sub

Answer 1

将其保存为 .csv 文件后，只需重新执行相同的代码，但将 .csv 更改为 .txt

但这不一定会改变文件的类型。在 Activeworkbook.SaveAs 命令后，添加 FileFormat:= xlTextWindows。这实际上会将其保存为文本文件。

它应该看起来像:

ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment
有限\"& MySaveFile), FileFormat:= xlTextWindows

我建议在保存工作簿时查找多种不同类型的“文件格式”，只是为了熟悉它们。只需搜索 Excel FileFormats，您应该能够找到有关所有这些文件格式的信息。

更新:

正如评论中提到的，您保存的 CSV 文件可能实际上并未保存为逗号分隔的文件。为此，与我上面所说的类似，您可以将初始保存的文件格式更改为 xlCSV。这可以通过将 FileFormat:= xlcsv 添加到第一个 ActiveWorkbook.SaveAs

的末尾来实现

当您确实有兴趣更改文件类型而不仅仅是扩展名时，需要文件格式。以下是解释所有不同格式的 MSDN 站点的链接。 MSDN XLFileFormats 。您还可以使用分配给这些格式的数值，而不是使用文本 xlCSV 或 xlTextWindows。除了 FileFormat:= xlCSV，您可以执行 FileFormat:= 6，或者对于 xlTextWindows，您可以执行 FileFormat:= 20 >