vba copy from csv

[Chas17]

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

 

[knichols]

So I wrote this little bit of code here:

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