excel 导出到.csv -如何处理不可见的引号？

我试图使用VBA用户表单制作一些Excel表单，以帮助我们分析crete有效的.csv文件，这是目前用于添加数据到数据库。什么是必要的-应该只有分号和逗号作为特殊字符。不幸的是，当我尝试导出.xlsm文件到.csv一切看起来都很好，但如果我打开文件，例如.记事本我看到额外的引号。它看起来像这样：
Excel表格：
| 识别码|集团|属性|
| - -|- -|- -|
| 一二三四;| GRP_A;|在此基础上，建立了一个新的数据库。等等。|
| 一二三五;| GRP_B;|在此基础上，建立了一个新的数据库。等等。|
记事本：1234;GRP_A;"VAL=abc, DT_CON=2022-10-20;" 1235;GRP_A;"VAL=abc, DT_CON=2022-10-20;"
我试图将文件保存为一些格式（包括xlCSVUTF 8-但当使用这个，我得到了错误1004），使用参数 * 本地：=真 *。没有像我想要的工作。我不知道如何处理这个使用VBA。
我的代码：

Sub CopyToCSV()
    Dim MyPath As String
    Dim MyFileName As String
        'The path and file names:
    MyPath = "C:\Users\BM73LJ\Documents\" ' ThisWorkbook.Path
     'Makes sure the path name ends with "\":
    If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
        'Copies the sheet to a new workbook:
    Sheets("WYNIKI").Copy
        'The new workbook becomes Activeworkbook:
    MyFileName = MyPath & tb_CorpoKey.Value & "_" & Format(Date, "yyyymmdd") & ".csv"
    ' ActiveWorkbook.WebOptions.Encoding = msoEncodingUTF8
    With ActiveWorkbook
        'Saves the new workbook to given folder / filename:
    .SaveAs Filename:= _
        MyFileName, _
        FileFormat:=xlUnicodeText
    'Closes the file
    .Close False
End With
End Sub

尝试像这样更改代码：

With ActiveWorkbook
        'Saves the new workbook to given folder / filename:
    .SaveAs Filename:= _
        MyFileName, _
        FileFormat:=xlTextPrinter 'THIS IS THE CHANGE
    'Closes the file
    .Close False
End With

更新日期：

嘿，Kooneer，尝试添加以下更改，它在保存工作簿之前清除了某些不可见字符的单元格。

Sub CopyToCSV()
    Dim MyPath As String
    Dim MyFileName As String
        'The path and file names:
    MyPath = "C:\Users\BM73LJ\Documents\" ' ThisWorkbook.Path
     'Makes sure the path name ends with "\":
    If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
    
        'Copies the sheet to a new workbook:
    Sheets("WYNIKI").Copy
    
    With Application
        .calculation = xlCalculationManual
        .DisplayStatusBar = False
        .enableEvents = False
        .screenUpdating = False
        .Interactive = False
        .DisplayAlerts = False
        .AskToUpdateLinks = False
    End With
    
    Dim rng As Range, cell As Range
    
    Set rng = getDataRange(Sheets("WYNIKI"))
    
    For Each cell In rng
        
        With cell
            
            .value = Application.WorksheetFunction.Clean(.value)
            
        End With
        
    Next cell
    
        'The new workbook becomes Activeworkbook:
    MyFileName = MyPath & tb_CorpoKey.value & "_" & Format(Date, "yyyymmdd") & ".csv"
    ' ActiveWorkbook.WebOptions.Encoding = msoEncodingUTF8
    With ActiveWorkbook
        'Saves the new workbook to given folder / filename:
        .SaveAs Filename:= _
            MyFileName, _
            FileFormat:=xlTextPrinter
        'Closes the file
        .Close False
    End With
    
    With Application
        .calculation = xlCalculationAutomatic
        .DisplayStatusBar = True
        .enableEvents = True
        .screenUpdating = True
        .Interactive = True
        .DisplayAlerts = True
        .AskToUpdateLinks = True
    End With
    
End Sub

Private Function getLastCell(ws As Worksheet, Optional lookin As Long = xlFormulas) As Range

    Dim lastRow As Long, lastCol As Long
    
    With ws
        
        lastRow = .Cells.Find(What:="*", After:=.Cells(1, 1), LookAt:=xlPart, lookin:=lookin, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        lastCol = .Cells.Find(What:="*", After:=.Cells(1, 1), LookAt:=xlPart, lookin:=lookin, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
        Set getLastCell = .Cells(lastRow, lastCol)
        
    End With

End Function

Private Function getFirstCell(ws As Worksheet, Optional lookin As Long = xlFormulas) As Range

    Dim firstRow As Long, firstCol As Long
    
    With ws
        
        firstRow = .Cells.Find(What:="*", After:=.Cells(.Cells.Rows.count, .Cells.Columns.count), LookAt:=xlPart, lookin:=lookin, searchorder:=xlByRows, searchdirection:=xlNext).Row
        firstCol = .Cells.Find(What:="*", After:=.Cells(.Cells.Rows.count, .Cells.Columns.count), LookAt:=xlPart, lookin:=lookin, searchorder:=xlByColumns, searchdirection:=xlNext).Column
        Set getFirstCell = .Cells(firstRow, firstCol)
        
    End With

End Function

Private Function getDataRange(ws As Worksheet, Optional lookin As Long = xlFormulas) As Range

    Dim firstCell As Range, lastCell As Range
    
    Set firstCell = getFirstCell(ws, lookin)
    Set lastCell = getLastCell(ws, lookin)
    Set getDataRange = ws.Range(firstCell, lastCell)

End Function

我不知道你是否也需要在每个单元格上使用trim函数来清理空间。如果是这样的话，你还应该在循环中添加.Value = Trim(.Value)。
你现在的产量怎么样？？

excel 导出到.csv -如何处理不可见的引号？

1条答案

相关问题

热门标签

最新问答