+ Reply to Thread
Results 1 to 2 of 2

Modify Code to Tab/Space Delimit (Instead of "|")

Hybrid View

  1. #1
    Registered User
    Join Date
    10-14-2011
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007
    Posts
    67

    Modify Code to Tab/Space Delimit (Instead of "|")

    Hello All -

    I was able to find this code online, which works perfect for grabbing .txt files from a folder location and producing .xlsx files into an output folder. However; I am looking to delimit the .txt files by TAB & SPACE (not "|"). Can someone show me what I'd need to do to modify this code?

    Any help is MUCH appreciated.

    Sub tgr()    
        Const txtFldrPath As String = "C:\TextFiles"      'Change to folder path containing text files
        Const xlsFldrPath As String = "C:\ExcelOutput"     'Change to folder path excel files will be saved to
        
        Dim CurrentFile As String: CurrentFile = Dir(txtFldrPath & "\" & "*.txt")
        Dim strLine() As String
        Dim LineIndex As Long
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        While CurrentFile <> vbNullString
            LineIndex = 0
            Close #1
            Open txtFldrPath & "\" & CurrentFile For Input As #1
            While Not EOF(1)
                LineIndex = LineIndex + 1
                ReDim Preserve strLine(1 To LineIndex)
                Line Input #1, strLine(LineIndex)
            Wend
            Close #1
            
            With ActiveSheet.Range("A1").Resize(LineIndex, 1)
                .Value = WorksheetFunction.Transpose(strLine)
                .TextToColumns Other:=True, OtherChar:="|"
            End With
            
            ActiveSheet.UsedRange.EntireColumn.AutoFit
            ActiveSheet.Copy
            ActiveWorkbook.SaveAs xlsFldrPath & "\" & Replace(CurrentFile, ".txt", ".xls"), xlNormal
            ActiveWorkbook.Close False
            ActiveSheet.UsedRange.ClearContents
            
            CurrentFile = Dir
        Wend
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True    
    End Sub

  2. #2
    Registered User
    Join Date
    10-14-2011
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Modify Code to Tab/Space Delimit (Instead of "|")

    .TextToColumns DataType:=xlDelimited, Tab:=True, Space:=True, Other:=False
    replaces

    .TextToColumns Other:=True, OtherChar:="|"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Best way to modify this line of Code using ("WScript.Shell").
    By vnzerem in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-30-2014, 04:58 PM
  2. =IF(G2=H2,"Match","Review") Need to modify to compensate for empy cells
    By Aeroice in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2012, 08:37 PM
  3. How could I replace an "=", "-" and "+" sign with space
    By Abhushan86 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 05-07-2012, 04:32 AM
  4. Modify code to email a selected range or ("print area") in lieu of ActiveSheet
    By robertse in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-15-2010, 05:15 PM
  5. Replies: 4
    Last Post: 06-13-2006, 07:25 PM

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