CODEWITHSUNDEEP
excelvba
sagittarius_88
sagittarius_88

Reputation: 59

Import multiple CSV files while adding the file name for each imported file

I've been trying to import multiple CSV files, each file has a unique name. What I'm trying to do is: Add a column with the file names filled all the way to end for each imported file.

Sub ImportMultipleCSV()

Dim myfiles
Dim i As Integer
Dim j As Integer
Dim Answer

myfiles = Application.GetOpenFilename(filefilter:="CSV Files (*.csv), *.csv", MultiSelect:=True)


    If IsArray(myfiles) Then
    Answer = MsgBox("Delete Files after Import?", vbYesNo + vbQuestion)
        For i = LBound(myfiles) To UBound(myfiles)
            With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;" & myfiles(i), Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1, 0))
                .RefreshStyle = xlOverwriteCells
                .AdjustColumnWidth = True
                .TextFileStartRow = 2
                .TextFileParseType = xlDelimited
                .TextFileCommaDelimiter = True
                .Refresh

              'add file name to Seperate column

             Range("A" & Rows.Count).End(xlUp).Offset(0, 7).Value = myfiles(i)
**^^ this line only adds the file name, but I want to fill down.**


            End With

            If Answer = vbYes Then
                Kill myfiles(i)
            End If
        Next i

    Else
        MsgBox "No File Selected"
    End If


Dim xConnect As Object
    For Each xConnect In ActiveWorkbook.Connections
        If xConnect.Name <> "ThisWorkbookDataModel" Then xConnect.Delete
    Next xConnect


'Range("C:C,E:E,G:G").Delete


End Sub

This is my output file I'm trying to achieve. enter image description here

Any help is appreciated. Thank you so much!

Upvotes: 0

Views: 80

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

Change this:

Range("A" & Rows.Count).End(xlUp).Offset(0, 7).Value = myfiles(i)

to this:

Range(Range("H" & Rows.Count).End(xlUp).Offset(1), Range("A" & Rows.Count).End(xlUp).Offset(0,7)).Value = myFiles(i)

Upvotes: 3

Related Questions