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!

 

[Yongle]

You will have a few questions after testing, but try this as your FIRST test

Create a new workbook
Place code below in a module
Open the workbook containing the values and make the correct sheet active
Run the code
- code prompts you for a folder
- select a folder and click OK

Sub CreateTextFile()
    Dim myFolder As String, myFile As String, i As Integer
    myFile = "XXX.csv"
    myFolder = ChooseFolder
    If myFolder = "" Then Exit Sub
    myFile = myFolder & "\" & myFile
    Open myFile For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    For i = 1 To 3
        Write [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , Cells(i, 1).Value & vbCr
    Next
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    MsgBox myFile, vbOK, "SAVED TO .."
End Sub

Private Function ChooseFolder() As String
    Dim x As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo TheEnd
        x = .SelectedItems(1)
    End With
TheEnd:
    ChooseFolder = x
End Function

AFTER TESTING I need some more info

... about contents of A4
- what does it contain ? the full path ? a subfolder name ?

... what is the name of the text file each time ? does it vary ? should user provide ? etc

 

[sassriverrat]

You will have a few questions after testing, but try this as your FIRST test

Create a new workbook
Place code below in a module
Open the workbook containing the values and make the correct sheet active
Run the code
- code prompts you for a folder
- select a folder and click OK

Sub CreateTextFile()
    Dim myFolder As String, myFile As String, i As Integer
    myFile = "XXX.csv"
    myFolder = ChooseFolder
    If myFolder = "" Then Exit Sub
    myFile = myFolder & "\" & myFile
    Open myFile For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    For i = 1 To 3
        Write [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , Cells(i, 1).Value & vbCr
    Next
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    MsgBox myFile, vbOK, "SAVED TO .."
End Sub

Private Function ChooseFolder() As String
    Dim x As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo TheEnd
        x = .SelectedItems(1)
    End With
TheEnd:
    ChooseFolder = x
End Function

AFTER TESTING I need some more info

... about contents of A4
- what does it contain ? the full path ? a subfolder name ?

... what is the name of the text file each time ? does it vary ? should user provide ? etc

Hmmm...to answer your questions:

A4 will contain a full address. (saving to a MicroSD card so the location should be fairly reliable.

Name of the text file is specified in cell A5 (so user specified). That way I can come back and clean some of this up later.

As for the test- That worked pretty well- better than what I was trying to do....

I can easily enough change the "name" but I need this to specifically be a .txt document, like literally saving something that came out of notepad (on windows).

Ideally, this would save without a prompt if the address is correctly detected, otherwise if it opened a window that was already in the save "area" (i.e. let's say F: drive for example), that would be nice too.

Thanks

 

[Yongle]

I can easily enough change the "name" but I need this to specifically be a .txt document

The Function remains the same
Delete the original sub and replace with this (there are 2 changes)

Sub CreateTextFile()
    Dim myFolder As String, myFile As String, i As Integer
    myFile = "XXX.txt"
    myFolder = ChooseFolder
    If myFolder = "" Then Exit Sub
    myFile = myFolder & "\" & myFile
    Open myFile For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    For i = 1 To 3
        Write [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , Cells(i, 1).Value
    Next
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    MsgBox myFile, vbOK, "SAVED TO .."
End Sub

Does this now generate the text file required ?

 

[Yongle]

A4 will contain a full address. (saving to a MicroSD card so the location should be fairly reliable.

- OK

Name of the text file is specified in cell A5 (so user specified). That way I can come back and clean some of this up later

- is user entering this manually ?
- what should happen if file with specified name already exists in folder ?

Ideally, this would save without a prompt if the address is correctly detected, otherwise if it opened a window that was already in the save "area" (i.e. let's say F: drive for example), that would be nice too.

- OK

 

[sassriverrat]

The user will enter the name manually, yes. So if it is to be called “Hi.txt”, the user types in ”Hi”.

If the file exists, then running the code will overwrite the existing file (which I know, is dangerous).

Note:new code does generate desired text file. Thank you

 

[Yongle]

Replace sub with this

Sub CreateTextFile()
    Dim myFolder As String, myFile As String, i As Integer
[COLOR=#006400]'get file name[/COLOR]
    myFile = Cells(5, 1)
    If myFile = "" Then
        MsgBox "Enter file name in A5"
        Exit Sub
    Else
        myFile = myFile & ".txt"
    End If
[COLOR=#006400]'get folder[/COLOR]
    myFolder = Cells(4, 1)
    If myFolder = "" Then myFolder = ChooseFolder
    If myFolder = "" Then
        MsgBox "Folder not selected"
        Exit Sub
    End If
    If Right(myFolder, 1) <> "\" Then myFolder = myFolder & "\"
[COLOR=#006400]'full file path & name[/COLOR]
    myFile = myFolder & myFile
[COLOR=#006400]'write to text file[/COLOR]
    Open myFile For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    For i = 1 To 3
        Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Cells(i, 1).Value
    Next
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    MsgBox myFile, vbOK, "SAVED TO .."
End Sub

otherwise if it opened a window that was already in the save "area" (i.e. let's say F: drive for example), that would be nice too

And amend this line in the function

        .InitialFileName = Application.DefaultFilePath

To

        .InitialFileName = "F:\"

Comments
After testing amended code above for required functionality ... we must next consider weak points in the code
1. The user may inadvertently enter illegal characters into the file name in A5
2. The folder in A4 may not exist (yes I know it should exist but things change)

Error handling needs to be included next

 

[sassriverrat]

Comments
After testing amended code above for required functionality ... we must next consider weak points in the code
1. The user may inadvertently enter illegal characters into the file name in A5
2. The folder in A4 may not exist (yes I know it should exist but things change)

Error handling needs to be included next[/QUOTE]

Points:
1. This makes sense. This will be a regulated cell- administrator will "set" file name once and cell will be locked after that. So if "Hi" is put in once, fill should always be saved as "Hi.txt" without average user's ability to change (will require an admin to change).

2. The folder may be an issue. I'm trying to think how I want to address this with error coding. I think I'm going to write a piece of error coding that essentially just ends the code and tells the user the folder was not detected. Adding onto that, if the error exists, I'll plan to use your original code to initiate a "save" function that will bring up a traditional "save" dialog box so the user can manually choose to save the file (if he or she does not fix the SD card save location first).

 

[Yongle]

Deleted - will repost shortly

 

[Yongle]

The folder may be an issue

One way...


Add this function to the same module

Private Function FolderExists(FolderString) As Boolean
    On Error Resume Next
    FolderExists = False
    If Len(Dir(FolderString, vbDirectory)) > 0 Then FolderExists = True
End Function

and amend the sub

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)
[COLOR=#ff0000]    If myFolder = "" Or FolderExists(myFolder) = False Then myFolder = ChooseFolder[/COLOR]
    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 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    For i = 1 To 3
        Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Cells(i, 1).Value
    Next
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    MsgBox myFile, vbOK, "SAVED TO .."
End Sub