modifying csv file using vba

Question

I need help in modifying the CSV file using VBA. I did research and came up with this solution. However, I can't get the expected output. So, for example, I have a CSV file:

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

I want to change all the values under the productname and suppliername. And change something like the combination of ProductID and the Column Name. My expected output should look like:

    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

It can occur multiple times and can change the column location. This is my code:

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

Please help me check my code. I got an error on this part:

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

it says that the file is already open.

What is wrong with your code?

urdearboy
– urdearboy

2019-10-08 14:58:51 +00:00
Commented Oct 8, 2019 at 14:58 — urdearboy
– urdearboy, Commented Oct 8, 2019 at 14:58
LinteItems - use Option Explicit !

Tim Williams
– Tim Williams

2019-10-08 15:05:04 +00:00
Commented Oct 8, 2019 at 15:05 — Tim Williams
– Tim Williams, Commented Oct 8, 2019 at 15:05
@TimWilliams, i am already using option explicit.

pinkpanther
– pinkpanther

2019-10-08 15:11:54 +00:00
Commented Oct 8, 2019 at 15:11 — pinkpanther
– pinkpanther, Commented Oct 8, 2019 at 15:11
@urdearboy, i modified my question.

pinkpanther
– pinkpanther

2019-10-08 15:12:33 +00:00
Commented Oct 8, 2019 at 15:12 — pinkpanther
– pinkpanther, Commented Oct 8, 2019 at 15:12
Then why are you not seeing a compile error?

Tim Williams
– Tim Williams

2019-10-08 15:12:34 +00:00
Commented Oct 8, 2019 at 15:12 — Tim Williams
– Tim Williams, Commented Oct 8, 2019 at 15:12

Mathieu Guindon · Accepted Answer · 2019-10-08 15:18:32Z

NEVER hard-code file handles, they aren't for you to grab, they're for VBA to query what's available and give you a free, usable file handle. Use the FreeFile function to do this.

Dim fileHandle As Long
fileHandle = FreeFile

Then replace all hard-coded #1 handles with #fileHandle.

You cannot open two different files using the same handle. You've already opened the file for input:

Open FilePath For Input As #1

So when you try to use the same handle for output...

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

That's when you get an error; you haven't closed the #1 handle yet, and now you're trying to reuse it to open another file - you can't do that.

You're dealing with two files, so either you open the first one, read it, then close it before you open the second one, write to it and then close it - or, you open both, and write to one as you read the other, then close both.

Either way, you shouldn't hard-code file handles. Use FreeFile to get a free file handle. Always.

Ferd · Accepted Answer · 2019-10-08 15:54:45Z

0

Try this:

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

edited Oct 8, 2019 at 15:54

answered Oct 8, 2019 at 15:48

Ferd

1,3411 gold badge15 silver badges17 bronze badges

2 Comments

pinkpanther Over a year ago

Hi @Ferd, i just realized what if there is a comma in the input file? How do i ignore it if Im going to use the comma when splitting the data?

Ferd Over a year ago

Three options here: • Deal with those cases directly from LineFromFile variable, before splitting. • Make sure your files to not have that condition. • Use another separator, maybe |.

Collectives™ on Stack Overflow

modifying csv file using vba

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related