External File Processor

[binar]

Fellow Forum Members,
I would be very grateful if anyone out there could help me develop a macro that matches one of five templates to a list of names I have listed in “Column A”. The template are text files that reside in C:\Templates and are for simplicity sake are named:

1one.txt
2two.txt
3three.txt
Etc…
My filenames are listed in cells A1 through A26 and for simplicity sake the names are as follow:

Cell A1 = Document1
Cell A2 = Document2
Cell A3 = Document3
Etc….
My goal is to have an individual button coded to target each of the five template files which I need to manually match to the correct Document name. These buttons are named:

1one
2two
3three
4four
5five
The macro can detect which cell in Column A is currently selected. So if I want to match template 1one.txt to the name “Document3”, all I have to do is select Cell A3 and click the button that is assigned to template “1one.txt” which is button “1one”. Once I manually make this match, the macro copies the file “1one.txt” (located in C:\Templates), and then renames the matched template file with the name “Document3.txt” and then it pastes the “Document3.txt” file in a folder located in C:\Output. The macro also makes a list of the matched / processed files in "Column H"
Attached is an Excel file that shows a descriptive layout to provide further clarification. Essentially, I'm seeking to manually match custom names to specific external text files with the help of buttons.
I'm just seeking for the basic code for only one button. Not necessary to code all five buttons. Any help will be greatly appreciated.

[binar]

Hi Kenneth,
If you see this post it might remind you of a post you helped me out with a while back ago located in the link below:


http://www.vbaexpress.com/forum/showthread.php?t=36352


This task differs in that it provides more control since I will be manually matching. The script on the link above works from a control list in a batch process mode. Just figured I point that out just in case you decide to participate in this thread. Cheers

[BrianMH]

to return the range that is selected all you need to do is use selection.

[binar]

I think this involves more than just using selection. It needs a macro that is capable of autodectecting what cell is currently selected as shown in the attached Excel file.

[GTO]

I'm not utterly sure, but if I understand what you want to do, maybe:

In the Worksheet's Module:

Option Explicit

Dim FSO As Object

Private Sub cmd3Three_Click()
Dim _
PathOld As String, _
PathNew As String, _
NewName As String

'// Set a reference (once) to FSO //
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
'// Change paths to suit. //
PathOld = ThisWorkbook.Path & "\Old"
PathNew = ThisWorkbook.Path & "\New"
'// I changed the name of CommandButton3. Assuming actual is like your example, I //
'// was thinking that just using the .Caption could be handy. //
NewName = Me.cmd3Three.Caption & ".txt"

