Office VBA--Get the Current Workbook/Sheet names?

I need to retrive the file name of the currrent open Workbook, and the
Current WorkSheets name for use in a string. I open the WorkBook with a
complete filename given by the user in a form.

Dim MP3Collection As String
Dim WishList As String

Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = False
Application.ScreenUpdating = False

'Open MP3 Collection List
MP3Collection = txtWishList(1).Text
Workbooks.Open filename:=MP3Collection

So Now I need to know the filename only & the worksheet name only for use
in the following bits of code:

Worksheets("Mp3_Export").Range("C2:Finish").FillUp
ActiveCell.FormulaR1C1 = "=COUNTIF([Collection.xls]Mp3_Export!C[-1],RC[-
1])"

My code basically does this: Opens two Excel files specified by my user
on a form (therfore these names are not constant). THen the code
contacates columns A & B on both files & compares for duplicates.

So in order to loose the constants inmy test code above I need to
determine the filename & Worksheet name of the files they opened. My
current two strings used to open the files contain the FUll PAth Name of
each File.

THANKS

The Current Workbook name can be used doing this:

Code:

---

x.ActiveWorkbook.Name

---

for the other one, like this:

Code:

---

Function GetShortPath(LongPath As String) As String
Dim ShortPath As String
Dim pos As Integer

pos = InStrRev(FilePath, "\") 'find last slash
ShortPath = Mid(FilePath, pos + 1) 'extract file name(not the slash!!)
GetShortPath = ShortPath 'return
End Function

---

Thanks. I got it to work slightly different than you suggested but your insight helped me catch a clue.
Here is the way I did it.

Dim MySheet1 As String
Dim MyWorkBook1 As String
MyWorkBook1 = ActiveWorkbook.Name
MySheet1 = Sheets(1).Name
MsgBox MySheet1
MsgBox MyWorkBook1

My question is this, what was the "x." for in your example? When I used it I got an error. Also ActiveWorkBook.Name returned the actual file name of the workbook including extension.

Finally now that I have my strings, do you see a problem with the following use of them?

Worksheets(MySheet1)Range("C2:Finish").FillUp

ActiveCell.FormulaR1C1 = "=COUNTIF([MyWorkBook1]MySheet1!C[-1],RC[-1])"


Thanks for your help