Mandy_
asked on
VBA Excel - import csv and sort to different rows
hi expert,
the VBA code below import a csv into the active worksheet with delimiter ";" so far so good.
I need your help to implement 2-3 features
1. The name of the existing worksheet is always "IMPORT" not should use the active worksheet function
2. The CSV data should sort from the column J "Action" which has 3 Values (new,delete,we)
insert "new" from A2 until A150
insert "delete" from A151 until A185
insert "we" from A186 until A220
3. Not so important : It should clear the worksheet "Import" before from row A2. A1 has the header
Thanks in advance for your answers.
Best Regards
Mandy
the VBA code below import a csv into the active worksheet with delimiter ";" so far so good.
I need your help to implement 2-3 features
1. The name of the existing worksheet is always "IMPORT" not should use the active worksheet function
2. The CSV data should sort from the column J "Action" which has 3 Values (new,delete,we)
insert "new" from A2 until A150
insert "delete" from A151 until A185
insert "we" from A186 until A220
3. Not so important : It should clear the worksheet "Import" before from row A2. A1 has the header
Thanks in advance for your answers.
Best Regards
Mandy
Sub Datei_Importieren()
Dim strFileName As String, arrDaten, arrTmp, lngR As Long, lngLast As Long
Const cstrDelim As String = ";" 'Delimiter
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Choose Filename"
.InitialFileName = "c:\temp\*.csv" 'Pfad anpassen
If .Show = -1 Then
strFileName = .SelectedItems(1)
End If
End With
If strFileName <> "" Then
Application.ScreenUpdating = False
Open strFileName For Input As #1
arrDaten = Split(Input(LOF(1), 1), vbCrLf)
Close #1
For lngR = 1 To UBound(arrDaten)
arrTmp = Split(arrDaten(lngR), cstrDelim)
If UBound(arrTmp) > -1 Then
With ActiveSheet
lngLast = .Cells(Rows.Count, 1).End(xlUp).Row + 1
lngLast = Application.Max(lngLast, 10)
.Cells(lngLast, 1).Resize(, UBound(arrTmp) + 1) _
= Application.Transpose(Application.Transpose(arrTmp))
End With
End If
Next lngR
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi kimputer,
Thank you so much for help. The CSV you can find as attachment.
There are 3 datasets included. NEW, DELETE, WE at the column Aktion.
At import process they should be sort
all Aktion, New from Row 2
all Aktion, delete from row 151
all Aktion, We from row 186
Needs to be insert from row 2, 151 and 186
the number of datasets are variable. Sometimes "New" from 2 to 20 or Delete from 151 to 160 only.
The data will process in other worksheet depends on the data in different rows.
ee-example2.csv
Thank you so much for help. The CSV you can find as attachment.
There are 3 datasets included. NEW, DELETE, WE at the column Aktion.
At import process they should be sort
all Aktion, New from Row 2
all Aktion, delete from row 151
all Aktion, We from row 186
Needs to be insert from row 2, 151 and 186
the number of datasets are variable. Sometimes "New" from 2 to 20 or Delete from 151 to 160 only.
The data will process in other worksheet depends on the data in different rows.
ee-example2.csv
ASKER
appreciate fpr your assistence. For the missing part i will open a new question
Rows with specific action values (New, Delete, WE) should be inserted at different locations, e.g. all "Delete" occupy rows 151 to 185.