Import multiple delimited files

[Antman404]

Hello to everyone
and thank you in advance for taking time to read my post
i am not very skilled in vba, so i would like to ask you for your help in case someone can afford time for my problem.
in a folder where my workbook is located are comma delimited files with continous numbers (e.g. abc1_0001.rnd, abc1_0002.rnd,...) and one with main information (main.rnh)

[rnh/rnd files work like csv files but are specific created from my program]

my problems are the following:

• the comma delimited files are not seperated at the comma, everything goes into column A
  i want them seperated into their own columns
• the first 4 rows of the delimited files should be skipped. otherwise the "subheadings" will repeat after every imported file

i found (and already slightly edited) the code below. but now i am stuck.
i very much appreciate your help

Sub ImportAllFiles()
    Dim Sht  As Worksheet
    Dim Wb As Workbook
    Dim StrPath As String
    Dim FileDialog As FileDialog
    Dim File As String
    On Error GoTo ErrHandler
    StrPath = ThisWorkbook.Path
    Set Sht = ThisWorkbook.ActiveSheet
    If MsgBox("Clear sheet before importing?", vbYesNo, "Clear everything?") = vbYes Then Sht.UsedRange.Clear
    Application.ScreenUpdating = False
    File = Dir(StrPath & "\" & "*.rnh")
    Do While File <> ""
        Set Wb = Workbooks.Open(StrPath & "\" & File)
        Columns(1).Insert xlShiftToRight
        ActiveSheet.UsedRange.Copy Sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
        Wb.Close False
        File = Dir
    Loop
     File = Dir(StrPath & "\" & "*.rnd")
    Do While File <> ""
        Set Wb = Workbooks.Open(StrPath & "\" & File)
        Columns(1).Insert xlShiftToRight
        ActiveSheet.UsedRange.Copy Sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
        Wb.Close False
        File = Dir
    Loop
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox "no files csv", , "Error"
End Sub

 

[GWteB]

Hi Antman404, welcome to this forum.
See if the code below works for you.

Sub ImportAllFiles()
    Dim Sht         As Worksheet
    Dim Wb          As Workbook
    Dim StrPath     As String
'    Dim FileDialog  As FileDialog
    Dim File        As String
    
    Dim raDest      As Range
    
    On Error GoTo ErrHandler
    StrPath = ThisWorkbook.Path
    Set Sht = ThisWorkbook.ActiveSheet
    If MsgBox("Clear sheet before importing?", vbYesNo, "Clear everything?") = vbYes Then Sht.UsedRange.Clear
    
    Application.ScreenUpdating = False
    
    File = Dir(StrPath & "\" & "*.rnh")
    Do While File <> ""
        Set raDest = Sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
        Call ImportCSV(StrPath & "\" & File, raDest)
        File = Dir
    Loop
     
    File = Dir(StrPath & "\" & "*.rnd")
    Do While File <> ""
        Set raDest = Sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
        Call ImportCSV(StrPath & "\" & File, raDest)
        File = Dir
    Loop
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox "no files csv", , "Error"
End Sub


Sub ImportCSV(ByVal argCSV_FullName As String, ByRef argDest As Range)
    With argDest.Parent.QueryTables.Add(Connection:="TEXT;" & argCSV_FullName, Destination:=argDest)
        .TextFileStartRow = 5
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[Antman404]

You are my hero. Thank you very much. It works perfect.

 

[GWteB]

Glad to help