If CopyRenameFile(FSO, PathOld, PathNew & "\", ActiveCell.Value, NewName) Then
MsgBox "Success", vbInformation, vbNullString
End If
End Sub

Function CopyRenameFile(FSObj As Object, _
FilePath_From As String, _
FilePath_To As String, _
FileName_Old As String, _
FileName_New _
) As Boolean
Dim MsgText As String

With FSObj
'// Ensure source file exists. //
If .FileExists(VerifyTrailingSep(FilePath_From) & FileName_Old) Then
'// Ensure that the destination file does not exist. //
If Not .FileExists(VerifyTrailingSep(FilePath_To) & FileName_New) _
And .FolderExists(FilePath_To) Then
On Error GoTo Terminate
MsgText = "Error copying"
.CopyFile Source:=VerifyTrailingSep(FilePath_From) & FileName_Old, _
Destination:=VerifyTrailingSep(FilePath_To) & FileName_New, _
OverWriteFiles:=False
On Error GoTo 0
Else
MsgText = "New file already exists, or bad path."
GoTo Terminate
End If
Else
MsgText = "File to copy does not exist."
GoTo Terminate
End If
End With
CopyRenameFile = True
Exit Function
Terminate:
MsgBox MsgText, vbCritical, vbNullString
End Function

Function VerifyTrailingSep(Path As String)

VerifyTrailingSep = Path & IIf(Right(Path, 1) = "\", vbNullString, "\")
End Function
Hope that helps,

Mark

[binar]

Mark,
Thanks for your post and sharing your code. I wasted no time trying it out and I have to admit I am having a tough time getting it to work. It might help me if you can answer the following:

Does your comment:
'// Set a reference (once) to FSO
Require I do something? What do you mean by "Set a reference" ?

I get a compile error at "ME" on this line
NewName = Me.cmd3Three.Caption & ".txt"

I changed ".txt" to 3three.txt
I changed "\Old" to "C:\Template"
I changed "\New" to "C:\Output"

Also, not sure why this script does not showup in the Macro Selector dialog window.

At the most basic level, what I'm essentially trying to accomplish is select a cell that contains a name and then I need to decide which button to match it up with. Once I make the match in my head, I want click on the correct button and have the macro rename the linked external text file wth the name in the selected cell, and then have it copy the renamed file from the template folder over to the output folder. It then also creates a list of the files that were processed in column C.

Any help will be greatly appreciated. Thanks.

[Kenneth Hobs]

The FSO reference is set by late binding so you don't need to set anything. IF it were early bound set, you would need to add the reference in Tools > Reference. The advantage to early binding is that intellisense will then work for that object.

Mark changed the CommandButton3 object name as he explained in the comments. You can change it back to CommandButton3 in the code to test commandbutton3 or rename that button as he explained. This is done by being in Design mode, right clicking the commandbutton3, Properties, or do it in the VBE. Changing the ".txt" would not be useful unless you wanted to change file extension to ".doc" for example.

These sorts of things are easy if you don't do any error checking. A simple FileCopy() will suffice under some conditions. There is an API method where we can tell it to overwrite the file or not and to prompt if it exists or not.

In your Sheet1 object in the VBE, paste this code to try my example.
Private Sub CopyTemplate(cb As MSForms.CommandButton)
Dim inPath As String, outPath As String, tf As Boolean
Dim inFile As String, outFile As String

'Delete outpath file if it exists
tf = True
inPath = "c:\Templates\"
outPath = "c:\output\"

If Dir(inPath, vbDirectory) = "" Then
MsgBox inPath & vbLf & "Macro is ending.", vbCritical, "Folder Does Not Exist"
Exit Sub
End If
If Dir(outPath, vbDirectory) = "" Then
MsgBox outPath & vbLf & "Macro is ending.", vbCritical, "Folder Does Not Exist"
Exit Sub
End If

inFile = inPath & cb.Caption & ".txt"
outFile = outPath & ActiveCell.Value & ".txt"

If Dir(inFile) = "" Then
MsgBox inFile & vbLf & "Macro is ending.", vbCritical, "Input File Does Not Exist"
Exit Sub
End If

If Dir(outFile) <> "" And tf Then Kill outFile
If Dir(outFile) = "" Then FileCopy inFile, outFile

Range("H" & Rows.Count).End(xlUp).Offset(1).Value2 = ActiveCell.Value2
End Sub

Private Sub CommandButton1_Click()
CopyTemplate CommandButton1
End Sub

Private Sub CommandButton2_Click()
CopyTemplate CommandButton2
End Sub

Private Sub CommandButton3_Click()
CopyTemplate CommandButton3
End Sub

Private Sub CommandButton4_Click()
CopyTemplate CommandButton4
End Sub

Private Sub CommandButton5_Click()
CopyTemplate CommandButton5
End Sub


If I were doing that sort of thing, I would use a worksheet change event for a cell in column B that had the template names like the commandbutton captions in a data validatation list. That way, if you have lots of entries in column B, you don't have to scroll back up to click a button after selecting the outfile name in column A.

[binar]

Kenneth,
Here is an animated icon of me bowing down to you in total reverence of your VBA programming skills.

:bow:

Thanks a thousand times. This code of yours is very amazing. I have looked at for a long time now and I can not figure out what line is in charge of the "intellisense" magic. What gives your macro the brains to know which cell is currently selected on the worksheet?

It works exactly as I envisioned it. It allows me to mate together a unique name to a unique template file without having to deal with the mess of doing it manually. To boot, it even generates a list in Column H which is awesome!

Something else I find cool about your code is how I define what template file to look for in the Templates folder only by editing the name of the gray command button itself. Very cool.

Lastly, the one thing I find strange about your macro, and maybe you can clarify, is how it does not display in my MACRO Dialog Window (invoked by pressing ALT + F8) like other Macros do. Your other Macros show up by SUB name in this list and this macro for some reason does not. Just curious why this is the case.

Again many thanks for this masterpiece of code you have posted and thanks as well to everyone that posted in this thread. :hi:

[Kenneth Hobs]

Thanks for the flowers. :friends:

VBA's intellisense is simply an early bound object that shows the methods or properties when you type the period after the object. Help is usually available as well. e.g. Application. The application object was already set so you don't need to set a reference in Tools > References..., and then Dim it.

ActiveCell is used to tell it what range is selected when the macro was played. As explained earlier, Selection could have been used. It does the same thing. Selection is what you typically get when you record a macro. Recording a macro is a good place to start any project. Recorded macros do need modification to make them more efficient sometimes.

Using the Caption property for the commandbutton is good way to go sometimes. For some projects, you might want to use the Tag property if you want to store data temporarily for use later.

The macros are not shown as they are Private. Put a macro in a Module which infers that they will be Public and available to "play" by Alt+F8. Of course you could easily modify the function to take an input parameter by a Module's Sub rather than a commandbutton's caption. I can show you how if needed.

The code could be made a bit better if it were in a Class. If you do many Subs with code that could be programmed, there are a couple ways to go about that if you don't want to do a Class. As you can see in the 5 Subs, they could easily be generated by a simple For loop. I did 250 Private Subs this way once. It sure saves a lot of copy, paste, edit, work.

[binar]

Kenneth,
Just a followup question regarding changing the destination of where the list goes to. Instead of "H" I prefer the list to end up on "Sheet2 Column A". Therefore, I tried two tweaks on my own without any success. They make the compiler crash.

My first attempt was to change:
Range("H" & Rows.Count)... TO Range("Sheet2!A" & Rows.Count)....


My second attempt was to change:
Range("H" & Rows.Count)... TO Range("Sheet2.Select A" & Rows.Count)....

Neither option worked. I really thought such a minor change would be something I could do on my own, however there seems to be something going on with the code that I don't understand. If it's not too much trouble could you change the destination to Sheet2 Column A? Thanks a million.

[BrianMH]

try
sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value2 = ActiveCell.Value2

[Kenneth Hobs]

Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1).Value2 = ActiveCell.Value2

[binar]

Hi Kenneth,
I hope you are doing well and still visiting this forum.

Below is an awesome piece of code you wrote that uses intellisense to make copies of external text files which it copies and directs to an OUTPUT folder as well as generating a list of the filenames on a different worksheet.

I have been using this script of yours a lot, and recently had a project that required I select a cell and then press the button a total of 190 times. Performing this intellisense process a total of 190 times introduces a lot of human errors and takes a lot of time.

Can I bother you to revisit this code you wrote and make the following modification to the intellisense feature:

It would be awesome if the intellisense related code could be modified so that it works on a range of selected cells as a batch process instead of only just a single cell at a time. So for example, I would like to be able to select a range of cells such as G20:G200, followed by pressing a command button linked to modified intellisense code that will perform the intellisense process on a selected range as a batch process on all 180 cells in rapid fire succession. This would eliminate the human error factor by a lot.

I hope modifying your intellisense code in this way is possible. It will make your code much more robust.

Thanks.






The FSO reference is set by late binding so you don't need to set anything. IF it were early bound set, you would need to add the reference in Tools > Reference. The advantage to early binding is that intellisense will then work for that object.

Mark changed the CommandButton3 object name as he explained in the comments. You can change it back to CommandButton3 in the code to test commandbutton3 or rename that button as he explained. This is done by being in Design mode, right clicking the commandbutton3, Properties, or do it in the VBE. Changing the ".txt" would not be useful unless you wanted to change file extension to ".doc" for example.

These sorts of things are easy if you don't do any error checking. A simple FileCopy() will suffice under some conditions. There is an API method where we can tell it to overwrite the file or not and to prompt if it exists or not.

In your Sheet1 object in the VBE, paste this code to try my example.
Private Sub CopyTemplate(cb As MSForms.CommandButton)
Dim inPath As String, outPath As String, tf As Boolean
Dim inFile As String, outFile As String

'Delete outpath file if it exists
tf = True
inPath = "c:\Templates\"
outPath = "c:\output\"

If Dir(inPath, vbDirectory) = "" Then
MsgBox inPath & vbLf & "Macro is ending.", vbCritical, "Folder Does Not Exist"
Exit Sub
End If
If Dir(outPath, vbDirectory) = "" Then
MsgBox outPath & vbLf & "Macro is ending.", vbCritical, "Folder Does Not Exist"
Exit Sub
End If

inFile = inPath & cb.Caption & ".txt"
outFile = outPath & ActiveCell.Value & ".txt"

If Dir(inFile) = "" Then
MsgBox inFile & vbLf & "Macro is ending.", vbCritical, "Input File Does Not Exist"
Exit Sub
End If

If Dir(outFile) <> "" And tf Then Kill outFile
If Dir(outFile) = "" Then FileCopy inFile, outFile

Range("H" & Rows.Count).End(xlUp).Offset(1).Value2 = ActiveCell.Value2
End Sub

Private Sub CommandButton1_Click()
CopyTemplate CommandButton1
End Sub

Private Sub CommandButton2_Click()
CopyTemplate CommandButton2
End Sub

Private Sub CommandButton3_Click()
CopyTemplate CommandButton3
End Sub

Private Sub CommandButton4_Click()
CopyTemplate CommandButton4
End Sub

Private Sub CommandButton5_Click()
CopyTemplate CommandButton5
End Sub


If I were doing that sort of thing, I would use a worksheet change event for a cell in column B that had the template names like the commandbutton captions in a data validatation list. That way, if you have lots of entries in column B, you don't have to scroll back up to click a button after selecting the outfile name in column A.

[Kenneth Hobs]

This is untested but maybe it is close.

Private Sub CopyTemplates(cb As MSForms.CommandButton)
Dim inPath As String, outPath As String, tf As Boolean
Dim inFile As String, outFile As String
Dim aCell As Range

'Delete outpath file if it exists
tf = True
inPath = "c:\Templates\"
outPath = "c:\output\"

If Dir(inPath, vbDirectory) = "" Then
MsgBox inPath & vbLf & "Macro is ending.", vbCritical, "Folder Does Not Exist"
Exit Sub
End If
If Dir(outPath, vbDirectory) = "" Then
MsgBox outPath & vbLf & "Macro is ending.", vbCritical, "Folder Does Not Exist"
Exit Sub
End If

inFile = inPath & cb.Caption & ".txt"

For Each aCell In Selection
outFile = outPath & aCell.Value & ".txt"

If Dir(inFile) = "" Then
MsgBox inFile & vbLf & "Macro is ending.", vbCritical, "Input File Does Not Exist"
Exit Sub
End If

If Dir(outFile) <> "" And tf Then Kill outFile
If Dir(outFile) = "" Then FileCopy inFile, outFile

Range("H" & Rows.Count).End(xlUp).Offset(1).Value2 = aCell.Value2
Next aCell
End Sub

Private Sub CommandButton1_Click()
CopyTemplates CommandButton1
End Sub

Private Sub CommandButton2_Click()
CopyTemplates CommandButton2
End Sub

Private Sub CommandButton3_Click()
CopyTemplates CommandButton3
End Sub

Private Sub CommandButton4_Click()
CopyTemplates CommandButton4
End Sub

Private Sub CommandButton5_Click()
CopyTemplates CommandButton5
End Sub

[binar]

Hi Kenneth,

Below is an animated icon that captures the wide eyed amazement on my face when I was done testing your code and saw the results for the first time:

:eek:

And below is another animated icon of my applauding your VBA coding talent that I find incredible:

:clap:

Your recent code modification to your intellisense code has put this VBA script of yours on total steroids!!!

It processed 180 files at a blink of an eye, and without any errors. Compared to how I was doing it one cell at a time this is a world of difference. In the old way I was making mistakes by selecting the same cell twice or skipping over a cell followed by hitting the command button.

All of these kind of mistakes have disappeared now with this new code of yours. I am in total amazement how it batch processeses whatever range of cells I have selected in RAPID FIRE motion with NO MISTAKES!!!

Thanks a lot. You make this forum the most awesome forum on the internet. Have a great Fourth of July and thanks a lot again. :hi:

[Kenneth Hobs]

I am glad that it worked out for you. Here is a slight improvement that might make it run a tad faster.

cheers

Option Explicit
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
Public glb_origCalculationMode As Integer

Private Sub CopyTemplates(cb As MSForms.CommandButton)
Dim inPath As String, outPath As String, tf As Boolean
Dim inFile As String, outFile As String
Dim aCell As Range

On Error GoTo EndNow
SpeedOn

'Delete outpath file if it exists
tf = True
inPath = "c:\Templates\"
outPath = "c:\output\"

If Dir(inPath, vbDirectory) = "" Then
MsgBox inPath & vbLf & "Macro is ending.", vbCritical, "Folder Does Not Exist"
Exit Sub
End If
If Dir(outPath, vbDirectory) = "" Then
MsgBox outPath & vbLf & "Macro is ending.", vbCritical, "Folder Does Not Exist"
Exit Sub
End If

inFile = inPath & cb.Caption & ".txt"
If Dir(inFile) = "" Then
MsgBox inFile & vbLf & "Macro is ending.", vbCritical, "Input File Does Not Exist"
Exit Sub
End If

For Each aCell In Selection
outFile = outPath & aCell.Value & ".txt"

If Dir(outFile) <> "" And tf Then Kill outFile
If Dir(outFile) = "" Then FileCopy inFile, outFile

Range("H" & Rows.Count).End(xlUp).Offset(1).Value2 = aCell.Value2
Next aCell

EndNow:
SpeedOff
End Sub

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub

Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub CommandButton1_Click()
CopyTemplates CommandButton1
End Sub

[binar]

Hi Kenneth,
I have tried out your newly revised code and it works nicely. I'll take your word that it is a tad faster. Nevertheless, thanks for your time in revising the code to where it's currently at. I plan to use it from now on. It is light years ahead of your first script that required performing the intellisense process a single cell at a time. Thanks a lot again for your script. It amazing!

:thumb