Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1164
  • Last Modified:

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

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

Open in new window

0
Mandy_
Asked:
Mandy_
  • 2
1 Solution
 
KimputerCommented:
Updated code for point 1 and 3 (please make sure you have a sheet with the name "IMPORT", as I didn't insert error checking code for now)

Sub Datei_Importieren()
  Dim strFileName As String, arrDaten, arrTmp, lngR As Long, lngLast As Long
  Const cstrDelim As String = ";" 'Delimiter
  
  Dim importsheet As Worksheet
  Set importsheet = ActiveWorkbook.Sheets("IMPORT")
  
 
  Application.ScreenUpdating = False
  For i = importsheet.UsedRange.Rows.Count To 2 Step -1
    importsheet.Cells(i, 1).EntireRow.Delete
  Next
  Application.ScreenUpdating = True

 
  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 importsheet
          lngLast = .Cells(Rows.Count, 1).End(xlUp).Row + 1
          lngLast = Application.Max(lngLast, 2)
          .Cells(lngLast, 1).Resize(, UBound(arrTmp) + 1) _
            = Application.Transpose(Application.Transpose(arrTmp))
        End With
      End If
    Next lngR
  End If
  
End Sub

Open in new window


Point 2 is a bit of a mystery to me right now. Maybe you can send a sample csv file? And then you talk me through it step by step? It sounds a bit illogical that those numbers are always fixed that you gave earlier (2, 151, 186, 220).
0
 
QlemoC++ DeveloperCommented:
Re 2: From earlier communications:
Rows with specific action values (New, Delete, WE) should be inserted at different locations, e.g. all "Delete"  occupy rows 151 to 185.
0
 
Mandy_Author Commented:
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
0
 
Mandy_Author Commented:
appreciate fpr your assistence. For the missing part i will open a new question
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now