Xls export to txt

[marreco]

Hi
Dear I have the following routine that exports to txt

[COLOR=#000000][FONT=Verdana]Sub Exportar() [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]    Application.DisplayAlerts = False [/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]    template_file = ActiveWorkbook.FullName [/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]    fileSaveName = Application.GetSaveAsFilename( _ [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]                   InitialFileName:="C:\username" + _ [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]                                    VBA.Strings.Format(Now, "mmddyyyy") + ".txt", _ [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]                   fileFilter:="Text Files (*.txt), *.txt") [/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]    If fileSaveName = False Then [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]        Exit Sub [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]    End If [/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]    'cria uma cópia da pasta de trabalho atual da planilha atual [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]    Dim newBook As Workbook [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]    Dim plan As Worksheet [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]    Set newBook = Workbooks.Add [/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]    ThisWorkbook.ActiveSheet.Copy Before:=newBook.Sheets(1) [/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]    'exclui as demais planilhas [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]    For Each plan In newBook.Sheets [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]        If plan.Name <> ActiveSheet.Name Then [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]            newBook.Worksheets(plan.Index).Delete [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]        End If [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]    Next [/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]    newBook.SaveAs Filename:= _ [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]                          fileSaveName, FileFormat:=xlTextWindows, _ [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]                          CreateBackup:=False [/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]    'fecha a pasta de trabalho gerada [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]    newBook.Close SaveChanges:=True [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]    Set newBook = Nothing [/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]    MsgBox "O arquivo foi exportado com sucesso! ", vbInformation, "Exportar arquivos" [/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]End Sub[/FONT][/COLOR]

The doubt is as follows, in order that the code is the same txt all exports to spreadsheet rows or 65536 rows! How can I adjust to the routine that exports only rows filled?


I appreciate the help

 

[Legacy 143009]

Hi!

Try to fit this:

ActiveSheet.SpecialCells(xlCellTypeConstants, 23).Select

somewhere in your code.

 

[marreco]

Hi.

I'm try

[COLOR=#000000][FONT=Verdana]Sub Exportar()[/FONT][/COLOR][COLOR=#000000][FONT=Verdana]    Application.DisplayAlerts = False


    template_file = ActiveWorkbook.FullName


    fileSaveName = Application.GetSaveAsFilename( _
                   InitialFileName:="C:\username" + _
                                    VBA.Strings.Format(Now, "mmddyyyy") + ".txt", _
                   fileFilter:="Text Files (*.txt), *.txt")


    If fileSaveName = False Then
        Exit Sub
    End If


    'cria uma cópia da pasta de trabalho atual da planilha atual
    Dim newBook As Workbook
    Dim plan As Worksheet
    Set newBook = Workbooks.Add


    ThisWorkbook.ActiveSheet.Copy Before:=newBook.Sheets(1)
    ActiveSheet.SpecialCells(xlCellTypeConstants, 23).Select '<--Linha acrescentada
    'exclui as demais planilhas
    For Each plan In newBook.Sheets
        If plan.Name <> ActiveSheet.Name Then
            newBook.Worksheets(plan.Index).Delete
        End If
    Next


    newBook.SaveAs Filename:= _
                          fileSaveName, FileFormat:=xlTextWindows, _
                          CreateBackup:=False


    'fecha a pasta de trabalho gerada
    newBook.Close SaveChanges:=True
    Set newBook = Nothing


    MsgBox "O arquivo foi exportado com sucesso! ", vbInformation, "Exportar arquivos"


End Sub


[/FONT][/COLOR]

Displayed the following message: Object does not support this property or method

Thanks for the help!

 

[Legacy 143009]

May be you should give a cell range like:

ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23).Select

Else, I don't know

 

[marreco]

Hi.
generated the following error.

Run-time error '1004 ':
No cells were found.

 

[marreco]

Hi

Any idea?

Thank you!!

 

[marreco]

Hi

Any idea?

Thank you!!

 

[Legacy 143009]

Ahh! I didn't get your code really well but it first creates a new sheet and then saves. It is maybe because your new sheet is empty. I have no more idea, sorry...

 

[marreco]

hi, Flashbond, thanks for the willingness to help.
let's see if anyone else has any idea how to help me.


Thank you!

 

[marreco]

Hi

Any idea?

Thank you!!