Import multiple delimited files

Antman404

New Member
Joined
May 26, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
Hi Antman404, welcome to this forum.
See if the code below works for you.
VBA Code:
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

New Member
Joined
May 26, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
You are my hero. Thank you very much. It works perfect.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
Glad to help :)
 

Watch MrExcel Video

Forum statistics

Threads
1,112,784
Messages
5,542,500
Members
410,559
Latest member
jordansmith6532
Top