File Browser to Import Dbase

[Fliption]

I have been using some code that allows a user to browse for a file in order to import it into Access. It works like a charm as long as the data to be inported comes from another access file. The module that allows the user to select a file is simply saving the path and filename selected as a variable. The import command for access looks like this below:


DoCmd.TransferDatabase acImport, "Microsoft Access",strPath, actable, "TableName", "NEWTableName"

The variable is inserted in the red portion. The problem is with Dbase. I need to be able to do the same thing with Dbase but its import syntax looks like this:

DoCmd.TransferDatabase acImport, "dBase 5.0",strPath, actable, "FileName.dbf", "NEWTableName"

As you can see in order to import dbase you have to know both the path/file name combination AND the the file name on it's own. I just don't know enough about coding in VBA yet to modify the code I'm using. I'm pasting that code in here. Any ideas or thoughts on how to do this are appreciated.


' Declarations
' (Copy them to the (declarations) section of a module.)
Public Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustomFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Public Const OFN_FILEMUSTEXIST = &H1000
Public Const OFN_HIDEREADONLY = &H4
Public Const OFN_PATHMUSTEXIST = &H800
Public Declare Function GetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (lpofn As OPENFILENAME) As Long




Public Function PromptFileName() As String
Dim filebox As OPENFILENAME ' open file dialog structure
Dim FName As String ' filename the user selected
Dim result As Long ' result of opening the dialog

' Configure how the dialog box will look
With filebox
' Size of the structure.
.lStructSize = Len(filebox)
' Handle to window opening the dialog.
.hwndOwner = 0 'Me.Hwnd
' Handle to calling instance (not needed).
.hInstance = 0
' File filters to make available: Access Databases and All Files
.lpstrFilter = "Access Databases (*.mdb)" & vbNullChar & "*.mdb" & _
vbNullChar & "All Files (*.*)" & vbNullChar & "*.*" & _
vbNullChar & vbNullChar
'.lpstrCustomFilter is ignored -- unused string
.nMaxCustomFilter = 0
' Default filter is the first one (Text Files, in this case).
.nFilterIndex = 1
' No default filename. Also make room for received
' path and filename of the user's selection.
.lpstrFile = Space(256) & vbNullChar
.nMaxFile = Len(.lpstrFile)
' Make room for filename of the user's selection.
.lpstrFileTitle = Space(256) & vbNullChar
.nMaxFileTitle = Len(.lpstrFileTitle)
' Initial directory is C:\.
.lpstrInitialDir = "C:\" & vbNullChar
' Title of file dialog.
.lpstrTitle = "Select a File" & vbNullChar
' The path and file must exist; hide the read-only box.
.flags = OFN_PATHMUSTEXIST Or OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY
' The rest of the options aren't needed.
.nFileOffset = 0
.nFileExtension = 0
'.lpstrDefExt is ignored -- unused string
.lCustData = 0
.lpfnHook = 0
'.lpTemplateName is ignored -- unused string
End With

' Display the dialog box.
result = GetOpenFileName(filebox)
If result <> 0 Then
' Remove null space from the file name.
FName = Left(filebox.lpstrFile, InStr(filebox.lpstrFile, vbNullChar) - 1)
'Debug.Print "The selected file: "; fname
End If

'return the string of the file name
PromptFileName = FName

End Function

 

[meloncolly]

so, set a variable to get the path and the file. Once you have the full path you can strip out the file name using:

Function FileNameOnly(pname) As String
' Returns the filename from a path/filename string
Dim i As Integer, length As Integer, temp As String
length = Len(pname)
temp = ""
For i = length To 1 Step -1
If Mid(pname, i, 1) = "\" Then
FileNameOnly = temp
Exit Function
End If
temp = Mid(pname, i, 1) & temp
Next i
FileNameOnly = pname
End Function

'so the whole thing looks like this:

Dim MyPath, MyFile

MyPath = PromptFileName()
'then extract the filename from the full path
MyFile = FileNameOnly(MyPath)

Now you can use the full path (MyPath) and just the filename in your code. If you just need the path only, then this function applied to MyPath will get you that.

example:

PathNameOnly(MyPath)

function:
Function PathNameOnly(pname) As String
' Returns the pathname only from a path/filename string
' or "" if no back slash is found
Dim i As Integer, length As Integer, temp As String
length = Len(pname)
temp = ""
For i = length To 1 Step -1
If Mid(pname, i, 1) = "\" Then
PathNameOnly = Left(pname, i)
Exit Function
End If
Next i
PathNameOnly = ""
End Function

 

[Fliption]

Thanks for the help. Unfortunately I can't seem to get this to work. Before I proceed, let me ask this question. I'm not very astute at VBA so pardon my ignorance. It looks as if this code is looking for a backslash to split the full path/filename into a path and a filename. Does this still work if a user has the file sitting in a directory that is several folders deep and therefore has more than one backslash?

I have since learned that I was incorrect on the syntax. To import a dbf file, you need to use the path by itself and then the file name by itself. So the last function you added to split out just the path would be needed. Wouldn't you need an additional line in this case similar to this:

Dim path
Path=Pathnameonly(MyPath)

??

And then the variables I would insert into the docmd line would be 'Myfile' and 'Path'?

I did this and I get an error... 'path' is not a valid path. Make sure the path name is spelled correctly blah blah blah"

Thanks for any input.

 

[gemma-the-husky]

its probably the strpath bit thats falling over on the dbase import,

the strpath is probably the fully specified path including the filename, and the "tablename" is probably the table name.

However each dbs provider has slighltly different syntax for this sort of thing.

I am sure I saw a reference to a link, showing various connection strings for different dbs types, but I'm not exactly sure where