Detect Workbook Name when using GetOpenFileName
 

Hi,

I'm using the following code in Access to select an Excel file that I wish
to import into a Table. It works great. However I would also like to test
that the user has selected the correct Excel file.

"directory" provides the file path information that I need for the Access
VBA statement to import the file into the table. However before I go futher I
need to test for the name of the workbook as well. I could parse this out by
detecting the backslashes in the file path - but that's not very neat.

Any help appreciated.

Rgds...Chris

Set xlObj = CreateObject("excel.application")
xlObj.Visible = True

directory = xlObj.Application.getopenfilename("Excel Files (*.XLS),
*.XLS", 1)

Detect Workbook Name when using GetOpenFileName
 

Hi Chris

You can use Dir


Sub testing()
Dim FName As Variant
Dim N As Long
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")

If FName < False Then
MsgBox Dir(FName)
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chris Gorham" wrote in message ...
Hi,

I'm using the following code in Access to select an Excel file that I wish
to import into a Table. It works great. However I would also like to test
that the user has selected the correct Excel file.

"directory" provides the file path information that I need for the Access
VBA statement to import the file into the table. However before I go futher I
need to test for the name of the workbook as well. I could parse this out by
detecting the backslashes in the file path - but that's not very neat.

Any help appreciated.

Rgds...Chris

Set xlObj = CreateObject("excel.application")
xlObj.Visible = True

directory = xlObj.Application.getopenfilename("Excel Files (*.XLS),
*.XLS", 1)

Detect Workbook Name when using GetOpenFileName
 

Hi Chris,

Here's another way:

Sub test()
Dim vFullPath As Variant
Dim vFileInfo As Variant
Dim sFileName As String

vFullPath = Application.GetOpenFilename("Excel Files " & _
"(*.XLS), *.XLS", 1)

If vFullPath < False Then
MsgBox vFullPath
vFileInfo = Split(vFullPath, Application.PathSeparator)
sFileName = vFileInfo(UBound(vFileInfo))
MsgBox sFileName
End If
End Sub


This is code from within Excel, so you'll have to qualify references to
Application with xlObj.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Chris Gorham wrote:
Hi,

I'm using the following code in Access to select an Excel file that I
wish to import into a Table. It works great. However I would also
like to test that the user has selected the correct Excel file.

"directory" provides the file path information that I need for the
Access VBA statement to import the file into the table. However
before I go futher I need to test for the name of the workbook as
well. I could parse this out by detecting the backslashes in the file
path - but that's not very neat.

Any help appreciated.

Rgds...Chris

Set xlObj = CreateObject("excel.application")
xlObj.Visible = True

directory = xlObj.Application.getopenfilename("Excel Files (*.XLS),
*.XLS", 1)