Automate task w/VBA ?

[DizzyD]

Hi all,
Perhaps one (or more) of you wizards can assist me with
automating a daily tasks. Here's the deal - Every day I
ftp a batch file to a partner company, they break that
batch up into individual files ( usually 50-100). Thru a
script they deposit these files back into a pre-defined
folder on my server - however, they add a bunch of
characters to each file name. I copy all these files into
notepad, strip these characters and manually count the
total files received. I don't mind doing the copy and
paste function into Notepad, Word, Excel or whatever and
from there I like some VBA to perhaps do a string function
(?) to strip all but the last 6 characters of the file
name and total the number of files. Example below...

I ftp:
Batch AL021504.txt ( includes 50 files)

I receive the next day:
BVDX100001.txt
BVDX100002.txt
BVDX100003.txt
BVDX100004.txt
BVDX100005.txt

and so on for the remaining 45 files..

Currently I copy all 50 into Notepad, crop the BVDX and
the .txt extension and manually count the number of files.

Is there some VBA magic I can use to help me crop and add ?

I hope I explained the requirements??

As always - thanks in advance !!! ;-)

 

[Peter Hewett]

Hi Diddy

I'm not sure what you're using Notepad for??? You just what to rename all
files with a BVDX prefix??? You don't do anything else with the file???

Cheers - Peter

 

[Guest]

I leave the files in the folder they are downloaded into
BUT I need to count all the individual files from the
overnight batch and compare them to a matrix from the day
before to insure I received confirmation that the entire
batch was received. I do NOT want to rename the files,
currently I highlight all the files names, copy and paste
into Notepad so I can crop them and print out a page and
then manually count the files.

Also, adding a checkbox next to the file names would be
nice too!

Cheers!

 

[Jezebel]

It might be easier to do this in Excel. Wouldn't be entirely automatic, but
you can see exactly what's going on, which sounds like a plus in this case.

Excel has the functions Left(), Mid() and Right() to retrieve sections of
text from another cell. So if you paste the list into columnA, you could use
=Mid(A1,5,6) in column B to extract the six characters you want from the
name (then fill downwards for the entire column). And incidentally, the
count of files will be the number of rows you've filled in column A.

VBA has the same functions so you could automate this completely, but it
hardly sounds worth it.

 

[Peter Hewett]

Hi Diddy

Try this, it will produce a nice neatly formatted Word document. All you
have to do is modify the code to set the path to where your files are kept:

Public Sub FileNameList()
Dim docOutput As Word.Document
Dim rngOut As Word.Range
Dim rngHold As Word.Range
Dim strRootFolder As String
Dim strFile As String

' Create output document
Set docOutput = Documents.Add
Set rngOut = docOutput.Content

' This is the folder to search for your files in
strRootFolder = "F:\My Templates\Test Documents\*.txt"

' Add all files in the root folder to the output document
strFile = Dir$(strRootFolder)
Do Until LenB(strFile) = 0
' Crop first 4 characters of file name and file type
strFile = Left$(strFile, InStr(strFile, ".") - 1)

' Add filename to document
rngOut.InsertSymbol -3933, "Wingdings 2", True
rngOut.Font.Size = 14
rngOut.Move wdCharacter, 1
Set rngHold = rngOut.Duplicate
rngOut.Text = " " & strFile & vbCr
rngOut.Start = rngHold.End
rngOut.Font.Size = 11
rngOut.Collapse wdCollapseEnd

' Next file if any
strFile = Dir$
Loop


With ActiveDocument.Content

' Delete the last paragraph in the document as it's redundant
.Paragraphs(.Paragraphs.Count).Range.Select
Selection.Delete

' Tidy up paragraph formatting
With .ParagraphFormat
.SpaceBefore = 3
.SpaceAfter = 3
End With
End With
End Sub

HTH + Cheers - Peter

 

[Peter Hewett]

Hi Dizzy

Even though the comment says <Crop first 4 characters of file name and file
type>, it doesn't! replace this line:
strFile = Left$(strFile, InStr(strFile, ".") - 1)

with this line:
strFile = Mid$(Left$(strFile, InStr(strFile, ".") - 1), 5)

Cheers - Peter

 

[DizzyD]

Thanks for the great idea... Since I will ALWAYS need the
last 6 characters before the file extension which function
works best? Right(), Mid(), Left().

example ACKJM00101.txt

We always want the 6 characters to the IMMEDIATE left of
the .

And since one day I may get 50 files and the 85 how do I
setup Excel to automatically place a "Total Files
received" with the count at the last row?

Thanks again!

 

[Jezebel]

You also have the Len() function to tell you the length of the name, so if
your filenames always comprise an unknown prefix, then six numeric
characters, then the four character ".txt", you could use = Mid(A1,
len(A1)-9,6).

As for the total files received, use the Count() function, which returns the
non-null cells in the selected range.