.GetOpenFilename assitance

[Paul]

Hi, using XL 2000
A simple task but struggling over a solution:
The following reports the file path AND name..

FileToOpen = Application.GetOpenFilename _
("All Files (*.*), *.*")

If FileToOpen <> False then
MsgBox "Open " * FileToOpen
End if

I just need the filename.

Any ideas?

Thanks, Paul

 

[Bob Phillips]

Hi Paul,

Try this
Dim FSO As Object
Dim FileToOpen

FileToOpen = Application.GetOpenFilename _
("All Files (*.*), *.*")

If FileToOpen <> False Then
Set FSO = CreateObject("Scripting.FileSystemObject")
MsgBox "Open " & FSO.getfile(FileToOpen).Name
Set FSO = Nothing
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Flanagan]

Paul, the follwoing should do it (untested)

for I = len(FileToOpen) to 1 step -1
if mid(FileToOpen,I,1)="\" Then
JustFileName = mid(FileToOpen, I+1)
exit For
end if
Next

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Jarek]

Hi,
another way using InStrRev function

JustFileName= Mid(FileToOpen, InStrRev(FileToOpen, "\") + 1)

Jare

 

[Bob Phillips]

But that is not available in XL97, so Bob 's solution is more robust.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul]

Thanks bob. Its pulled the fulename.
How would I use this variable to display a workbook?
(make active)

Paul


Dim FileToOpen

FileToOpen = Application.GetOpenFilename _
("All Files (*.*), *.*")

If FileToOpen <> False Then
Set FSO = CreateObject
("Scripting.FileSystemObject")
MsgBox "Open " & FSO.getfile(FileToOpen).Name
Set FSO = Nothing
End If

 

[Tom Ogilvy]

Dim sStr as String, wkbk as Workbook
sStr = FSO.getfile(FileToOpen).Name
on error resume next
set wkbk = Workbooks(sStr)
On error goto 0
if not wkbk is nothing then
wkbk.activate
else
msgbox sStr & " is not currently open"
End if