File Extension Trouble

[coryt]

Here goes nothing...

I have a form which the user can specify (or choose) the path to an excel spreadsheet they wish to import. They then click a button and an inputbox appears asking them what they want to call the table. Since the excel spreadsheet is already named I thought it might be a good idea to have that name default in the inputbox. This works fine. The problem is that the input box supplies the name of the spreadsheet and it's exstension (ex. Test.xls). Access will not allow Test.xls to be used for a table name. How can I make the file name appear without the extension?

Thank you in advance!

 

[coryt]

Thought it might be good to post my code.
Private Sub cmdDialog_Click()

Dim strPath As String

CDSearchString = MakeFilterString("Excel Files (*.xls)", "*.xls")
CDCaption = "Select Excel File"

If IsNull([FilePath]) Or Me.FilePath = "" Then
CDInitDir = "C:\"
Else
CDInitDir = Me.FilePath
End If

strPath = LaunchCD(Me)
If Not strPath = "None Selected" Then
Me.FilePath = Dir(strPath & "*.*")
End If
End Sub

Private Sub cmdImport_Click()
Dim strTableName As String
strTableName = InputBox("What do you wish to call your table?", "Table Name", Me.FilePath)

DoCmd.TransferSpreadsheet acImport, , strTableName, Me.FilePath

I tried taking the &"*.*" out of Me.FilePath = Dir(strPath & "*.*") but that didn't work.

 

[john471]

Check out Bob's ySplit Function in Post #6 of This Thread (you'll need to refer to the attached Word document). Then you might use his function and do something like

Private Sub cmdImport_Click()
    Dim strTableName As String
[COLOR=RoyalBlue]    Dim strDefault As String

    strDefault = ysplit(Me.FilePath,".",True)
    strTableName = InputBox("What do you wish to call your table?", "Table Name", strDefault)[/COLOR]    
    DoCmd.TransferSpreadsheet acImport, , strTableName, Me.FilePath

(assuming you, like myself, are using a version of Access (97, in my case) that doesn't have its own implementation of such functionality - I think in some later version a split() function was introduced to do this type of task???)

HTH

Regards

John.

 

[coryt]

I am using Access 2002.

the code you provided did not work ("sub or function not defined") so I took the y off split. I am now getting a Type Mismatch error on this line:
strDefault = Split(Me.FilePath, ".", True)

This is my code:
Dim strTableName As String
Dim strDefault As String

strDefault = Split(Me.FilePath, ".", True)
strTableName = InputBox("What do you wish to call your table?", "Table Name", strDefault)
DoCmd.TransferSpreadsheet acImport, , strTableName, Me.FilePath

 

[coryt]

I got it by using the ySplit option you gave me. I forgot I had to create a module that contained the ySplit function.

 

[ghudson]

What if by chance the users Excel file name has a period within the filename? [ie: Test.File.xls]

You can use this [assuming that the name of your field that is holding the filename is "FilePath"] to get the file name [minus the .xls]

strDefault = Left((FilePath), Len(FilePath) - 4)

 

[john471]

Since Access* won't lent you use a dot/decimal point in a table name, you probably do want to truncate it at the first one found.

*well AC97 anyway