OPEN FILE DIALOG BOX 1

[LBERNARDE]

I am having the user select different files to import into an Access 2000 database. In VFP there is a Getfile() command, is there anything like that? I've been doing research on this and everything I've found is tons of code, that I'm having difficulty making sens of. Is there something that simple to help me accomplish my task? Thanks for the help!

 

[Gazonice]

Hi,

Make new a module called: mdl_browse, paste this code into it:

'*******************************start

Option Compare Database
Option Explicit

Private Type BrowseInfo
hwndOwner As Long
pIDLRoot As Long
pszDisplayName As Long
lpszTitle As Long
ulFlags As Long
lpfnCallback As Long
lParam As Long
iImage As Long
End Type

'Browsing for directory.
Private Const BIF_RETURNONLYFSDIRS = &H1 'For finding a folder to start document searching
Private Const BIF_DONTGOBELOWDOMAIN = &H2 'For starting the Find Computer
Private Const BIF_STATUSTEXT = &H4
Private Const BIF_RETURNFSANCESTORS = &H8

Private Const BIF_BROWSEFORCOMPUTER = &H1000 'Browsing for Computers.
Private Const BIF_BROWSEFORPRINTER = &H2000 'Browsing for Printers
Private Const BIF_BROWSEINCLUDEFILES = &H4000 'Browsing for Everything

Private Const MAX_PATH = 260

Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal _
lpString1 As String, ByVal lpString2 As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As _
BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As _
Long, ByVal lpBuffer As String) As Long

Public Function BrowseForFolder(hwndOwner As Long, sPrompt As String) As String

'=================================================
'Opens the system dialog for browsing for a folder
'=================================================
Dim iNull As Integer
Dim lpIDList As Long
Dim lResult As Long
Dim sPath As String
Dim udtBI As BrowseInfo

With udtBI
.hwndOwner = hwndOwner
.lpszTitle = lstrcat(sPrompt, "&quot

.ulFlags = BIF_RETURNONLYFSDIRS
End With

lpIDList = SHBrowseForFolder(udtBI)
If lpIDList Then
sPath = String$(MAX_PATH, 0)
lResult = SHGetPathFromIDList(lpIDList, sPath)
Call CoTaskMemFree(lpIDList)
iNull = InStr(sPath, vbNullChar)
If iNull Then
sPath = Left$(sPath, iNull - 1)
End If
End If

BrowseForFolder = sPath

End Function
'************************************finish

Then, on your form, create a command button and use this code to call the module: (onclick)

MyStr = BrowseForFolder(hWnd, "Browse Window text here&quot

 

[LBERNARDE]

Thanks, that worked great, however, I'm doing an import and want to be able to actually select a file to work with in my code and I can't see the actual files in the folder, just the folders. Do you have any more suggestions?
Thanks Lisa

 

[aaahhhrrggghhhh]

Hi.

One suggestion - use the "Microsoft Common Dialog Control 6 (SP3)" (COMDLG32.OCX). Make sure that you have it as a reference.

Now use the dialog box to get the path to the file you want to select. I have written some code, but this is for a system that I wrote so you may have to just use the principals in your db. Make sure that the form that you have has the common dialog control on it. Heres the sample code

Private Sub btnAllDataExport_Click()

reply = MsgBox("Will Export all data to the desired Access datafile. Are You Sure?", vbYesNo)
If reply = vbNo Then Exit Sub
Me.CmDlg1.CancelError = True
On Error GoTo usrexit
Me.CmDlg1.Flags = cdlOFNHideReadOnly
Me.CmDlg1.Filter = "Microsoft Access Database (*.mdb)|*.mdb"
Me.CmDlg1.FilterIndex = 2
Me.CmDlg1.ShowOpen

'make sure that you dont point to the current database
If (Me.CmDlg1.FileName = (CurrentProject.Path & "\" & CurrentProject.Name)) Or _
(Me.CmDlg1.FileName = (Mid$(DBEngine.Workspaces(0).Databases(0).TableDefs("NEWLTT_PREPROP&quot.Connect, 11))) Then

MsgBox ("You cannot overwite this database with its own data!&quot
Exit Sub

End If

'open the destination database to ensure that these tables don't already exist. If so, remove them.
Dim chkdb As Database
fullfilename = Me.CmDlg1.FileName

Call ExportData(fullfilename, False)

usrexit:
If Err.Number = 32755 Then
MsgBox ("Data Not Exported&quot
ElseIf Err.Number = 3356 Then
MsgBox ("Destination database is not ready, please ensure that noone is using it and export again.&quot
' Else
' MsgBox (Err.Number & " " & Err.Description)
End If

End Sub


At one point in the code it calls a separate function (ExportData) which recieves the full pathname of the file, and another parameter. If you want to see the code for this, please let me know. I will be glad to share it.

Regards,
Mr Big Dont be small. be BIG

 

[LBERNARDE]

You'll have to bear with me, I'm fairly new in VBA, I did get the reference you noted established, but the Common Dialog Control is what I'm having problems with. I just have been unable to figure out how to get this thing to work. You helped me tremendously by telling me which reference I need. By the way do you know any place that will tell you which reference you need to accomplish a task? That has been a problem for me because I know you need references activated, but I never know which one does what. Anyway, I must be missing something to get this command dialog control to work. I read that you need the Microsoft Office 10.0 Object Library, but I only see the 9.0 OL on my Access. I will get this, bear with me! Your help is greatly appreciated.
Thanks Lisa

 

[oharab]

STEP AWAY FROM THE COMMON CONTROL DIALOG. THERE IS NOTHING TO SEE THERE!

Seriously I would not reccommend using that control to anyone. I have seen too many pleas for help when it doesn't work or there are problems when you send your database to another user!

A much better way would be to use the code from:

http://mvps.org/access/api/api0001.htm

It works everytime and you don't have to worry about setting references.

HTH

Ben

----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[LBERNARDE]

Unbelievable!!! Thank you, it worked, now I can go on figuring out the rest. Thank you, thank you!

 

[LBERNARDE]

The open worked, now I'm trying to figure out what variable the file name is located in. I've stepped thru the code watching the variables to find the one that contains the file name so I can do my import, but haven't been able to locate it.

 

[oharab]

The function ahtCommonFileOpenSave returns the file name.
Basically you need to do something like:

Dim strFilename as string 'var to store the filename
Dim strFilter as string 'var to store the file filter

strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", "*.MDA;*.MDB&quot 'Load the filter

strFilename=ahtCommonFileOpenSave(InitialDir:="C:\", Filter:=strFilter, FilterIndex:=3, DialogTitle:="Hello! Open Me!&quot

Then use strFilename however you want.

HTH

Ben

----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------