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]

disregard, my error.

 

[Domenic]

Thank you! , 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?

Did you change the path and filename accordingly? If so, did you make sure it was a valid path to your folder and file?

 

[Domenic]

The following code has been amended so that the user gets prompted for a filename...

Option Explicit

Sub Export_to_UTF_16_LE()

    Dim saveas_filename As Variant
    saveas_filename = Application.GetSaveAsFilename(FileFilter:="Unicode Text (*.txt), *.txt", Title:="SaveAs")
    If saveas_filename = False Then
        Exit Sub
    End If

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

    ActiveSheet.Copy
    
    Rows(1).Delete
    
    Columns("C:G").Delete
    
    ActiveWorkbook.SaveAs Filename:=saveas_filename, 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

Does this help?

 

[ogassav]

Hi Domenic

the sheet is exported now, however there's one bug which appears in every TXT file.

Here's the link to XLSM file with your macro already integrated and with test input data in the cells. As you see column "L" contains CONCATENATE formula which gathers all the data from columns "C-G". This is actually the reason why these columns should not be exported to TXT, as they are temporary ones.

Column "L" should be exported, however there's a #REF! text in the resulting TXT file, instead of actual data from this column. Here's the screenshot:

What could have caused this strange bug? ?

 

[Domenic]

I haven't looked at your sample workbook, but it looks like we simply need to convert formulas into values before exporting the worksheet...

Option Explicit

Sub Export_to_UTF_16_LE()

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

    ActiveSheet.Copy
    
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    
    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]

Hi @Domenic

thank you so much, now it works exactly as it should - appreciate your help! The only question that is left is the TXT file size: it's 10.8kb vs. 5.65kb compared to the one created using code from @MoshiM (same excel sheet was used in export of course!).

I checked why's that and i guess the reason is that your code exports all rows, including empty ones. For this reason the resulting TXT contains all the necessary data on the top (with columns divided by tabs) and an empty space below this data. This empty space consists of the same number of columns (of course) divided by tabs, but there's no data there, as if someone just pressed "Space" button when filled in those cells in Excel.

Is there a way to ignore empty rows in export?

 

[ogassav]

UPD:

What came to my mind is that the problem of export of empty cells could have been caused by presence of formulas in certain rows?

I may be mistaken, of course, but VBA code in its current version possibly can't distinguish formula from "real" data, and as a result - exports it too.

Just an assumption...

 

[Domenic]

Actually, when I tested both methods, the 2 files had the same size. So I'm not sure why it's different for you. I did notice, though, in going through this thread again, in my last post I posted the wrong version of my code. It excluded the deletion of the first row (and the prompting of the user to save the file). Maybe you caught it and already made the amendments. Just in case, the code should be as follows. Does this help?

Option Explicit

Sub Export_to_UTF_16_LE()

    Dim saveas_filename As Variant
    saveas_filename = Application.GetSaveAsFilename(FileFilter:="Unicode Text (*.txt), *.txt", Title:="SaveAs")
    If saveas_filename = False Then
        Exit Sub
    End If

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

    ActiveSheet.Copy
    
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    
    Rows(1).Delete
    
    Columns("C:G").Delete
    
    ActiveWorkbook.SaveAs Filename:=saveas_filename, 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]

Hi Domenic, sure i noticed that and my code contained those lines (as i've started to understand a little which line means what).
Could you please try your code with Excel file i've shared a couple of posts above? If it will work for you possibly smth is wrong on my side.
Just please check that only lines with data are exported, nothing more. And make sure to fill in Tag1 Tag2 columns (those we skip in VBA export), as exactly they are used in CONCATENATE formula in the very last column.