如何使用VBA从Excel导出带引号逗号格式的文本文件？

Got it, let's sort out this text file export issue. The goal is to have each data field wrapped in quotes and separated by ","—I know firsthand that getting the quote escaping right in VBA can be tricky, so let's break this down step by step.

The Core Issue: Escaping Quotes in VBA #

In VBA, to include a double quote (") inside a string, you have to escape it with another double quote. So the separator you want (",") translates to """,""" in VBA code. Your original attempt was on the right track, but likely had a missing or extra quote somewhere in the concatenation.

Corrected Working Code #

Here's a complete, tested version of your code that properly formats the data and saves it to a text file:

Private Sub CommandButton1_Click()
    Dim FilePath As String
    Dim CellData As String
    Dim StockCode As String
    Dim JobNumber As String
    Dim TheDateTime As String
    
    ' Extract values from cells (convert to string to avoid data type inconsistencies)
    StockCode = CStr(Range("H1").Value)
    JobNumber = CStr(Range("H2").Value)
    
    ' Generate timestamp (added leading zeros for consistency, e.g., 05 instead of 5 for May)
    TheDateTime = Format(Now, "mmddyyyyhhmmss")
    
    ' Build the formatted data string: each field wrapped in quotes, separated by ","
    CellData = """" & StockCode & """" & """,""" & JobNumber & """" & """,""" & TheDateTime & """"
    
    ' Let user select save location (user-friendly alternative to hardcoding paths)
    FilePath = Application.GetSaveAsFilename( _
        FileFilter:="Text Files (*.txt), *.txt", _
        Title:="Export Data to Text File")
    
    ' Proceed only if user didn't cancel the save dialog
    If FilePath <> "False" Then
        ' Open file for writing (overwrites existing file; use Append instead of Output to add to file)
        Open FilePath For Output As #1
        ' Write the formatted line to the file
        Print #1, CellData
        ' Close the file to free resources
        Close #1
        
        ' Optional: Confirm success to the user
        MsgBox "Data saved successfully to:" & vbCrLf & FilePath, vbInformation
    End If
End Sub

Key Improvements Explained #

• Proper Quote Escaping: The CellData line uses """" to add a single double quote around each field, and """,""" to insert the "," separator between fields.
• String Conversion: Using CStr() ensures numbers, dates, or empty cells are treated as strings, preventing unexpected formatting issues.
• Consistent Timestamp: Format(Now, "mmddyyyyhhmmss") adds leading zeros to months/days/hours etc., so your timestamp is always 12 characters long (e.g., 05202024143025 instead of 5202024143025).
• User-Friendly Save Dialog: GetSaveAsFilename lets the user choose where to save the file instead of relying on a hardcoded path.

Adding More Fields #

If you need to include additional fields (e.g., a customer name in cell H3), just extend the CellData line following the same pattern:

Dim CustomerName As String
CustomerName = CStr(Range("H3").Value)
CellData = """" & StockCode & """" & """,""" & JobNumber & """" & """,""" & CustomerName & """" & """,""" & TheDateTime & """"

Exporting Multiple Rows #

If you need to export multiple rows of data (e.g., rows 5 to 20), you can loop through the rows and write each line to the file:

' Inside the If FilePath <> "False" block:
Open FilePath For Output As #1
Dim i As Integer
For i = 5 To 20
    StockCode = CStr(Range("H" & i).Value)
    JobNumber = CStr(Range("I" & i).Value)
    CellData = """" & StockCode & """" & """,""" & JobNumber & """"
    Print #1, CellData
Next i
Close #1

内容的提问来源于stack exchange，提问作者Djones12