VBA: how to skip a RANGE of columns when exporting to .TXT?

[ogassav]

Dear community,

I have an Excel table to be exported to TXT in Unicode encoding with data separated by tabs (these 2 criteria are important) and i want to skip the range of columns: from C to G. Everything that is before C and after G should be exported.

Which VBA code will be able to do that? It will be good if i'm able to choose the destination folder where the resulting TXT file will be saved.

Thank you!

 

[JLGWhiz]

Wouldn't it just be simpler to create a sheet with a table that excludes those columns and then export that table?

 

[ogassav]

Well, technically this is possible, but the idea is to avoid extra editing due to human factor risks.

 

[Peter_SSs]

but the idea is to avoid extra editing due to human factor risks

Your question says that you want this done with vba so wouldn't that avoid any human error?

 

[MoshiM]

Try this.

Sub Text_Exclude()
'_________________________________________________________________________________________________________________________
'USER DECIDES FILE PATH
'Text File created from the Active Sheet
'Columns you don't want to add to the text file should be added to the line Exclusion=split(,,,,)
'______________________________________________________________________________________________________________________
Dim FSTR As String, H As Long, ColumnN As Long, AR As Variant, Exclusion As Variant, _
Text_Path As String, Delimiter As String, Column_Offset As Long, T As Long, FS As Object, TXT As Object

Text_Path = Application.GetSaveAsFilename 'USER DECIDES FILE PATH

If Text_Path = vbNullString Or Text_Path = "False" Then
    MsgBox ("No input detected. Ending script")
    End
End If

Set FS = CreateObject("Scripting.FileSystemObject")
Set TXT = FS.CreateTextFile(Text_Path, True, True) 'Unicode text file and will overwrite

Delimiter = Chr(9) 'TAB used to delimit array elements
Exclusion = Split("C,D,E,F,G", ",") 'column letters you don't want to parse

With ActiveSheet.UsedRange 'Determine if excluded columns were offset to determine array pasring
    If .Column = 1 Then
        AR = .Value2
    Else
        AR = .Value2
        Column_Offset = .Column - 1 'default value is 0
    End If
End With

For T = LBound(Exclusion) To UBound(Exclusion) 'convert column letters to column numbers

    Exclusion(T) = ActiveSheet.Range(Exclusion(T) & 1).Column - Column_Offset
   
Next T

For H = 1 To UBound(AR, 1)
   
    For ColumnN = 1 To UBound(AR, 2)
   
        If IsError(Application.Match(CStr(ColumnN), Exclusion, 0)) Then 'if column number doesn't exist in the exclusion array
           
            If FSTR = vbNullString Then
                FSTR = AR(H, ColumnN)
            Else
                FSTR = FSTR & Delimiter & AR(H, ColumnN)
            End If
           
        End If
       
    Next ColumnN
   
    TXT.WriteLine (FSTR)
   
    FSTR = vbNullString
   
Next H

TXT.Close

End Sub

 

[Domenic]

You haven't specified which unicode encoding type you want. If UTF-16 LE is acceptable, here's another approach. The following code assumes that the sheet containing the data is the active sheet. First, it creates a temporary copy of the worksheet, then it deletes Columns C through G, inclusive, then it saves it as UTF-16 LE, and then it closes the workbook without saving it. You'll need to change the path and filename accordingly. Also, if a file with the same name already exists, the file will be overwritten. Note that all of this happens in the background, so you won't see any of it happening. Here's the code...

Option Explicit

Sub Export_to_UTF_16_LE()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    ActiveSheet.Copy
    
    Columns("C:G").Delete
    
    ActiveWorkbook.SaveAs Filename:="C:\Users\Domenic\Desktop\sample.txt", FileFormat:=xlUnicodeText
    
    ActiveWorkbook.Close SaveChanges:=False
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
    MsgBox "Your sheet has been exported!", vbExclamation, "Sheet Exported"

End Sub

Hope this helps!

 

[ogassav]

MoshiM

Thank you so much! Everything works just fine. There is only 1 thing that i forgot - this code should also ingore line 1 (not column!) which is a header with column names. Could you please kindly help me with that? (I'm a total noob in macros, sorry )

Domenic

Thank you for your help too! As for the destination file format i need exactly what is called Unicode Text (*.txt) in the list of formats available when the file is saved. Not sure whether this is UTF-16 LE or not, but this file format is just what i need.

 

[MoshiM]

MoshiM

Thank you so much! Everything works just fine. There is only 1 thing that i forgot - this code should also ingore line 1 (not column!) which is a header with column names. Could you please kindly help me with that? (I'm a total noob in macros, sorry )

change

For H = 1 To UBound(AR, 1)

to

For H = 2 To UBound(AR, 1)

 

[Domenic]

Yes, the code will save it in that exact file format. Here's the code, which has been amended to include the deletion of the first row...

Option Explicit

Sub Export_to_UTF_16_LE()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    ActiveSheet.Copy
    
    Rows(1).Delete
    
    Columns("C:G").Delete
    
    ActiveWorkbook.SaveAs Filename:="C:\Users\Domenic\Desktop\sample.txt", FileFormat:=xlUnicodeText
    
    ActiveWorkbook.Close SaveChanges:=False
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
    MsgBox "Your sheet has been exported!", vbExclamation, "Sheet Exported"

End Sub

 

[ogassav]

MoshiM

Thank you million of times, it works just perfectly fine!

Domenic

Thank you! However, when macros is run i get the following message: Run-time error '1004': Method 'Save As' of object '_Workbook' failed. What do you think is wrong?