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