Accessing the text of a filename

[Jacek]

Hello everyone,

I come to you for some guidance regarding a task that I have been assigned to do. What I am trying to do is have my macro simply take the filename and generate it in a specific cell.

Specifically, the filenames are in the following format: "20100512_xxx.dat"

What I would like to do is write "2010-05-12" into a specific cell.

Thank you in advance.

Jacek

 

[wigi]

my macro simply take the filename

How can you get the filename? Does the user choose the filename? Is the file opened?

 

[Jacek]

How can you get the filename? Does the user choose the filename? Is the file opened?

Yes, my macro requests the user to choose the file, and then I have the macro actually open the file. It is a .dat file.

My macro uses the Application.GetOpenFileName procedure and assigns a variable to the file.

I then use Workbooks.OpenText Filename:= "the variable name"

The .dat file is a CSV file...if that makes sense.

Hope this info is sufficient.

thanks.

 

[Jacek]

Ok so I have just read into the MID function...and the Cell("filename") utility. The only problem is it shows the entire filepath. What about having one cell show the filename alone, then I can use the MID function to specify what characters I want...then I have to figure out how to add the hyphens.

 

[djreiswig]

If the format of the filename is consistent then just pick it apart from the right side. This should be able to be modified to meet your needs.

Sub test()
    Var = "C:\temp\test\123\20100512_xxx.dat"
    Debug.Print Mid(Var, Len(Var) - 15, 4) & "-" & Mid(Var, Len(Var) - 11, 2) & "-" & Mid(Var, Len(Var) - 9, 2)
End Sub

 

[wigi]

Ok so I have just read into the MID function...and the Cell("filename") utility. The only problem is it shows the entire filepath. What about having one cell show the filename alone, then I can use the MID function to specify what characters I want...then I have to figure out how to add the hyphens.

A quick Google search action would have given:

http://office.microsoft.com/en-us/e...nt-excel-file-name-in-a-cell-HA001034627.aspx

 

[Jacek]

A quick Google search action would have given:

http://office.microsoft.com/en-us/e...nt-excel-file-name-in-a-cell-HA001034627.aspx

Haha, yes I just came across that same webpage. Wonderful, I got everything working. Looks great. Thanks guys!