Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Automation of File Name (Excel 2003)

    Hi, hopefully someone will be able to help me. I import a number of csv files into Excel. The value of cell A1 is always something like ( there are multiple spaces between the POLCBRN name and Division even though they don't show up in the post)

    'Filter by: Policy Control Branch (POLCBRN)=BLOEMFONTEIN Division (DIV)=AGRICULTURE Section Code (SEC)=BUILDINGS COMBINED , EMPLOYERS' LIABILITY , MOTOR SPECIFIED EXTENSIONS , MOTOR TRADERS - INT & EXT , PUBLIC LIAB. OCCURRENCE BASIS , PUBLIC LIAB.CLAIMS MADE BASIS , SPECIFIED BUSES , SPECIFIED CARS , SPECIFIED COMMERCIAL VEHICLES , SPECIFIED MOTORCYCLES , SPECIFIED TRAILERS Period=2008-10 "

    I want to name the file by concatenating the value of (POLCBRN) - in this case BLOEMFONTEIN and the first 4 letters of DIV - in this case AGRI.

    To do this by placing a formula in a cell and referring to it is quite easy - =MID(A1,FIND("N)=",A1)+3,FIND(" ",A1)-FIND("N)=",A1))&""&MID(A1,FIND("V)=",A1)+3,4)

    How do I do the same in VBA? I cannot find the VBA equivalent of "FIND".

    Any help will be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automation of File Name (Excel 2003)

    In VBA you can use the InStr function:

    Dim intPos As Integer
    intPos = Instr(Range("A1"), "N)=")

    intPos will be the position where "N)=" is found within the value of cell A1, or 0 if not found.

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Automation of File Name (Excel 2003)

    Thanks very much Hans I've managed to get it to work with your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •