vba - 将选择导出到 CSV

Question

我创建了一个 Excel 电子表格模板，供我们的客户填写并发送回给我们。我想手动选择他们填充的数据并将其保存为 .csv 以导入另一个软件。我首先通过录制宏来尝试此操作。这不起作用，因为不同的客户发送不同数量的记录。

我尝试了来自在线研究的代码片段并想出了这个。

Sub Select_To_CSV()
   Dim rng As Range
   Dim myrangearea()
   Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select


   Dim myPath As String, v
   myPath = "p:\" & _
     Format(Date, "yyyymmdd") & ".csv"
   'myPath = "x:\" & Format(Date, "yyyymmdd") & ".csv"
    v = SaveAs(myPath)
    If v <> False Then ThisWorkbook.SaveAs v
End Sub

Function SaveAs(initialFilename As String)
  On Error GoTo EndNow
  SaveAs = False
  With Application.FileDialog(msoFileDialogSaveAs)
    .AllowMultiSelect = False
    .ButtonName = "&Save As"
    .initialFilename = initialFilename
    .Title = "File Save As"
    '.Execute
    .Show
    SaveAs = .SelectedItems(1)
  End With
EndNow:
End Function

Sub Select_To_CSV()
   Dim rng As Range
   Dim myrangearea()
   Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select


   Dim myPath As String, v
   myPath = "p:\" & _
     Format(Date, "yyyymmdd") & ".csv"
   'myPath = "x:\" & Format(Date, "yyyymmdd") & ".csv"
    v = SaveAs(myPath)
    If v <> False Then ThisWorkbook.SaveAs v
End Sub

这非常有效，除了当我回去查看文件夹中的 .csv 时，它是同一个工作表而不是选定的列。

最终我想做的是，

手动选择我想要的列

运行将选定列转换为 .csv

的宏

出现另存为对话框

导航到我想要的某个文件夹。

Answer 1

干得好:

Sub MacroMan()

ChDrive "P:" '// <~~ change current drive to P:\
Dim copyRng As Excel.Range
Dim ThisWB  As Excel.Workbook
Dim OtherWB As Excel.Workbook
Dim sName   As String

'// set reference to the 'Master' workbook
Set ThisWB = ActiveWorkbook

'// assign selected range to 'copyRng'
Set copyRng = Application.InputBox(Prompt:="Select range to convert to CSV", Type:=8)

'// If the user selected a range, then proceed with rest of code:
If Not copyRng Is Nothing Then
    '// Create a new workbook with 1 sheet.
    Set OtherWB = Workbooks.Add(1)

    '// Get A1, then expand this 'selection' to the same size as copyRng. 
    '// Then assign the value of copyRng to this area (similar to copy/paste)
    OtherWB.Sheets(1).Range("A1").Resize(copyRng.Rows.Count, copyRng.Columns.Count).Value = copyRng.Value

    '// Get save name for CSV file.
    sName = Application.GetSaveAsFilename(FileFilter:="CSV files (*.csv), *.csv")

    '// If the user entered a save name then proceed:
    If Not LCase(sName) = "false" Then
        '// Turn off alerts
        Application.DisplayAlerts = False
        '// Save the 'copy' workbook as a CSV file
        OtherWB.SaveAs sName, xlCSV
        '// Close the 'copy' workbook
        OtherWB.Close
        '// Turn alerts back on
        Application.DisplayAlerts = True
    End If

    '// Make the 'Master' workbook the active workbook again
    ThisWB.Activate

    MsgBox "Conversion complete", vbInformation
End If

End Sub

这将允许您手动选择一个范围(包括整个列)。然后它将所述范围转移到新工作表上，使用“另存为”对话框将该工作表另存为 CSV，然后将其关闭。