MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro that gets data out of the File Name


Posted by Jason on September 26, 2001 12:47 PM

I'm trying to make a macro that creates a column and fills every row with the number that is in the file name.
ex.
Filname is 07_25_42.xls

I want to add a column that has every row filled with 7.

Anyone know how to do this?


Posted by Barrie Davidson on September 26, 2001 1:22 PM

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

Posted by Jason on September 26, 2001 1:52 PM

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.

Posted by Henry on September 26, 2001 9:57 PM

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"

Posted by Henry on September 26, 2001 10:13 PM

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. :

Posted by Jason on September 27, 2001 8:51 AM

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


Posted by Barrie Davidson on September 27, 2001 10:57 AM

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

Posted by Henry on September 27, 2001 3:03 PM

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"