I have a line of code similar to the below:
Workbooks.Open ("C:Test.xls")

Later in the same macro I have the following code:
ActiveCell.FormulaR1C1 = "'Test"

I want to be able to replace the "C:Test.xls" with the next file in this
process and to also use the file name to populate the information in the
second line of code.

Is this a loop to open several books?
"Replace C:\Test.xls with the next file in this process." So are the file
names already put in a column in one workbook, or does each workbook contain
the
name of the next workbook to open?

To get "test" from "C:\Test.xls" you'd simply do this:

public sub MyMacro()
Dim strFilePath As String
Dim strFileName As String

strFilePath = "C:\Files\Test.xls"
strFileName = FileNameFromPath(strFilePath)
' strFileName will then equal Test
end sub

Public Function FileNameFromPath(ByVal strPath As String) As String
If strPath < Empty Then
FileNameFromPath = Mid$(strPath, InStrRev(strPath, "\") + 1, _
(Len(strPath) - InStr(1, strPath, ".")) + 1)
End If
End Function

"JT" wrote:

I have a line of code similar to the below:
Workbooks.Open ("C:Test.xls")

Later in the same macro I have the following code:
ActiveCell.FormulaR1C1 = "'Test"

I want to be able to replace the "C:Test.xls" with the next file in this
process and to also use the file name to populate the information in the
second line of code.