Capture the Name of the Excel file while opening the same using VBA code

[tinumathews]

I am using a code to open file from another file.


Sub OpenFile()

Dim fileName


fileName = Application.GetOpenFilename


If fileName <> "False" Then

Workbooks.Open fileName, Format:=2

End If

End Sub


My requirement is to capture the name of the file (Not the path) which is opened through the code. Somthing like


Dim xlname as string

xlname = ThisWorkbook.Name. If i use this in my code above it gives me the first file's name.


OR


In other words I need to open a file and capture the same file's name in a variable and use it for the rest of the code.


Can you please help me....

 

[SirJB7]

Hi, tinumathews!

Tried "Workbooks(Application.Workbooks.Count).Name" yet?

Regards!

 

[tinumathews]

Hi SirJB7,


Thanks for the reply, I am new to VBA coding so have not tried. Now i tried and not getting the required result. i have changed the code to

Sub OpenFile()

Dim fileName


fileName = Application.GetOpenFilename


If fileName <> "False" Then

Workbooks(Application.Workbooks.Count).Name , Format:=2

End If

End Sub


am i missing somthing here ?

 

[tinumathews]

OH ok i understood, i have captured the name in a variable. But this is giving me the name of the file that is been used for the code. My requirement is to capture the name of the file that i open with this code...


any idea, or am i not correct in understanding the basic

 

[SirJB7]

Hi, tinumathews!

Let's change the code to this:

-----

Sub OpenFile()

Dim fileName, filename2


fileName = Application.GetOpenFilename


If fileName <> "False" Then

workbooks.open filename, format:=2

filename2=Workbooks(Application.Workbooks.Count).Name

End If

End Sub

-----


In variable "filename" you'll get the full path to the opened file, and in variable "filename2" you'll get just the name with the extension and without the path.

Regards!