PDA

View Full Version : How export specific range to txt file



marreco
01-21-2015, 03:48 AM
Hi.

I need use this code, but export specific range("A1:C100")


Sub Export_to_txt_file() Application.DisplayAlerts = False


template_file = ActiveWorkbook.FullName


fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:="C:\" + "saida.txt", _
fileFilter:="Text Files (*.txt), *.txt")


If fileSaveName = False Then
Exit Sub
End If


Dim newBook As Workbook
Dim plan As Worksheet
Set newBook = Workbooks.Add
'I need only export range("A1:C100"), do not full sheet, how do it?
ThisWorkbook.ActiveSheet.Copy Before:=newBook.Sheets(1)


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

newBook.Close SaveChanges:=True
Set newBook = Nothing


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


End Sub

Thank you!!

apo
01-21-2015, 07:26 AM
Hi..


I need use this code

Although, I am sure a more efficient alternative will exist other than what i have posted .. here's one way..



Private Sub CommandButton1_Click()
Dim z, x, i As Long, ii As Long
x = [A1:c100]
ReDim z(1 To UBound(x))
For i = LBound(x) To UBound(x)
For ii = LBound(x, 2) To UBound(x, 2)
z(i) = z(i) & x(i, ii)
Next ii
Next i
CreateObject("scripting.filesystemobject").CreateTextFile("C:\Saida.txt").write Join(z, vbCrLf)
End Sub

marreco
01-21-2015, 08:23 AM
Hi apo, imagine that i want and need choose place where i want save this file, how i do it or what line i have change in your code?

apo
01-21-2015, 12:56 PM
A some code to Select a Folder and then use the variable that the selection is stored as instead of a static Folderpath...



Private Sub CommandButton1_Click()
Dim z, x, i As Long, ii As Long, fldr As FileDialog, SelFold As String


Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
If .Show <> -1 Then Exit Sub
SelFold = .SelectedItems(1)
End With

x = [A1:c100]
ReDim z(1 To UBound(x))
For i = LBound(x) To UBound(x)
For ii = LBound(x, 2) To UBound(x, 2)
z(i) = z(i) & x(i, ii)
Next ii
Next i
CreateObject("scripting.filesystemobject").CreateTextFile(SelFold & "\Saida.txt").write Join(z, vbCrLf)
End Sub