Results 1 to 10 of 10

Import multiple text files into different sheets + delimiter

Threaded View

  1. #1
    Registered User
    Join Date
    08-23-2012
    Location
    Canada
    MS-Off Ver
    Excel 2017
    Posts
    25

    Import multiple text files into different sheets + delimiter

    Hey guys, first time posting here, absolutely no experience programming macros, but I did do some vba in high school about 5 years ago, so extremely rusty, let me explain my question.

    I have a text file for every month of the year for 4 years and I would like to import them to a single workbook but different sheets. I actually already found a solution to this, a section of code I found (from this forum I think?) I don't remember where I found it, so if the OP of this code could come forward and claim this, much appreciated.

    Also, I did hit the sticky but could not find the solution to my first problem, delimiters.
    I edited this code so it would ask for the path of the txt files as well as changed it from csv files to txt files.

    Sub Example12()
    Dim MyPath As String
    Dim FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long
    Dim Fnum As Long
    Dim mybook As Workbook
    Dim basebook As Workbook
    
    'Fill in the path\folder where the files are
    'on your machine
    MyPath = InputBox("Enter", "I Rock")
    
    'Add a slash at the end if the user forget it
    If Right(MyPath, 1) <> "\" Then
    MyPath = MyPath & "\"
    End If
    
    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.txt")
    If FilesInPath = "" Then
    MsgBox "No files found"
    Exit Sub
    End If
    
    On Error GoTo CleanUp
    
    Application.ScreenUpdating = False
    Set basebook = ThisWorkbook
    
    'Fill the array(myFiles)with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
    Fnum = Fnum + 1
    ReDim Preserve MyFiles(1 To Fnum)
    MyFiles(Fnum) = FilesInPath
    FilesInPath = Dir()
    Loop
    
    'Loop through all files in the array(myFiles)
    If Fnum > 0 Then
    For Fnum = LBound(MyFiles) To UBound(MyFiles)
    Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
    mybook.Worksheets(1).Copy after:= _
    basebook.Sheets(basebook.Sheets.Count)
    
    On Error Resume Next
    ActiveSheet.Name = mybook.Name
    On Error GoTo 0
    
    ' You can use this if you want to copy only the values
    ' With ActiveSheet.UsedRange
    ' .Value = .Value
    ' End With
    
    mybook.Close savechanges:=False
    Next Fnum
    End If
    CleanUp:
    Application.ScreenUpdating = True
    End Sub
    Problem is, the text files have semicolon delimiters and I know that you set delimiter options in the "Workbooks.Open" command but I don't know the syntax.
    Also, I need 2 other things, first is a must have, but the second is not needed, it would just be nice.

    1) I need to add an extra column to each worksheet that adds certain cells together, eg B2 + C2 + E2, and implement this in the same code as the above.
    2) Copy these extra columns from each worksheet into a different workbook, specifying the start and end point for each set of 12 worksheets (Eg 2008 Jan - Dec in the 2008 worksheet, then 2009 Jan - Dec in the 2009 worksheet etc etc)

    Thanks a lot!! (Oh does Tapatalk work for this forum by the way?)
    Last edited by zijin_cheng; 08-23-2012 at 08:26 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1