Copying CSV to Excel

toshi110

New Member
Joined
Nov 17, 2017
Messages
2
Hi Everyone

I have been using this code to copy data from csv file and merging it to a sheet. However, it has started messing up for some reason. Instead of pasting the data in new row starting from last row, it goes to the last column entry of first row and keep going to the next column (no row breaks). Any help is appreciated, it used to work properly but not anymore!

Here is the code:
Code:
strDestPath = Range("G28").Value
    
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Worksheets("Sheet1").Visible = True
Worksheets("Sheet1").Select
    
       
If Right(strDestPath, 1) <> "\" Then strDestPath = strDestPath & "\"


strFile = Dir(strSourcePath & "*.csv")
    
    Do While Len(strFile) > 0
        Cnt = Cnt + 1
        If Cnt = 1 Then
            r = 1
        Else
            
            r = Cells(Rows.Count, "A").End(xlUp).Row + 1
        End If
        Open strSourcePath & strFile For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
            If Cnt > 1 Then
                Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , strData
            End If
            Do Until EOF(1)
                Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , strData
                x = Split(strData, ",")
                For c = 0 To UBound(x)
                    Cells(r, c + 1).Value = Trim(x(c))
                Next c
                r = r + 1
                
                'Delete old date Data
                Worksheets("Sheet1").Select
                lRow = Cells(Rows.Count, 1).End(xlUp).Row
                date1 = CDate(Worksheets("Sheet1").Cells(2, 2))
                date2 = CDate(Worksheets("Sheet1").Cells(lRow, 2))
    
                If DateDiff("d", date2, date1) > 31 Then
                Range("B1").Select
                ActiveSheet.Range("$A:$AH").AutoFilter Field:=2, Operator:= _
                xlFilterValues, Criteria2:=Array(2, date1)
                Range("B2").Select
                Range(Selection, Selection.End(xlDown)).Select
                Selection.EntireRow.Delete
                ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
                ActiveSheet.ShowAllData
                End If
            Loop
        Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
        
        
        
        'Move file to different folder after copying its data
        Name strSourcePath & strFile As strDestPath & strFile
        strFile = Dir
    Loop
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
it should be a single statement:

Code:
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\bob\Documents\Book1.csv", Destination:=Range("$B$17"))
        .CommandType = 0
        .Name = "Book1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top