0

I'm trying to build a macro which takes text generated in a worksheet & saves it to a text file. When run the user is prompted to save the text to a file using the Save As Dialog box. The problem is that if the user selects a location other than the default my documents folder, it does not actually save. If the macro is run a second time however before closing the workbook it works. I had to use On Error Resume Next to prevent an error box when the user does cancels the Save as box but if I comment this out the error when trying to save in a non-my docs location is error 53 file not found. Does anyone know whats going on here or how to fix this? Check my code below for simple illustration of error:

Sub saveFileAs()
Dim textFile, printText As String

textFile = "filename.txt"  'won't work without declaring something for textFile

printText = ActiveSheet.Range("A1").Value

Open textFile For Output As #1
    Print #1, printText

Close #1


On Error Resume Next

Name textFile As Application.GetSaveAsFilename(, filefilter:="TXT (*.txt),*.txt")

End Sub
Chrismas007
  • 6,085
  • 4
  • 24
  • 47
Bruce
  • 1
  • 1

1 Answers1

0

Doing this worked for me, no errors.

Sub testing()
Dim TextFile As String, PrintText As String
Dim SaveName As Variant

TextFile = "filename.txt"
PrintText = ActiveSheet.Range("A1").Value

Open TextFile For Output As #1
  Print #1, PrintText

Close #1

SaveName = Application.GetSaveAsFilename(, filefilter:="TXT (*.txt),*.txt")
If SaveName <> False Then Name TextFile As SaveName

End Sub
Rdster
  • 1,846
  • 1
  • 16
  • 30
  • This works if you choose the desktop or documents as the save destination but trying to save to any other folder results in a 'file not found error' at the 'Name TextFile As SaveName' instruction. Any idea why this happens? – Bruce Jan 19 '17 at 21:14