Generate TEXT Files from a Control File?

[binar]

Fellow Forum Members,
I got a work related task that takes a lot of time and unfortunately no application exists that does this task out of the box. The task involves making anywhere between 60 to 120 copies from a single seed text file and then individually renaming each of the copied text files with sequential filenames (such as: Part0001.txt, Part0002.txt, Part0003.txt, etc...) It's very labor intensive!

I would much rather setup a 120 row sequential filename list inside Excel and then have a VBA script copy and paste the seed text file 120 times while also assigning sequential filenames it picks up from the filename list inside the control file. Is such a VBA script possible?

The Excel worksheet (control file) needs to have these columns:
Column A (CELL A1) = Path of Seed Text File
Column B (CELL B1) = Name of Seed File
Column C = List of sequential filenames
Column D (CELL D1) = Path of output folder where all the text file copies end up.

I have researched File Renamer applications, and none of them seem to have a feature that will let one duplicate a seed file many times and rename all of the copies with a custom sequential filename.

Any help will be greatly appreciated. Thanks.

[Tommy]

binar,

Could you post an example spreadsheet with the information and a sample text file so we don't have to guess and or duplicate?

Thanks you much!

[Kenneth Hobs]

Of course your macro could build the sequential filenames. I wrote a kbase entry to create sequential filenames like Windows does.

The method using FileCopy() is simple. Checking for folder or file existence is bulk of it.

Sub SeedFileCopy()
Dim pathSeed As String, seedFilename As String, outFolder As String
Dim seedFile As String, outPath As String, outFile As String
Dim cell As Range

pathSeed = Range("A1").Value2
If Dir(pathSeed, vbDirectory) = "" Then
MsgBox pathSeed & " does not exist!", vbCritical, "Macro Ending"
Exit Sub
End If

seedFilename = Range("B1").Value2
seedFile = AddBackSlash(pathSeed) & seedFilename
If Dir(seedFile) = "" Then
MsgBox seedFile & " does not exist!", vbCritical, "Macro Ending"
Exit Sub
End If

outPath = Range("D1").Value2
If Dir(outPath, vbDirectory) = "" Then
MsgBox outPath & " does not exist!", vbCritical, "Macro Ending"
Exit Sub
End If
outPath = AddBackSlash(outPath)

For Each cell In Range("C1", Range("C1").End(xlDown))
FileCopy seedFile, outPath & cell.Value2
Next cell
End Sub

Function AddBackSlash(str As String) As String
If Right(str, 1) = "\" Then
AddBackSlash = str
Else: AddBackSlash = str & "\"
End If
End Function

[shrivallabha]

A bit of DOS (and Bat files) can do this. I am attaching a workbook sample for you. You need to:

1. Put in necessary data in first 4 columns.
2. Copy down the formula written from cell E2.
3. Open Notepad (You use them a lot, don't you?)
4. Copy and paste the data from E1 to last row to opened Notepad.
5. Save the text file as SomeName.bat (batch file) and close it.
6. Double click on the SomeName.bat and your files will be copied as you need.

If need be, we can then try to convert this to VBA code.

[binar]

Kenneth,
Thank you a million times. You created a masterpiece! It performs exactly the way I envisioned it. I am blown away how all the text files magically appear in the Test Area folder. I also like how you got the filename to write itself with that formula you included. I'm going to feel like Superman at work now because both of the scripts you have shared really speed up my work when used together.

My plan is to create a single seedfile that is embedded with unique symbol target text such as "#". Once I finish setting up my seedfile with all necessary unique target text, I'm going to make 120 copies of this seed file (using your script in this thread). Finally, using your other script that replaces text using a control file, I'm going to replace all of the unique symbol target text in one single batch process! This is the coolest set of tools ever!

Also, thanks to everyone who posted in this thread. Shrivallabha, thanks for the DOS BAT solution. It's also a cool solution, however I prefer to work with Excel. :hi:

[binar]

Kenneth,
One more question. Can you recommend a good VBA programming book for a newbie? I would very much like to someday program my own stuff using VBA within Excel. This skill is very handy to have as you have shown me with both of your scripts. Thanks.

[shrivallabha]

It can be made 'cooler' as below:
Option Explicit
Dim i As Integer
Const csPath As String = "C:\Copy.bat"
Dim lLastRow As Long
Private Sub RunApp_Click()
With Sheet1
lLastRow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 4 To lLastRow
.Range("E" & i).FormulaR1C1 = _
"=""copy ""&""""""""&RC[-4]&""\""&RC[-3]&""""""""&"" ""&""""""""&RC[-2]&""\""&RC[-1]&"""""""""
Next i
End With
CreateBatFile
MsgBox "Files are copied to desired location!"
Kill csPath
End Sub
Private Sub CreateBatFile()
Dim FSO As Object, Writer As Object
'Creating a DOS file
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Writer = FSO.CreateTextFile(csPath, True)
'This help prevents DOS NOT to show the command
Writer.WriteLine ("@ echo off")
Writer.Close
With Sheet1
lLastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set Writer = FSO.OpenTextFile(csPath, 8)
For i = 4 To lLastRow
Writer.WriteLine (.Range("E" & i).Value)
Next i
Writer.Close
End With
RunBatFile
End Sub
Private Sub RunBatFile()
Dim RunBat
'Running the bat file from excel
RunBat = Shell(csPath, 0)
'Cleanup
With Sheet1
.Range("E4:E" & lLastRow).ClearContents
End With
End Sub


I am attaching the revised Excel file which has a button. One word (sentence maybe :biggrin: ) of caution: paste the path as it is like:
C:\MyPath and not C:\Mypath\
And it will perform the above mentioned manual steps.

Kenneth's solution is really good. I just did this out of curiosity!

PS Edit: I am also learning VBA just because I like it. There are two books that I've been referring to for quite a while:
VBA and Macros for Microsoft Excel by Bill Jelen Aka MrExcel
Excel 2007 VBA Programmer's Reference by John Green, Stephen Bullen, Rob Bovey and Michael Alexander (I saw a recommendation on XLD's website for its earlier edition)

[Kenneth Hobs]

Some of the MVP's have good books. I like John Walkenbach's books.

http://mvps.org/links.html#Excel