CODEWITHSUNDEEP
excelvbaloopscsv
I. Я. Newb
I. Я. Newb

Reputation: 329

Loop through cells and import CSV files, by using the cells' value in the file path

I have 1 sheet with asset names in the top row and dates in the first column.

Current file

I need to loop trough all cells in the first row, take the cell's value and include it in the file path to import column 6 from a text file.

So far I've got this:

For i = 2 To HCP.Cells(1, HCP.Columns.Count).End(xlToLeft).Column
    If HCP.Cells(1, i).Value <> "" Then
        j = HCP.Cells(2, HCP.Columns.Count).End(xlToLeft).Column + 1
            With HCP.QueryTables.Add(Connection:="TEXT;" & Folder & "\" & i & "1440.CSV", Destination:=HCP.Cells(2, j + 1))
                .TextFileParseType = xlDelimited
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = True
                .TextFileColumnDataTypes = Array(9, 9, 9, 9, 9, 1, 9, 9, 9, 9)
                .Refresh BackgroundQuery:=False
                i = i + 1
            End With
    End If
Next

And I get this at the .Refresh BackgroundQuery:=False line:

Error Message

I reckon I am not using the variables in the loop correctly.

Upvotes: 1

Views: 209

Answers (1)

Senor Penguin
Senor Penguin

Reputation: 405

It's because you're not actually setting i to any value in a cell, it's only your counter. You need to set HCP.Cells(1, i).Value to a variable and pass that variable through your file path.

Try this:

For i = 2 To HCP.Cells(1, HCP.Columns.Count).End(xlToLeft).Column

    rowContent = HCP.Cells(1, i).Value

    If rowContent <> "" Then
        j = HCP.Cells(2, HCP.Columns.Count).End(xlToLeft).Column + 1
            With HCP.QueryTables.Add(Connection:="TEXT;" & Folder & "\" & rowContent & "1440.CSV", Destination:=HCP.Cells(2, j + 1))
                .TextFileParseType = xlDelimited
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = True
                .TextFileColumnDataTypes = Array(9, 9, 9, 9, 9, 1, 9, 9, 9, 9)
                .Refresh BackgroundQuery:=False

            End With
    End If
Next

Also you dont need i = i + 1 in your code, the Next will automatically increment your i number up To the value you specified.

Upvotes: 1

Related Questions