Isolate the name of the file after GetOpenFilename

[paulbhess]

I have a number of spreadsheets that need conversion to new layout to accommodate some added rows. I want to select the original workbook, save it to a new directory, then update the various cells from one workbook to the other. Is there a simple way to isolate the file name from the drive/path? I came across some code that used the dialog box "GetOpenFilename," so I thought that would be a good place to start. My "VBA and Macros: Microsoft Excel" book was not helpful.
I'm using MS Excel 2007.
Thanks for the assistance.

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

hi,

So, have you opened all the workbooks or are you opening them one at a time in a loop to process and then need to reference the name later?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mintjulep]

Simply object.name

where object is a reference to the workbook of interest.

 

[paulbhess]

PHV,
The code fragment I came across used
[Dim Fname As Variant
Fname = Application.GetOpenFilename( _
FileFilter:="Excel 97-2003 Files (*.xls), *.xls", _
Title:="Select a file or files", _
MultiSelect:=True)
]
SKIPVOUGHT,
I am selecting the spreadsheets one at a time. My intention in running the macro is to save to a new file name, move the cells, do a few calculations, save and close the spreadsheets. I am going to store the macro in my personal macro file.

MINTJULEP,
Will Object.name pull only the name (without path) from the Variant that stores the result of Application.GetOpenFilename?

Thanks everyone,
On to round 2

 

[PHV]

I am selecting the spreadsheets one at a time
Really, so why this ?
MultiSelect:=True)

Anyway, have a look at the Split and UBound functions, eg:
a=Split(Fname,"\")
MsgBox Fname & vbCrLf & a(UBound(a))


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Use name in the Workbooks.Open method.

Then get the name from ActivWorkbook.Name.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[paulbhess]

PHV,
I understand your confusion about how many spreadsheets I was opening. I'm new at VBA and did not notice the "MultiSelect:=true" option setting.

SKIPVOUGHT,
I will give your solution a try tomorrow.

Thanks, everyone. I'll let you know how things turn out.