Re: Automate task w/VBA ?

From: Jezebel (frolly_at_mkkk.com)
Date: 02/22/04 

Date: Sun, 22 Feb 2004 11:07:21 +1100

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.

"DizzyD" <DizzyD@discussions.microsoft.com> wrote in message
news:13d8e01c3f8d0$7181e4d0$a301280a@phx.gbl...
> 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 !!! ;-)
>

Relevant Pages

  • Re: Calling Crystal Ball Predictor from VBA
    ... Tell us which version of Excel you're using, and post the VBA code that you've recorded. ... Tell us why you want to use CB Predictor. ... Developer Kits allow users to completely automate and control Crystal ...
    (microsoft.public.excel.programming)
  • Importing tabulated text data into excel
    ... I am a newbe to Excel VBA, and so-so for general Excel use. ... clipboard before importing. ... Back to the "Notepad" method, there are some required manual steps in order ...
    (microsoft.public.excel)
  • Re: How to develop MS Office cross application programs
    ... logical extension of VBA. ... >> code for Word, Access, or whatever from within the Excel IDE. ... > object modelyou want to automate. ...
    (microsoft.public.office.developer.vba)
  • Re: Not equal to?
    ... Copy the VBA ... code to Notepad and delete the modules. ... > spreadsheet for years. ... this was all written in Excel 2000. ...
    (microsoft.public.excel.programming)
  • EXPORTING MS PROJECT TIMEPHASED DATA
    ... and therefore I would have thought Microsoft would have helped automate this ... The task is to export MS Project timephased data into MS Excel, ... Perhaps this could be done via VBA? ...
    (microsoft.public.excel)
Loading