Help with VBA - Excel to TXT

Question

I have a spreadsheet that I use in my store that saves my orders in txt that in another spreadsheet I analyze all the data, but whenever I ask a new request and click to generate the TXT it opens the Save As dialog. Is there any way to skip this step and save it without opening the save as, specifying where to save? this macro I took on the internet and adapted for my use, but as I am layman this part I could not change. follows the macro:

Sub ExportRangetoFile()
'Update 20130913
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Range("C6:Q20")
Set WorkRng = Range("C6:Q20")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

already this specified the cells that he exports to txt, everything works perfectly, it would only automate even more.

from now on thank

Answer 1

Use this: saveFile = Application.Getsavefilename(fileFilter:="Text Files (*. txt). I just pulled out the "As" "Getsaveas" and left Getsave, test there and see if it works!

Answer 2

Not to display the save dialog, just remove the line saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt") for the method Application.GetSaveAsFilename is who displays the dialogue.

To specify the path to save the file assign the path to the variable saveFile. In this example I used as a way C:\Pedidos.txt.

'Declare uma variável global para servir de incremento para os arquivos
Public arquivo As Integer

Sub ExportRangetoFile()
'Update 20130913
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Range("C6:Q20")
Set WorkRng = Range("C6:Q20")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.add
WorkRng.Copy
wb.Worksheets(1).Paste

'Comente a próxima linha para não mostrar o dialogo Salvar como
'saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")

'Coloque dentro das aspas o caminho do arquivo onde quer salvar o arquivo
'Nesse exemplo usei C:\Pedidos.txt incrementado pela variável arquivo
Let saveFile = "C:\Pedidos" & arquivo & ".txt"

wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close

'incrementa o contador de arquivos para o próximo uso
arquivo = arquivo + 1

Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Answer 3

Got it, got it

nomearquivo = Range("T6")
Let saveFile = "C:\Users\André Willemann\Desktop\Relatorios Brecho TUX\" & nomearquivo & ""

now it saves with the name of the cell T6 which is the order number