vba - How to fill up excel names based on filename -

vba - How to fill up excel names based on filename -

i inquire if there excel macro or vba automatically file name e.g. d01q12013 , add together 3 new columns(district, quarter, year) table existing info in first 13 columns, , populate 3 new columns based on filename (district - 01, quarter - 1, year - 2013)?

the below should started:

sub getname() range("a1").value = thisworkbook.name range("a2").value = "district - " & mid(thisworkbook.name, 2, 2) range("a3").value = "quarter - " & mid(thisworkbook.name, 5, 1) range("a4").value = "year - " & mid(thisworkbook.name, 6, 4) end sub

the 2 key elements here:

thisworkbook.name = gives name of workbook in code located. utilize "activeworkbook.name" name of workbook active.

mid = allows extract section of piece of text (such filename of workbook). format using function is:

mid(text_to_extract_from, start_position, number_of_characters)

so line "range("a4").value = "year - " & mid(thisworkbook.name, 6, 4)" sets value of cell a4 phrase begins "year - " , uses 4 characters origin 6th character of filename of workbook code located.

using these elements , macro recorder, should able accomplish goal of inserting new columns contain portions of filename.

excel vba excel-vba