Posted on

Write to text file with Excel VBA macro

The following Excel VBA code will write the contents of column S (first 1001 lines) to a text file named after B16 in a directory labeled B15. The B15 entry will also look for “desktop” or “Desktop” and find the local desktop folder for the user. This is accopmplished by the CreateObject("WScript.Shell").specialfolders("Desktop") item

Sub fileWrite()
    If (Cells(15, 2).Value = "Desktop") Or (Cells(15, 2).Value = "desktop") Then  'if input cell is desktop then
        strFolder = CreateObject("WScript.Shell").specialfolders("Desktop") & "\" 'use desktop folder
    Else
        strFolder = Cells(15, 2).Value 'otherwise use directory path listed in input cell
    End If
    strFile = Cells(16, 2).Value 'input cell for file name
    strPath = strFolder & strFile 'combined directory + file name
    Dim fso As Object 'declare file system object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object 'declare file object
    Set oFile = fso.CreateTextFile(strPath)
    'write the first 1001 lines of column S to a text file
    For i = 0 To 1000 'counter
        oFile.WriteLine Range("S" & (i + 1)) 'write cell contents to text file
    Next i
    oFile.Close 'close file
    Set fso = Nothing
    Set oFile = Nothing
End Sub