Extracting part of file name.

[Slade]

Hi

I would like to know how do i extract part of a file name or the complete file name to a cell. The path is on a server so it will be like "\\server\folder\" and then the file name "ed_scenario_validation_20110810.csv" the part with out the date "ed_scenario_validation_" always stay the same then the date "20110810" changes everyday.

Tnx

 

[wigi]

Hello

Have a look at this page:

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

 

[Slade]

I came across this as well but this is for the current workbook file name. The file name i am trying to import is from another source and nothing to do with the current work book. just need the file name in a cell to compare with other ones.

 

[Slade]

I am trying to use Application.Filesearch but read just now that it is not available in excel 2007. The code i tried was as follow:

Function FileName()
With Application.FileSearch
   .NewSearch
   .LookIn = "C:\"
   .FileName = "dir*.txt"
   .Execute

   For i = 1 To .FoundFiles.Count
       Debug.Print .FoundFiles(i)
   Next

End With
End Function

and then what i had in the cell was: =IF(B10=1,Filename,"Good")

What code can i use now to replace the Filesearch?

 

[wigi]

What code can i use now to replace the Filesearch?

Dir()

Many topics use code with Dir, so have a look in the other topics.

 

[Slade]

I have read trough a few of them but have no idee what is going on in them

 

[Slade]

I have done this but still does not work.

Function FileName()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String

sPath = "C:\" 'location of files
ChDir sPath
sFil = Dir("*.txt") 'change or add formats
Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

' do something
'oWbk.Close True 'close the workbook, saving changes
sFil = Dir
Loop ' End of LOOP
'End Sub

End Function

 

[Slade]

Ok wait i got it to work with this.

Function SitFileName()
  Dim oWbk As Workbook
  Dim sFil As String
  Dim sPath As String

  sPath = "C:\" 'location of files
  ChDir sPath
  sFil = Dir("dir*.txt") 'change or add formats
  Do While sFil <> "" 
  Sheet1.Range("A12") = sFil
  sFil = Dir
  Loop ' End of LOOP

End Function

But how do i run this function as a formula in a call. here is what i tried: =IF(B10=1,SitFileName,"Good")
But it does not work. If i run it out of the vb script directly it does work.

 

[wigi]

Do you mean that you put the function in a cell?

You should then assign the result of the formula to your formula name, like in:

Function SitFileName()
  Dim oWbk As Workbook
  Dim sFil As String
  Dim sPath As String

  sPath = "C:\" 'location of files
  ChDir sPath
  sFil = Dir("dir*.txt") 'change or add formats
  Do While sFil <> "" 
  SitFileName = sFil
  sFil = Dir
  Loop ' End of LOOP

End Function

This is how it should be syntax-wise, but I am not sure this is what you mean for YOUR function. Now, SitFileName will receive the name of the last file in the loop.

 

[Slade]

This worked now and now my problem is it does not read a file on a mapped drive.