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