I know I'm missing a critical piece but can't figure it out. How do I get
just the name of the file they selected (without path)?

Sub CopyFilesMacro()

Application.ScreenUpdating = False

Sheets("Input").Select
MyPath = Range("B2").Value

MsgBox ("As soon as you click the OK button, a Browse Dialog box will " _
& "pop up. Use it to choose the files you want to copy to the
location shown " _
& "in cell B2 of the Input sheet.")

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Dim vrtSelectedItem As Variant

With fd

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

FileCopy vrtSelectedItem, MyPath & "\" &
vrtSelectedItem.Name & ".xls"

Next vrtSelectedItem
Else
End If
End With

Set fd = Nothing
End Sub

Try something like the following:

For Each vrtSelectedItem In .SelectedItems
FileNameOnly = Mid(vrtSelectedItem, _
InStrRev(vrtSelectedItem, "\") + 1)
Debug.Print FileNameOnly
Next vrtSelectedItem



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 10 Mar 2009 14:34:04 -0700, Keithlo
wrote:

I know I'm missing a critical piece but can't figure it out. How do I get
just the name of the file they selected (without path)?

Sub CopyFilesMacro()

Application.ScreenUpdating = False

Sheets("Input").Select
MyPath = Range("B2").Value

MsgBox ("As soon as you click the OK button, a Browse Dialog box will " _
& "pop up. Use it to choose the files you want to copy to the
location shown " _
& "in cell B2 of the Input sheet.")

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Dim vrtSelectedItem As Variant

With fd

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

FileCopy vrtSelectedItem, MyPath & "\" &
vrtSelectedItem.Name & ".xls"

Next vrtSelectedItem
Else
End If
End With

Set fd = Nothing
End Sub

Here is one way. Be careful of the file extension. If you have them visible
on your system you do not need it in the destination path. It will be part
of the file name.


For Each vrtSelectedItem In .SelectedItems
Set Fso = CreateObject("Scripting.FileSystemObject")
fName = Fso.GetFileName(vrtSelectedItem)
MsgBox fName
FileCopy vrtSelectedItem, MyPath & "\" & fName & ".xls"
'<<Caution

Next vrtSelectedItem




"Keithlo" wrote:

I know I'm missing a critical piece but can't figure it out. How do I get
just the name of the file they selected (without path)?

Sub CopyFilesMacro()

Application.ScreenUpdating = False

Sheets("Input").Select
MyPath = Range("B2").Value

MsgBox ("As soon as you click the OK button, a Browse Dialog box will " _
& "pop up. Use it to choose the files you want to copy to the
location shown " _
& "in cell B2 of the Input sheet.")

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Dim vrtSelectedItem As Variant

With fd

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

FileCopy vrtSelectedItem, MyPath & "\" &
vrtSelectedItem.Name & ".xls"

Next vrtSelectedItem
Else
End If
End With

Set fd = Nothing
End Sub

Thanks very much, Chip. I will try this.

"Chip Pearson" wrote:

Try something like the following:

For Each vrtSelectedItem In .SelectedItems
FileNameOnly = Mid(vrtSelectedItem, _
InStrRev(vrtSelectedItem, "\") + 1)
Debug.Print FileNameOnly
Next vrtSelectedItem



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 10 Mar 2009 14:34:04 -0700, Keithlo
wrote:

I know I'm missing a critical piece but can't figure it out. How do I get
just the name of the file they selected (without path)?

Sub CopyFilesMacro()

Application.ScreenUpdating = False

Sheets("Input").Select
MyPath = Range("B2").Value

MsgBox ("As soon as you click the OK button, a Browse Dialog box will " _
& "pop up. Use it to choose the files you want to copy to the
location shown " _
& "in cell B2 of the Input sheet.")

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Dim vrtSelectedItem As Variant

With fd

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

FileCopy vrtSelectedItem, MyPath & "\" &
vrtSelectedItem.Name & ".xls"

Next vrtSelectedItem
Else
End If
End With

Set fd = Nothing
End Sub

Thanks very much, JLGWhiz. I will try this.

"JLGWhiz" wrote:

Here is one way. Be careful of the file extension. If you have them visible
on your system you do not need it in the destination path. It will be part
of the file name.


For Each vrtSelectedItem In .SelectedItems
Set Fso = CreateObject("Scripting.FileSystemObject")
fName = Fso.GetFileName(vrtSelectedItem)
MsgBox fName
FileCopy vrtSelectedItem, MyPath & "\" & fName & ".xls"
'<<Caution

Next vrtSelectedItem




"Keithlo" wrote:

I know I'm missing a critical piece but can't figure it out. How do I get
just the name of the file they selected (without path)?

Sub CopyFilesMacro()

Application.ScreenUpdating = False

Sheets("Input").Select
MyPath = Range("B2").Value

MsgBox ("As soon as you click the OK button, a Browse Dialog box will " _
& "pop up. Use it to choose the files you want to copy to the
location shown " _
& "in cell B2 of the Input sheet.")

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Dim vrtSelectedItem As Variant

With fd

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

FileCopy vrtSelectedItem, MyPath & "\" &
vrtSelectedItem.Name & ".xls"

Next vrtSelectedItem
Else
End If
End With

Set fd = Nothing
End Sub

You were right about the file extension. In my case it was part of the file
name. Thanks for that additional info.

"JLGWhiz" wrote:

Here is one way. Be careful of the file extension. If you have them visible
on your system you do not need it in the destination path. It will be part
of the file name.


For Each vrtSelectedItem In .SelectedItems
Set Fso = CreateObject("Scripting.FileSystemObject")
fName = Fso.GetFileName(vrtSelectedItem)
MsgBox fName
FileCopy vrtSelectedItem, MyPath & "\" & fName & ".xls"
'<<Caution

Next vrtSelectedItem




"Keithlo" wrote:

I know I'm missing a critical piece but can't figure it out. How do I get
just the name of the file they selected (without path)?

Sub CopyFilesMacro()

Application.ScreenUpdating = False

Sheets("Input").Select
MyPath = Range("B2").Value

MsgBox ("As soon as you click the OK button, a Browse Dialog box will " _
& "pop up. Use it to choose the files you want to copy to the
location shown " _
& "in cell B2 of the Input sheet.")

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Dim vrtSelectedItem As Variant

With fd

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

FileCopy vrtSelectedItem, MyPath & "\" &
vrtSelectedItem.Name & ".xls"

Next vrtSelectedItem
Else
End If
End With

Set fd = Nothing
End Sub