Thread: Excel to Txt Export

Hi All

I am new to this forum and also to Excel VBA.
I want to create a macro that can export all values in Column A to a separate txt file in desktop.

For example, if Column A looks like:

1
2
3
4
5

after running the macro, all the data only from current sheet column A is exported to txt file (along with newline character) and put it in desktop.

I have no clue how to create a txt file through macro... Can some one please help.

Thank you,

Welcome to the forum!

Sub Test_StrToTXTFile()
  StrToTXTFile CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator & "ken.txt", _
    Join(WorksheetFunction.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp))), vbCrLf)
End Sub

Sub StrToTXTFile(filePath As String, str As String)
  Dim hFile As Integer
  If Dir(FolderPart(filePath), vbDirectory) = "" Then
    MsgBox filePath, vbCritical, "Missing Folder"
    Exit Sub
  End If
  
  hFile = FreeFile
  Open filePath For Output As #hFile
  If str <> "" Then Print #hFile, str
  Close hFile
End Sub

Function FolderPart(sPath As String) As String
  FolderPart = Left(sPath, InStrRev(sPath, "\"))
End Function