Saving Cell Data to a .txt file

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
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!
 
:LOL:@Yongle

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

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.
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
is there a way to remove the apostrophes that are inserted around each line of words in the .txt document?
replace
Code:
Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  , Cells(i, 1).Value
with
Code:
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Cells(i, 1).Value
 
Upvote 0
Rich (BB code):
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?
 
Upvote 0
Is the code in the workbook with values in A1:A5 or in another workbook ?
Which version of Excel are you using ?
 
Last edited:
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top