VBA Code to Write Selected Cell Range to File

Tonight's tip is on an Excel macro you can use to copy a selected range directly to a text file, or more specifically a batch file.

In this example, you can see that I have some code for the Windows XCOPY command which will copy four PDFs from one folder to another. I have selected the lines I want to save to a batch file that I can run from the destination folder.

In Visual Basic I have inputted the VBA code found on this site, http://stackoverflow.com/questions/19688269/copy-a-range-selection-to-text-file

I changed the code posted to stackoverflow on the line currently reading:

oFile.Write c.Value & vbCrLf

from: oFile.Write c.Value & " "

. . . so a line break would be added between the selected cells rather than a space. The updated code is posted below. You may need to delete the spaces in front of the oFile.Write c.Value & vbCrLf line and add a tab. The Wix editor converts tabs to spaces.

The macro will successfully generate a batch file, that you can then double-click and run.

Sub writeCells() Dim strPath As String Dim strName As String Dim FSO As Object Dim oFile As Object Dim c As Range

strName = "test.bat" strPath = "C:\foofolder3\"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set oFile = FSO.CreateTextFile(strPath & strName)

For Each c In Selection

oFile.Write c.Value & vbCrLf Next c

oFile.Close

End Sub