PDA

View Full Version : Implementing Save As function in VBA code



eric7099
05-05-2017, 11:17 AM
I have a code that prints excel cells into a txt. The function works fine, but the filename and location have to be specified within the code. How can I use the getsaveasfilename function to save the txt to a user-specified file location and name?

Current print code:


Private Sub CommandButton1_Click()

Dim iLastRow As Long
Dim iLastCol As Long

iLastRow = Range("A" & Rows.Count).End(xlUp).Row
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Open "C:\Users\derickson\Documents\2200.txt" For Output As #1
For i = 2 To iLastRow
For j = 1 To iLastCol
If j <> iLastCol Then 'keep writing to same line
Print #1, Cells(i, j),
Else 'end the line
Print #1, Cells(i, j)
End If
Next j
Next i
'MsgBox "Failed to transfer " & iFail & " file(s).", iFail & " Transfer(s) Failed"
Close #1
'comment the shell command out if you don't want to open the file when the macro ends
'Shell "notepad.exe ""C:\Users\Ryan\Documents\wellsr\FundPrices.txt", vbNormalFocus
MsgBox ("done")

End Sub

Thanks!

mdmackillop
05-05-2017, 12:19 PM
Function FileSaveName() As String
FileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
End Function


and change this line



Open FileSaveName For Output As #1