CODEWITHSUNDEEP
excelvbaexcel-2016
Luca91
Luca91

Reputation: 609

VBA: import multiple csv file into an existing table

I want to import multiple csv files at the bottom of an existing table. However, when importing the files, it always excludes the first row of the list of each file. The first row of the list differs from the first row of the spreadsheet because in between there are other rows that are not needed (e.g. titles, empty rows...). Resuming: if I upload 5 files, it miss the first desired row of each of the 5 files.

This is the code: 

Private Sub Import_auction_offers_Click()
    Dim strSourcePath As String
    Dim strFile As String
    Dim Cnt As Long

    'Change the path to the source folder accordingly
    strSourcePath = "C:\Users\L18944\Desktop\example"

    If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"
    strFile = Dir(strSourcePath & "*.csv")

    Do While Len(strFile) > 0
        Cnt = Cnt + 1

Open strSourcePath & strFile For Input As #1

If Range("F2").Value <> "" Then
    Range("F1").End(xlDown).offset(1, 0).Select
Else:
    Range("F1:F" & Range("F" & Rows.Count).End(xlUp).Row).offset(1, 0).Select
End If
currentRow = 0
rowNumber = 0

'EOF(1) checks for the end of a file
Do Until EOF(1)
    Line Input #1, lineFromFile
    fileStr = Split(lineFromFile, vbLf)
    Dim item As Variant
    For Each item In fileStr
    'For item = LBound(fileStr) To UBound(fileStr)
        lineitems = Split(item, ";")
        'Debug.Print (item)
        If rowNumber = 1 Then
            startDate = lineitems(6)
        End If
        If rowNumber > 3 And item <> "" Then
            If Not doesOfferExist(CStr(lineitems(2))) Then
                ActiveCell.offset(currentRow, 0) = startDate
                ActiveCell.offset(currentRow, 1) = lineitems(4)
                ActiveCell.offset(currentRow, 2) = lineitems(3)
                ActiveCell.offset(currentRow, 3) = CDbl(lineitems(6))
                ActiveCell.offset(currentRow, 4) = CDbl(lineitems(7))
                ActiveCell.offset(currentRow, 5) = lineitems(8)
                ActiveCell.offset(currentRow, 6) = lineitems(1)
                ActiveCell.offset(currentRow, 7) = lineitems(2)
                ActiveCell.offset(currentRow, 8) = "New"
                currentRow = currentRow + 1
            End If
        End If

        rowNumber = rowNumber + 1
    Next item
Loop
Close #1
 Name strSourcePath & strFile As strSourcePath & strFile
        strFile = Dir
    Loop

  Application.ScreenUpdating = True

    If Cnt = 0 Then _
        MsgBox "No CSV files were found...", vbExclamation

End Sub

Does anyone understand why it miss the first line of each imported list? Thank you in advance

Upvotes: 2

Views: 581

Answers (1)

JosephC
JosephC

Reputation: 929

I didn't go through your ImportAuctionOffers code, but I'm assuming you are finding the new starting row for each file.

This code will let you pick your files (and set your initial directory). Then loop through all the selected items, calling your ImportAuctionOffers procedure for each file.

Sub test()
    Dim oFileDialog As FileDialog

    Set oFileDialog = Application.FileDialog(msoFileDialogFilePicker)
    oFileDialog.AllowMultiSelect = True
    oFileDialog.InitialFileName = "C:\Temp"    ' can set your default directory here

    oFileDialog.Show

    Dim iCount As Integer
    For iCount = 1 To oFileDialog.SelectedItems.Count
        Call ImportAuctionOffers(oFileDialog.SelectedItems(iCount))
    Next

End Sub

Update:

For your second issue: Not reading the first data line is likely due to the if statements with RowNumber.

rowNumber=0

Do ...
    if RowNumber = 1 Then ...
    if RowNumber > 3 ...

    RowNumber = RowNumber + 1
loop

Your code is not going to enter either of your if statements when RowNumber equals 0, 2, or 3. You probably just need to change your > 3 to either > 2, or >= 3.

Upvotes: 1

Related Questions