Reading CSV file into excel using VBA then outputting the results to a specific worksheet

Question

I've been searching for a while and can't find a simple solution. I've got a csv file that I would like to read into excel using vba and output the results into a specific cell range on a specific worksheet.

I've been using the following code, but if I run the excel macro twice it basically appends the data on the next blank column instead of copying over it. It also only pastes it into the active sheet instead of my specific sheet.

Any suggestions on how I can do this?

Thanks,

Public Sub Example()
    Const csPath As String = "starting_positions.csv"
    Dim ws As Excel.Worksheet
    Set ws = Excel.ActiveSheet
    With ws.QueryTables.Add("TEXT;" & csPath, ws.Cells(1, 1))
        .FieldNames = True
        .AdjustColumnWidth = True
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileCommaDelimiter = True
        ''// This array will need as many entries as there will be columns:
        .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat)
        .Refresh
    End With
End Sub

Peter L. · Accepted Answer · 2013-01-29 20:37:56Z

4

Assuming you run the code in the same workbook you need to collect CSV data to, try the following:

Replace Set ws = Excel.ActiveSheet with Set ws = ThisWorkbook.Sheets(1) or Set ws = ThisWorkbook.Sheets("Sheet1") to define any specific sheet using either its index or name.
ws.UsedRange.ClearContents place prior With... block will clear the whole sheet, and therefore new data will be placed to the same place as before.

One more hint: in case you're going to use this macro many times - do not create every time a new connection. In case all the options are similar - only use .Refresh method.

If you're stuck with the merging of code parts - try to use macro recorder passing any of the steps manually. For your case this will solve the majority of troubles. Good luck!

answered Jan 29, 2013 at 20:37

Peter L.

7,3445 gold badges37 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1898958 Over a year ago

Worked perfectly. Thanks!

user1898958 Over a year ago

Actually a follow up. I'm only going to be using this once when I open the excel sheet. Should I be worried about closing the connection? or is it fine as is?

Peter L. Over a year ago

@user1898958 I think such "connections" are fine to leave as it is - I use such one-time solutions frequently and don't even think about it. However, if you're pretty sure this is "launch and forget" solution - add the following lines of code: .Name = "Conn_Name" - anywhere in the connection setup options block, and ThisWorkbook.Connections("Conn_Name").Delete after End With. This will add connection, retry data, and delete it thereafter.

Collectives™ on Stack Overflow

Reading CSV file into excel using VBA then outputting the results to a specific worksheet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related