Excel VBA在Saveas方法中转换为CSV

Question

我正在尝试在。是否可以将此代码以 application.dialogs（xldialogsaveas）的形式使用。显示（arg2：= xlcsv）方法，这样我可以选择在哪里保存CSV文件？

Option Explicit

Sub CSV_Makerr()
   Dim r As Range
   Dim sOut As String, k As Long, M As Long
   Dim N As Long, nFirstRow As Long, nLastRow As Long
   Dim MyFilePath As String, MyFileName As String
   Dim fs, a, mm As Long
   Dim separator As String

   ActiveSheet.UsedRange
   Set r = ActiveSheet.UsedRange
   nLastRow = r.Rows.Count + r.Row - 1
   nFirstRow = r.Row
   separator = ","

   MyFilePath = "C:\TestFolder\"
   MyFileName = "whatever"
   Set fs = CreateObject("Scripting.FileSystemObject")
   Set a = fs.CreateTextFile(MyFilePath & MyFileName & ".csv", True)

   For N = nFirstRow To nLastRow
       k = Application.WorksheetFunction.CountA(Cells(N, 1).EntireRow)
       sOut = ""
       If k = 0 Then

       Else
           M = Cells(N, Columns.Count).End(xlToLeft).Column
           For mm = 1 To M
               sOut = sOut & Cells(N, mm).Text & separator
           Next mm
           sOut = Left(sOut, Len(sOut) - 1)
           a.writeline (sOut)
       End If
   Next

   a.Close
End Sub

这个想法是从CSV或空白列中删除逗号，即使我删除了几次，这些逗号即使存在。上面的代码有效，但没有自由来为不同的最终用户或PC选择位置路径。请让我知道是否可能。

Answer 1

这样的东西？

Sub CSV_Makerr()
   Dim r As Range
   Dim sOut As String, k As Long, M As Long
   Dim N As Long, nFirstRow As Long, nLastRow As Long
   Dim MyFilePath As String, MyFileName As String
   Dim fs, a, mm As Long
   Dim separator As String

   ActiveSheet.UsedRange
   Set r = ActiveSheet.UsedRange
   nLastRow = r.Rows.Count + r.Row - 1
   nFirstRow = r.Row
   separator = ","

   MyFilePath = Application.GetSaveAsFilename(fileFilter:="CSV Files (*.csv), *.csv")
   If MyFilePath <> "" Then
       Set fs = CreateObject("Scripting.FileSystemObject")
       Set a = fs.CreateTextFile(MyFilePath, True)
    
       For N = nFirstRow To nLastRow
           k = Application.WorksheetFunction.CountA(Cells(N, 1).EntireRow)
           sOut = ""
           If k = 0 Then
    
           Else
               M = Cells(N, Columns.Count).End(xlToLeft).Column
               For mm = 1 To M
                   sOut = sOut & Cells(N, mm).Text & separator
               Next mm
               sOut = Left(sOut, Len(sOut) - 1)
               a.writeline (sOut)
           End If
       Next
       a.Close
   End If
End Sub