FIND not applicable in VBA

[lambuhere1]

Hi
I am trying to extact the file name and use it as a variable in Xl. I am importing a text file in here and since it is a text file, the sheet shall have the same name as the imported text file. It is then saved as the same name with .xls extension. What I am trying to do is to extract the name from the filename and use the thing to rename tha sheets and add the sheets. But I get error in Find location.

Can you guide me out. Below is the code for reference.

Sub Getfilename()

Dim zyy As String, zab As Integer, zxx As String, zzz As String

zyy = ActiveWorkbook.Name
zxx = ActiveWorkbook.Path
MsgBox " Workbook path is " & zxx
MsgBox " Current Filename is " & zyy
zzz = Left("zyy", Find(".", "zyy") - 1)


Sheets("zyy").Select
Sheets("zyy").Name = "Sheet1"

'And this is the VBA code to Add new Sheet to the open file.
Do Until zab <= 4

Sheets.Add
zab = zab + 1
Loop
End Sub

 

[PearlGI]

You need to use the InStr function rather than Find.

Try this line:

zzz=Left(zyy, Instr(1, zyy, ".") - 1)

That should sort you out.

HTH

 

[lambuhere1]

Thanks a lot for your help. Got confused..

Ram P