Macro that gets data out of the File Name

Jason, where do you want to have this column (i.e., are you looking to fill column D)? Also, do you want all the rows (from 1 to 65,536) to be filled with the number. Finally, is the number always the first two characters of the file name?

BarrieBarrie Davidson

More Info

Thanks for the interest.
I'm trying to insert a row that will be row C and have it labled Meter at the top.
It will always be the first two digits in the file name.
Also, I only want it to fill as many rows as there are in the file.

This is starting to get way more complicated than I had anticipated.

Re: More Info

Try this :

- Define a name (let's say "File") equal to "=GET.DOCUMENT(88)"
- If you want the leading zero to be displayed, then put in a cell "=LEFT(File,2)"
- If you don't want the leading zero to be displayed then put "=LEFT(File,2)*1"

Re: More Info

Sorry, you need a macro. After defining the name per my previous posting, use this macro :-

Sub MyMacro()
Columns("C:C").Insert
Range(Range("A2"), Range("A65536").End(xlUp)).Offset(0, 2).FormulaR1C1 = "=LEFT(File,2)"
End Sub

This macro assumes that the last entry in column A is the last row on the worksheet.

Try this : - If you want the leading zero to be displayed, then put in a cell "=LEFT(File,2)" - If you don't want the leading zero to be displayed then put "=LEFT(File,2)*1" : Thanks for the interest. : I'm trying to insert a row that will be row C and have it labled Meter at the top. : It will always be the first two digits in the file name. : Also, I only want it to fill as many rows as there are in the file. : This is starting to get way more complicated than I had anticipated. :

Thanks, Henry, just one more question.

Henry,

Thanks a lot for the help. It's looking really good. I'm still a little confused on how to get the get.document command to work.
I tried to write
file = get.document(88)
It wont let it compile.
Am I doing it wrong?

Sorry, you need a macro. After defining the name per my previous posting, use this macro :- Sub MyMacro() Columns("C:C").Insert Range(Range("A2"), Range("A65536").End(xlUp)).Offset(0, 2).FormulaR1C1 = "=LEFT(File,2)" End Sub This macro assumes that the last entry in column A is the last row on the worksheet. : Try this

Going with Henry's code

Thanks a lot for the help. It's looking really good. I'm still a little confused on how to get the get.document command to work. I tried to write file = get.document(88) It wont let it compile. Am I doing it wrong?

: Sorry, you need a macro. After defining the name per my previous posting, use this macro :- : Sub MyMacro() : Columns("C:C").Insert : Range(Range("A2"), Range("A65536").End(xlUp)).Offset(0, 2).FormulaR1C1 = "=LEFT(File,2)" : End Sub : This macro assumes that the last entry in column A is the last row on the worksheet. :

You could use:

Sub MyMacro()
Columns("C:C").Insert
Range(Range("A2"), Range("A65536").End(xlUp)).Offset(0, 2).Value = Left$(ActiveWorkbook.Name, 2)
End Sub

I'm assuming you don't want to lose the leading zero in your file name.

Regards,
Barrie

Barrie Davidson

Re: Thanks, Henry, just one more question.

Thanks a lot for the help. It's looking really good. I'm still a little confused on how to get the get.document command to work. I tried to write file = get.document(88) It wont let it compile. Am I doing it wrong?

: Sorry, you need a macro. After defining the name per my previous posting, use this macro :- : Sub MyMacro() : Columns("C:C").Insert : Range(Range("A2"), Range("A65536").End(xlUp)).Offset(0, 2).FormulaR1C1 = "=LEFT(File,2)" : End Sub : This macro assumes that the last entry in column A is the last row on the worksheet. :

If you use Barrie's coding then you don't have to mess around with creating a name.
However, if you are interested in how to do a worksheet function to get the workbook name, this is one way :

- Go to Insert>Name>Define
- In the NamesInWorkbook box, type the word "File" without the quotes (or any other word you choose).
- In the RefersTo box, type =GET.DOCUMENT(88)
- Click OK

You can then get the workbook name by entering in any cell the formula "=File"