CODEWITHSUNDEEP
excelvbacsv
Sven
Sven

Reputation: 101

Why do i fail to read .csv data properly and how could i write the code just to select specific columns?

the code reads all csv data files from the same path/folder in which i save this VBA Code in a seperate .xlsm file.

The first file gets formated pretty well so i have 3 clean columns. By starting to read the second file within the path it starts to read all data as .csv

Do You know why?

And how would it be possible just to read specific columns from the files which are not specifically next to each other? Like Column A and Column C for example.

Thank you so much in advance

Sub Zusammenfuegen()
Dim strOrdner As String
Dim lngZeile As Long, lngZeileMax As Long
Dim lngZMax As Long, lngZeileFrei As Long
Dim wkbQuelle As Workbook
Dim objLST As ListObject

Application.ScreenUpdating = False

Tabelle4.UsedRange.Clear
'Tabelle4.Range("A1:C1").Value = Array("Datum", "Umsatz", "Region")
lngZeileFrei = 1
strOrdner = ThisWorkbook.Path

With Tabelle1
 
 lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row
 
 For lngZeile = 2 To lngZeileMax
 
   Workbooks.OpenText strOrdner & "\" & .Cells(lngZeile, 1).Value, Semicolon:=True, local:=True
   Set wkbQuelle = ActiveWorkbook
   .Cells(lngZeile, 2).Value = "eingelesen am " & Now
   
   With wkbQuelle.Worksheets(1)
    
    lngZMax = .Cells(.Rows.Count, 1).End(xlUp).Row
    lngZeileFrei = Tabelle4.Cells(Tabelle4.Rows.Count, 1).End(xlUp).Row + 1
    .Range(.Cells(2, 1), .Cells(lngZMax, 3)).Copy _
    Destination:=Tabelle4.Cells(lngZeileFrei, 1)
    
   End With
   wkbQuelle.Close savechanges:=False
   
 Next lngZeile

End With

With Tabelle4
 Set objLST = .ListObjects.Add(SourceType:=xlSrcRange, _
  Source:=.Range("A1").CurrentRegion, _
  xlListObjectHasHeaders:=xlYes)
 objLST.Name = "MeinListObject"
End With

Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 82

Answers (1)

user2316116
user2316116

Reputation: 6814

The code looks good and if there is any problem, check if csv is well-formatted.

With regard to specific columns: instead of taking all data

'.Range(.Cells(2, 1), .Cells(lngZMax, 3)).Copy _
'Destination:=Tabelle4.Cells(lngZeileFrei, 1)

set required range with named columns as below

.Range("A2:A" & lngZMax & ",C2:C" & lngZMax & ",D2:D" & lngZMax).Copy _
Destination:=Tabelle4.Cells(lngZeileFrei, 1)

For example, if source csv has 4 columns A B C D with 20 rows, where first row and second column B should be skipped out, then VBA function Range should look as

Range("A2:A20,C2:D20") or Range("A2:A20,C2:C20,D2:D20")

Upvotes: 1

Related Questions