vba copy from csv

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
I have a csv file <file1.csv> which contains a result set
created by another application. What I'd like to do is use vba
to copy the values from sheet1 (or the whole sheet would be fine) into
file #2 <file2.xls> so users can run formulas, vlookups, etc. without
having to access a separate file (and leave the original data
untouched).

Thanks
Chas
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

knichols

New Member
Joined
Apr 8, 2009
Messages
1
So I wrote this little bit of code here:

Code:
Sub test()
    Dim valuesLine
    Open "data.csv" For Input As #1
    myColumn = 1
    myRow = 1
    Do While Not EOF(1)
        Line Input #1, valuesLine
        myarray = Split(valuesLine, ",")
        For i = 0 To UBound(myarray) - LBound(myarray)
            Sheet1.Cells(myRow, myColumn) = myarray(i)
            myColumn = myColumn + 1
        Next i
        myRow = myRow + 1
        myColumn = 1
    Loop
    Close #1
End Sub

If you have your data saved in a file called data.csv and you're simply reading that data line by line into an excel sheet, this will work. It takes the first value and puts it in the first row, first cell. Then it puts the next value in the first row, second cell. When it gets to the end of the line, it moves to the second row and repeats the process.

This leaves the data.csv file untouched and reads the data into an excel spreadsheet for processing. Is this what you were looking for? Or something else?

Kevin
 

Watch MrExcel Video

Forum statistics

Threads
1,129,682
Messages
5,637,764
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top