vba - 将行附加到 .csv 文件会以某种方式丢失前两列数据

Question

我正在使用在这里找到的函数:Apend2CSV将更改的行附加到 CSV 文件。我现在已经在几个不同的项目中成功地使用了这个过程，但是这次它忽略了我试图附加的前两​​列。据我所知，一切都设置正确，我希望有更好的眼光的人能指出我的问题所在。该代码由 Worksheet_Change 事件触发，但它是一个单独的过程，因为它也被程序的其他部分调用。

在这种情况下， Range("A4:BB4") 应该被附加，但实际上只有 Range("C4:BB4") 附加。这是一个计算范围，其公式通过替换 " 的每个实例来解释 .csv 的潜在怪异之处，例如文本中的引号和逗号。与 ""并在附加值之前将所有值括在引号中。

这是代码:

Sub Append2CSV()
    Sheets("ToCSV").Calculate
    Dim tmpCSV As String
    Dim f As Integer
    Const CSVFile As String = "C:\TheCSV\WBCSV.csv"

    f = FreeFile
    Open CSVFile For Append As #f

    tmpCSV = Range2CSV(Sheets("ToCSV").Range("A4:BB4"))

    Print #f, tmpCSV
    Close #f
    ThisWorkbook.Saved = True
End Sub

Private Function Range2CSV(list) As String
    Dim tmp As String
    Dim cr As Long
    Dim r As Range

    If TypeName(list) = "Range" Then
        cr = 1
        For Each r In list.Cells
            If r.Row = cr Then
                If tmp = vbNullString Then
                    tmp = r.Value
                Else
                    tmp = tmp & "," & r.Value
                End If
            Else
                cr = cr + 1
                If tmp = vbNullString Then
                    tmp = r.Value
                End If
            End If
        Next
    End If
    Range2CSV = tmp
End Function

这是 .csv 文件中的文本:

A,AscendSKU,UPCNumber,VendorPartNumber,MFGPartNumber,Divison,G,PhysicalQOHAtTimeOfRecord,AscendQOHAtTimeOfRecord,ChosenVendor,Status,L,M,N,O,P,Q,R,S,Cost,Price,V,W,Location,DateRecordCreated,Z,UniqueID,DateTimeSerial,CurrentAscendQOH,CurrentAscendQOO,CurrentAscendYTD,Brand,ClickHereToStartBuyerModeCategory,AH,DateRecordModified,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,ChangedDuringBuyerMode
"","11833300044D","879410002474","ST6284","ST6284","1","1181 HI-RISE 1-1/8""x31.8 STEM","","0","Hawley","","","","","","","","","","9.01","19.99","","","","42277","","42277.5861111111---...---11833300044D","42277.5861111111","","","","ELEVEN81","Parts - Stems - Mountain and Hybrid","","42277.6491435185","","","","","","","","","","","","","","","","",""
"","CHA27625539K","719676285276","60814-0424","60814-0424","1","16 SPEC CHAMONIX HELMET","","2","Specialized Bicycle Components","","","","","","","","","","19.6","49.99","","","","42277","","42277.5841550926---...---CHA27625539K","42277.5841550926","","","","Specialized","Accessories - Helmets - Road - z.Mens","","42277.6491666666","","","","","","","","","","","","","","","","",""
"","CHA27625539K","719676285276","60814-0424","60814-0424","1","16 SPEC CHAMONIX HELMET","","2","Specialized Bicycle Components","","","","","","","","","","19.6","49.99","","","","42277","","42277.5841550926---...---CHA27625539K","42277.5841550926","","","","Specialized","Accessories - Helmets - Road - z.Mens","","42277.6491666666","","","","","","","","","","","","","","","","",""
"","WMS291257455","072774380459","38045","38045","1","WM SOLID RR AXLE SET 3/8x26x126x175","0","0","J & B Importers","","","","","","","","","","1.69","5.99","","","","42041","","42041.6198495370---...---WMS291257455","42041.619849537","","","","WHEEL MASTER","Parts - Hubs - Axles and Nuts and Cones","","42277.6496064815","","","","","","","","","","","","","","","","",""
"","WMS291257455","072774380459","38045","38045","1","WM SOLID RR AXLE SET 3/8x26x126x175","0","0","J & B Importers","","","","","","","","","","1.69","5.99","","","","42041","","42041.6198495370---...---WMS291257455","42041.619849537","","","","WHEEL MASTER","Parts - Hubs - Axles and Nuts and Cones","","42277.6496064815","","","","","","","","","","","","","","","","",""
"","FLS17361201Z","036121700116","FL4050","FL4050","1","FL SHOCK OIL 5WT--.--16OZ GET 2","","0","Bicycle Technologies International","O","","","","","","","","","8.95","19.99","","","","42063","","42063.7094444444---...---FLS17361201Z","42063.7094444444","","","","FINISH LINE","Accessories - Maintenance - Suspension Fluid","","42277.6517939815","","","","","","","","","","","","","","","","",""
"","FLS17361201Z","036121700116","FL4050","FL4050","1","FL SHOCK OIL 5WT--.--16OZ GET 2","","0","Bicycle Technologies International","O","","","","","","","","","8.95","19.99","","","","42063","","42063.7094444444---...---FLS17361201Z","42063.7094444444","","","","FINISH LINE","Accessories - Maintenance - Suspension Fluid","","42277.6552893519","","","","","","","","","","","","","","","","",""
"","FLS17361201Z","036121700116","FL4050","FL4050","1","FL SHOCK OIL 5WT--.--16OZ GET 2","","0","Bicycle Technologies International","O","","","","","","","","","8.95","19.99","","","","42063","","42063.7094444444---...---FLS17361201Z","42063.7094444444","","","","FINISH LINE","Accessories - Maintenance - Suspension Fluid","","42277.6552893519","","","","","","","","","","","","","","","","",""
"","SPE298655664","719676126357","542-3700","542-3700","1","SPEC FLATBOY GLUELESS PATCHKIT '14""","8","18","Specialized Bicycle Components","","","","","","","","","","1.44","2.99","","","","42063","","42063.7109722222---...---SPE298655664","42063.7109722222","","","","Specialized","Accessories - Flat Repair and Prevention - Patch Kits - Glueless","","42277.6569791666","","","","","","","","","","","","","","","","",""

Answer 1

我会扔我的2c

子测试:

Sub Tester()
    Dim s, fso
    s = getCsvContent(Range("A1").CurrentRegion)
    Set fso = CreateObject("scripting.filesystemobject")
    With fso.createtextfile("C:\users\yournamehere\desktop\temp.csv", True)
        .write s
        .Close
    End With
End Sub

将范围转换为 CSV 的函数:

Function getCsvContent(rng As Range)
    Dim data, r As Long, c As Long, sep, lb, s, tmp
    data = rng.Value
    s = ""
    lb = ""
    For r = 1 To UBound(data, 1)
        s = s & lb
        sep = ""
        For c = 1 To UBound(data, 2)
            tmp = data(r, c)
            If IsError(tmp) Then tmp = "#Error!" '<<handle errors
            If InStr(tmp, """") > 0 Then
                tmp = Replace(tmp, """", """""")
            End If
            If InStr(tmp, ",") > 0 Then
                tmp = """" & tmp & """"
            End If
            s = s & sep & tmp
            sep = ","
        Next c
        lb = vbNewLine
    Next r
    getCsvContent = s
End Function