Saving Cell Data to a .txt file

[sassriverrat]

Good Morning,

I've got some ideas but none seem to be working quite the way I want them to.....

I'd like a string of code that, when run, will take the contents of cells A1, A2, and A3 and save them in a .txt file, with each set of cell data being a new line (so the info of cell A1 is the top line, A2 is the second line, etc).

Ideally, this code would look for a save location specified in cell A4 and if not found, would open a standard "save" window for the user to choose the save location.

Any help would be greatly appreciated.

Thanks!

 

[sassriverrat]

@Yongle

Ha-HA! You've done it. I wrote an "error code" that you had already incorporated....so I deleted my code!

One last piece- is there a way to remove the apostrophes that are inserted around each line of words in the .txt document?



To give you an idea- this is now going to allow me to tweak this so that a user can input data, run the macro and have it sent via email or satellite connection to a computer that automatically receives and catalogues the data if it's been correctly formatted.....we used to have a program that did all of this but two things happened: 1. I really wanted to integrate it into the workbook so we weren't typing the data twice and 2. The program that did it was being babied through windows upgrades...and when we left windows 7 for new windows 10 machines, the windows 98 program didn't survive.

 

[Yongle]

is there a way to remove the apostrophes that are inserted around each line of words in the .txt document?

replace

Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  , Cells(i, 1).Value

with

Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Cells(i, 1).Value

 

[sassriverrat]

Sub CreateTextFile()    Dim myFolder As String, myFile As String, i As Integer
'get file name
    myFile = Cells(5, 1)
    If myFile = "" Then
        MsgBox "Enter file name in A5"
        Exit Sub
    Else
        myFile = myFile & ".txt"
    End If
'get folder
    myFolder = Cells(4, 1)
    If myFolder = "" Or FolderExists(myFolder) = False Then myFolder = ChooseFolder
    If myFolder = "" Then
        MsgBox "Folder not selected"
        Exit Sub
    End If
    
    If Right(myFolder, 1) <> "\" Then myFolder = myFolder & "\"
'full file path & name
    myFile = myFolder & myFile
'write to text file
    Open myFile For Output As #1 
    For i = 1 To 3
        Print #1 , Cells(i, 1).Value
    Next
    Close #1 
    MsgBox myFile, vbOK, "SAVED TO .."
End Sub
Private Function FolderExists(FolderString) As Boolean
    On Error Resume Next
    FolderExists = False
    If Len(Dir(FolderString, vbDirectory)) > 0 Then FolderExists = True
End Function

Just to make sure I'm not missing anything, this is what I have and it seems to worked perfectly. If the workbook isn't selected, it says "folder not selected" (if, for example, I have a second workbook open at the same time). Did I miss something?

 

[Yongle]

Is the code in the workbook with values in A1:A5 or in another workbook ?
Which version of Excel are you using ?

 

[sassriverrat]

Is the code in the workbook with values in A1:A5 or in another workbook ?
Which version of Excel are you using ?

Yes it's trying to work with another workbook- just realized that. It's trying to work based on the last cell (in whatever teh last workbook was) that I selected. I guess this won't be a problem because ultimately, this will run based on pressing a button (object) on a sheet...so that takes care of that. ANd for added redundancy, I'll make it a private sub.

Agreed?

Didn't miss anything on my part?

Thanks!

 

[Yongle]

As you say, with a button on the sheet , then the correct workbook and sheet should always be active when you run the code
I cannot see anything that you have missed - but come back if you run into any problems