使用VBA修改CSV文件

粉红豹

我需要使用VBA修改CSV文件的帮助。我进行了研究并提出了此解决方案。但是，我无法获得预期的输出。因此，例如，我有一个CSV文件：

ProductID,ProductName,SupplierName,CategoryID,Unit,Price
,,,,,
1,Chais,John Ray,1,10 boxes x 20 bags,18.00093483
2,Chang,Michael,1,24 - 12 oz bottles,19.66890343

我想更改productname和下的所有值suppliername。然后更改类似ProductID和Column Name的组合。我的预期输出应如下所示：

    ProductID,ProductName,SupplierName,CategoryID,Unit,Price
,,,,,
1,1 ProductName,1 SupplierName,1,10 boxes x 20 bags,18.00093483
2,2 ProductName,2 SupplierName,1,24 - 12 oz bottles,19.66890343

它可以发生多次，并且可以更改列的位置。这是我的代码：

Sub test()

    Dim FilePath As String, LineFromFile As Variant, LineItems() As String, strFile As Variant
    FilePath = "C:\Users\mestrivo\Documents\Files\MyFirstProg\test.csv"
    Open FilePath For Input As #1

    Do Until EOF(1)
        Line Input #1, LineFromFile
        LinteItems = Split(LineFromFile, ",")
        LineItems(1) = LineItems(0) & " ProductName"
        LineItems(2) = LineItems(0) & " SupplierName"
        strFile = Join(LineItems, ",")
    Loop

    Open "C:\Users\mestrivo\Documents\Files\MyFirstProg\test - 2.csv" For Output As #1

    Print #1, strFile
    Close #1

End Sub

请帮助我检查我的代码。我在这部分上有一个错误：

Open "C:\Users\mestrivo\Documents\Files\MyFirstProg\test - 2.csv" For Output As #1

它说该文件已经打开。

旅程

尝试这个：

Sub test()
    Dim FilePath As String, LineFromFile As Variant, _
    LineItems() As String, strFile As Variant

    FilePath = "mycsv.csv"
    Open FilePath For Input As #1

    Do Until EOF(1)
        Line Input #1, LineFromFile
        LineItems() = Split(LineFromFile, ",")

        'I suggest to add the following 'If' statements
        If LineItems(0) <> "" Then
            LineItems(1) = LineItems(0) & " ProductName"
            LineItems(2) = LineItems(0) & " SupplierName"
        End If

        If strFile <> "" Then strFile = strFile & Chr(10)
        '-------------------------------------------------

        strFile = strFile & Join(LineItems, ",")
    Loop

    'In the next 'Open' statement you are trying to
    'assign an object over another that's already opened,
    'therefore you must close the previous object first
    'and / or declare the second one with another name
    Close #1
    Open "mycsv2.csv" For Output As #1
    Print #1, strFile
    Close #1
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。